Different column order when comparing indexes

Morve

Hello everybody

I have found another case where the column order differ when comparing databases. The case is about the include fields in indexes.

I have two databases with identical tables.
I created an index like this:

create ix_Table1_index1 on Table1(Field1) include (Field2, Field3, Field4, Field5)

I run the script on both databases and both indexes works as they should. When I use SQL Compare 6.1.0.53 I find that on one database, the create script in the difference pane is like this:

create nonclustered index ix_Table1_index1 on Table1(Field1) include (Field2, Field3, Field4, Field5)

and on the other database the script is lke this:

create nonclustered index ix_Table1_index1 on Table1(Field1) include (Field4, Field2, Field5, Field3)

The field Column_id in sys.index_columns contains the correct order for the include columns in both databases. Because the Column_Id is not part of an order by statement, and the query SQL Compare 6.1 uses contains several inner joins, the SQL Server cannot be sure of the order of the returned data. It may differ from hour to hour based on use and statistics update. This is an easy task to fix. Just have a look at the query SQL compare uses for this task:

SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Index_Name],
(case ic.key_ordinal when 0 then cast(1 as tinyint) else ic.key_ordinal end) AS [ID],
clmns.name AS [Name],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N'IsComputed') AS bit) AS [IsComputed]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(tbl.name=N'TableName' and SCHEMA_NAME(tbl.schema_id)=N'dbo')
ORDER BY
[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[ID] ASC, ic.column_id ASC


My suggestion in bold. Indexes with identical signatures should not be listed with differences because of this.

I hope this may be of help to other people too.

Thanks

JonathanWatts

Hi there,

I have split this off from the original topic as whilst similar it is a different issue as it relates to an issue inside the index's included columns clause rather than the ordering of columns in table.

As the ordering in include clauses as no effect on the index then agree that your suggestion seems a sensible one. I will raise it to the developers to be fixed in a future build.

As a side note, if the only difference in the table is the order of columns in an index's include clause this table will still be treated as identical by the SQL Compare semantic comparison.

Regards,

Jonathan