Skip to content
← SQL Compare

How can we improve SQL Compare?

SQL Compare

Categories

JUMP TO ANOTHER FORUM

(thinking…)

SQL Compare store options for index create\rebuild

First this may be more apparent as tedious in my case, we happen to have large tables with decent sized indexes. I would appreciate if through the setup of a SQL Compare project I could impact index create or rebuild behavior through the selection of on\off toggle for the switches that are available upon creation of an index.

I use some options more than others with the most frequently added option being SORTINTEMPDB = ON. I use this because when building large indexes I want to try and avoid as much as possible any unintended file growth by specifying that TempDB is used for the sort operation of the index create.

Since there isn't a mechanism to specify this in the project options, I must always generate a deployment script and manually modify the index create portions of a given script to specify any switches I want to throw at the index create.

So for each possible modifier:

PADINDEX = ON
STATISTICSNORECOMPUTE = ON
SORTINTEMPDB = ON
DROPEXISTING = ON
ONLINE = ON
ALLOWROWLOCKS = ON
ALLOWPAGE_LOCKS = ON

I would like to be able to select that these options be used and specify the value (ON or OFF). Since this is a project level setting, I could then set the desired values as a project default ensuring that when the deployment is done through SQL Compare or a generated migration script that I don't have to step through each index create and add the options I want to use manually.

Using the previously listed modifiers as an example in that all are turned on, the deployment script for an index would include the following:

CREATE NONCLUSTERED INDEX [IDXIndexOne] ON [Schema].[Table]
(
[Column1] ASC
)
WITH (
PADINDEX = ON, STATISTICSNORECOMPUTE = ON, SORTINTEMPDB = ON, DROPEXISTING = ON, ONLINE = ON, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON
)

GO

78 votes
Vote

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
You have left! (?) (thinking…)
Dan Tibbs shared this idea  ·   ·  Report…  ·  Admin →

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Joe Harkins commented  ·   ·  Report

    I would also like to throw my voice in to request a default for SORT_IN_TEMPDB = ON and
    ONLINE = ON and throw one more in that is huge for my needs, which is a MAXDOP option to help with deployments to availability groups.

    We can of course go in and manually make these changes ourselves, but when I turn my devs loose and they want to make a quick index change, I would like to be able to ensure that they do so in a way that will not overwhelm my synchronous replica upon deployment.

  • Neil Macehiter commented  ·   ·  Report

    I would also like to see support for this for Index creation, particularly ONLINE = ON.

    In addition, with SQL Server 2016 we now have support for ONLINE = ON for ALTER TABLE ... ALTER COLUMN. Support for this would be greatly beneficial.

  • Jacob Golden commented  ·   ·  Report

    I would also really like to see this added...especially an option to apply indexes ONLINE since sometimes we may be going from lower environment to prod and want to not impact the environment as much as possible.

  • Anonymous commented  ·   ·  Report

    Hey Mark,
    Thanks for the update on this issue. I definitely understand the desire to keep the number of options in SQL Compare from bloating, but I want to register my support for adding this as an option as well. Perhaps it would be more maintainable if options specific to generating the deployment scripts were grouped together on their own.
    Regardless, this would be a really great option for those of us not managing the scripts folder, but using Source Control to do that for us or running the compare against another database (Both of which are common scenarios for us).

  • AdminMark Jordan (Software Engineer, Redgate) commented  ·   ·  Report

    Just an update on this - as far as I know we properly support comparing and deploying most of these index options apart from ONLINE=ON and SORT_IN_TEMPDB=ON. DROP_EXISTING should already be handled by SQL Compare where necessary.

    The problem is these two options in particular is that SQL Server doesn’t store the fact that an index was created with ONLINE=ON or SORT_IN_TEMPDB=ON, so SQL Compare isn’t able to treat it as a normal index property that gets compared and deployed. We've recently introduced a feature where if you put the ONLINE or SORT_IN_TEMPDB options into an index definition in a scripts folder we'll deploy using those options from the scripts folder to a live database, which maybe be a workaround.

    We're aware that there's demand to have these options as global enable/disable options, but that would add a lot more entries to our (already straining) project options list, so we want to try and keep that from becoming too big. We'd also like to give you more fine-grained control over which indexes get which options instead of having a global setting that overrides everything. I'm sorry we don't have anything more immediate at the moment but hopefully there will be improvements coming in this area

  • Anonymous commented  ·   ·  Report

    Agreed that enterprise customers using SQL Server Enterprise have high expectations for database devops and automated generation of database deployment scripts. Red Gate SQL Compare is missing capabilities for leveraging rich capcbilities built into SQL Enterprise edition. We find ourselves manually reviewing and modifying the output from SQL Compare to make it usable for safe uptime deployments.

    When generating scripts for SQL Server Enterprise edition, leverage the options ONLINE=ON and DROP_EXISTING=ON.

  • Luigi commented  ·   ·  Report

    It is pretty strange that a product target at enterprises (for the price tag and the practices in introduces) does not handle index options.
    The same applies for products relying on the SQL Compare comparison algorithm like SQL Source Control

  • Carlos Klop commented  ·   ·  Report

    Running in the same issue here with SQL Compare deployment. When creating an Index we would like to have the option ONLINE to ON. Now we have to change this manual.