Index w/included columns

aaron.bertrand

I'm still using 4.0, and am starting to think about migrating to 6.0. I have an issue in 4.0 where included columns are coming back in arbitrary order when I compare a database to a restored copy of its backup. Initially I thought it was a bug in the restore process, but as it turns out it is because of this order by:

ORDER BY i.object_id, i.name, ic.key_ordinal, SecondaryXmlType

Because these two servers happen to return the included columns in a different order, SQL Compare 4.0 gives a false positive and attempts to include this change when comparing two databases.

This should be (imho) as follows in order to prevent arbitrary ordering of included columns by SQL Server:


ORDER BY i.object_id, i.name, ic.key_ordinal DESC, i.index_column_id, SecondaryXmlType

-- or simply:

ORDER BY i.object_id, i.name, i.index_column_id, SecondaryXmlType

Have index comparison queries been enhanced in v 6.0?

Michelle T

We've completely changed the way that we handle included columns in indexes in 6.0 (the previous way was causing some problems with xml columns). I don't know if your particular problem has been solved because I can't reproduce it, but I encourage you to download a free trial of 6.1 and see if it's fixed.

(If the problem isn't fixed, please comment again to tell us and we'll make sure it's in our list of things to fix.)

aaron.bertrand

Thanks, it looks like this is not an issue so far in v6.

Cheers,
Aaron