How can I configure SCA to script index options with ONLINE = ON and SORT_IN_TEMPDB = ON?

PeterDaniels

I have created indexes in my dev DB with (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = 85) options per ops DB standards, but when I refresh the SCA project, it does not include those options in the generated synchronization script.  I have looked at:

https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/configuring-script-generation/configuring-comparison-script-generation-options

but I only see options for 

<!-- "Ignore fill factor and index padding" SQL Compare option --><br>  <SyncOptionIgnoreFillFactor>True</SyncOptionIgnoreFillFactor><br>&nbsp;<br>  <!-- "Ignore LOCK properties of indexes" SQL Compare option --><br>  <SyncOptionIgnoreIndexLockAttributes>False</SyncOptionIgnoreIndexLockAttributes>

 
How can I make it pick up and script ONLINE and SORT_IN_TEMPDB options?

TIA,

-Peter

Kendra_Little

Hi Peter,

These options aren't stored in the metadata in SQL Server after the index is created -- EXCEPT for fillfactor. 

For fillfactor, you can change the 'SyncOptionIgnoreFillFactor' setting in the .sqlproj file. Instructions on that are here, and the note on that setting indicates it also will mean that any index padding settings you use will be scripted as well.

For the options which apply to the index creation process only and aren't persisted in the metadata, like ONLINE and SORT_IN_TEMPDB, there is a User Voice page on this here which has an explanation of some of the considerations around why we don't have this presently. I personally think it's worth expanding the options list in Compare to have global enable/disable for each of these as it would be much nicer for you to be able to configure that preference rather than having to use snippets. Please do consider adding a note to that User Voice forum if you have the time. I'll also internally copy this thread over to the SQL Compare team as well, so they are aware.

Kendra