As I'm sure I must have mentioned in the past, I’m presently involved in a large ALM project at a client.  Part of this project is using Sql Server Data Tools (SSDT) to aid the developer experience and to establish the schema under source control as “The Truth”.

SSDT, has the ability to compare a schema under development to a schema on a server to produce diff scripts and therefore enabling automated deployments,  this is done using SQLPackage.exe .  A big part of this is naturally establishing trust in the software ie SSDT.  We have to be 110% confident that the scripts produced are the right scripts.  Recently though, we had a spurious change appear that took some explaining.

A stored procedure was appearing in a diff script that apparently had no changes, when comparing the project to the schema inside SSDT rather that SQLPackage.exe, I could indeed see that a change had been detected though what that change was, wasn't immediately apparent.  Here is a similar “non-change” :

The developer has tidied up the formatting of the statement ever-so slightly but we have the “IgnoreWhiteSpace” setting enabled.  This change should have been ignored and indeed testing proved that when you change tabs to spaces or add spaces etc ( ie whitespace changes) then the change would be ignored,  so something else was happening.

Loading up a hexeditor and comparing the two I spied another difference that had been overlooked,  the comment had a TAB changed to spaces.  But as I had ignore white spacing enabled therefore shouldn't have been seen.  Testing that this was the culprit was simple enough by turning on the “IgnoreComments”  option, and sure enough, no change.

This ,to me, appears to be a bug,  whitespacing is not ignored in comments when the “IgnoreWhiteSpace” option is set.  For us using “IgnoreComments” is not an option, so we had to live with this change being made to unify the production schema with source control (The Truth).

I did raise a connect item here : https://connect.microsoft.com/SQLServer/feedback/details/774098/ssdt-whitespace-in-a-comment-is-not-ignored-when-using-the-ignorewhitespace-option

but this was closed as “By Design”,  not sure I agree with that assessment but what can you do, eh ?

Hope that this at least saves someone some head scratching on the same issue.