Home Dashboard Directory Help

[SSDT] ONLINE index option is ignored by Dave_Ballantyne



Sign in
to vote
Type: Bug
ID: 775760
Opened: 1/4/2013 1:33:53 AM
Access Restriction: Public
User(s) can reproduce this bug


If i declare an index in SSDT as

CREATE INDEX [IX_myTable_Column] ON [dbo].[myTable] ([Name]) with(ONLINE=ON)

when i generate the publish script the ONLINE =ON option is missing.
Needless to say this can have a hugh production impact if not noticed.
Sign in to post a comment.
Posted by Peter A. Schott on 11/1/2013 at 1:20 PM
I have to agree with Dave here. This is essential for publishing database projects against any larger database. We need some way to specify that the ONLINE = ON option needs to be set for an index.
Posted by Dave_Ballantyne on 2/13/2013 at 6:14 AM
Hi Adam,

Thanks for the response.

Although I understand about ONLINE not being a declarative statement and therefore ignored by SSDT, SSDT is responsible for the creation of scripts that are run into an operational environment so its hard to see how the design of the deployment pipeline hasn't taken it into account.

I guess my concerns are that once again we are having to add more 'human factors' into the equation.

To generate an index on a bazillion row table AND keep a production server online we need to use the ONLINE option.

The index would be added to an SSDT project and checked in.
A pre-deployment script could then be created with the ONLINE option and then checked into TFS.
SQLPackage will now not do anything as the index already exists as it expects it to be.

Another developer could then come along at a later date,unaware of ONLINE operations and the pre-script involved and make a change only to the index in TFS and not the pre-script.

Now we have two index creations occurring once we hit the magic 'do-it' button.

Make any sense ?

Posted by Microsoft on 2/11/2013 at 1:49 PM
Hey Dave,

This issue is By Design. DACFx and SSDT not support the ONLINE index option, as it is a stateful T-SQL property. As DACFx and SSDT are purely declarative, these operational/stateful properties and pieces of syntax are not supported or honored by the deployment pipeline.

Thanks, and let us know if you have any additional questions or feedback.

Adam Mahood
Program Manager
SQL Database Systems
Sign in to post a workaround.