I came across this as we’d recently received a corporate directive that all indexes should be created with the ONLINE=ON option. That’s not too bad. Add that to all indexes as needed, publish the DB project, and we’re good to go. Except that we’re not.
Due to an issue with SSDT “working as designed”, the ONLINE=ON option is ignored as any of the criteria for comparing indexes within SSDT. On the positive side, it means that any index created that way won’t have the option dropped. On the negative side, it means that any index that is changed or added will not have that option.
If this is an issue to you, comment and vote on the related Connect ticket. If we can garner enough votes and interest, perhaps MS will hear us and change their mind about whether or not the ONLINE=ON should be part of the release script.
In the meantime, if you absolutely need this functionality, generate scripts for your releases and ensure that all index creates/alters include the option you want.
Edit: There's been some discussion about this on the MSDN forums around this. Kevin Cunnane posted several responses pointing to a Codeplex example of how this might be possible by compiling a DLL and referencing that through the SQLPackage command line. I have not had a chance to try that out yet, but am including the forum and Codeplex links for those who want to investigate further.
Forum post
Codeplex example
The link doesn't go to a Connect item.
ReplyDeleteGood catch, Mark. Not sure why my blog address was inserted before the "Connect" link. I've fixed that. I've also updated the post with a response I received from the MSDN forums about a possible way to work around this. (I still think it's worth upvoting at the Connect link, though.)
Delete