Can you ignore Index Include Column order?

BenDAltus

Just noticed this today, not sure if its a new thing or some strang "option" configuration I have.
I do have "Force Column Oder" enabled, buts only for Table Definitions.

VERSION : 14.5.1.18536 Professional

Example: Two index statements below are logically the same, but SQL Compare shows a difference in the INCLUDE

CREATE UNIQUE NONCLUSTERED INDEX IX_MyIndex ON dbo.MyTable (C1,C2) INCLUDE (C3,C4) WHERE (C5=1)
CREATE UNIQUE NONCLUSTERED INDEX IX_MyIndex ON dbo.MyTable (C1,C2) INCLUDE (C4,C3) WHERE (C5=1)

Pete_Ruiz

Hello @BenDAltus ,

We've created a support ticket for you and I've sent you an email. Can you please reply there?

Kind regards,

Pete Ruiz

squigley

Hello Ben,
Thanks for your patience with this as I've been able to replicate this and discuss it with my team.

Ultimately the conclusion we've gathered is that what you're seeing is expected behavior for the tool which is just highlighting textual differences and I'll explain below why that is the case:

Textual differences are just differences in the text shown  - so in this case the (C3,C4) vs (C4,C3)​.  They are not necessarily semantic differences (and in this example it is not a semantic difference, because the order of those columns doesn't make it a different object or change its function).

A semantic difference is one that indicates the objects are actually different in function or definition in some way, like say a column being an int in one table and a bigint in another table.

The object should only show up in the top view as different if there is a difference (of either sort) that is not being ignored.  However, as soon as there is one difference causing it to appear in the top pane as having a difference in the objects (or you find it in the identical objects category), the bottom pane, when SQL View is selected, will show ALL textual differences.  The Summary view should only show semantic differences when it is selected.​

So what you are seeing when the object is identical but it shows textual differences highlighted is that there are differences that are being ignored (in this case the column order) but since that pane is only showing textual differences it always highlights them.

When an object is identical, it won't attempt to be deployed, so those differences are not deployed, but if an object shows up as having a difference (for a reason that is not being ignored) then when it is deployed, all of the textual differences will be deployed along with the actual difference that caused it to show up as having a difference.

Thanks!