Options
SQL 2005 Index comparison with INCLUDE clause
webyeti
Posts: 2
First of all, Thanks for producing a fantastic product. I really don't think I could live without it! That said I have a couple of small annoyances I wanted to mention.
Issue 1)
I'm using SQL Compare 5.2 against two SQL 2005 (x64) databases. It's showing me some tables that are identical except for the order of the columns in the index's INCLUDE clause.
Example
CREATE NONCLUSTERED INDEX [_dta_index_OrderDetails_5_1976146581__K1_15_24] ON [dbo].[OrderDetails] ([OrderID]) INCLUDE ([ConfigurationID], [Comments])
!=
CREATE NONCLUSTERED INDEX [_dta_index_OrderDetails_5_1976146581__K1_15_24] ON [dbo].[OrderDetails] ([OrderID]) INCLUDE ([Comments], [ConfigurationID])
When I view and run the synchronization script it looks correct but if I re-compare the column order on the target database is still out of order. Regardless, since these are not actually part of the index I would like to see an option to ignore column order in the INCLUDE clause.
Issue 2)
Same setup, when a bit column has a default the script sometimes puts two pairs of parentheses around it and sometimes one pair. It does NOT report these as differences UNLESS there is another difference in the tables. Also, the synchronization script ignores them. My only real complaint is that it's distracting when I'm looking through the SQL Differences pane.
Example
[IsMatchItem] [bit] NOT NULL DEFAULT ((0))
!=
[IsMatchItem] [bit] NOT NULL DEFAULT (0)
Issue 1)
I'm using SQL Compare 5.2 against two SQL 2005 (x64) databases. It's showing me some tables that are identical except for the order of the columns in the index's INCLUDE clause.
Example
CREATE NONCLUSTERED INDEX [_dta_index_OrderDetails_5_1976146581__K1_15_24] ON [dbo].[OrderDetails] ([OrderID]) INCLUDE ([ConfigurationID], [Comments])
!=
CREATE NONCLUSTERED INDEX [_dta_index_OrderDetails_5_1976146581__K1_15_24] ON [dbo].[OrderDetails] ([OrderID]) INCLUDE ([Comments], [ConfigurationID])
When I view and run the synchronization script it looks correct but if I re-compare the column order on the target database is still out of order. Regardless, since these are not actually part of the index I would like to see an option to ignore column order in the INCLUDE clause.
Issue 2)
Same setup, when a bit column has a default the script sometimes puts two pairs of parentheses around it and sometimes one pair. It does NOT report these as differences UNLESS there is another difference in the tables. Also, the synchronization script ignores them. My only real complaint is that it's distracting when I'm looking through the SQL Differences pane.
Example
[IsMatchItem] [bit] NOT NULL DEFAULT ((0))
!=
[IsMatchItem] [bit] NOT NULL DEFAULT (0)
This discussion has been closed.
Comments
Glad you like the product, I have some good news and some not so good news:
Firstly good news, if you email support@red-gate.com and reference this post they should be able to sort you out with an fix that ignores the order of columns in the INCLUDE clause.
Now the not so good news. Those brackets, although fairly minor I know this issue irrates customers. We are at the moment undertaking requirements gathering for the next version of SQL Compare and whilst I am really hoping that the SQL Differences gets an overhall I cannot promise that everything that goes into the list will be done.
Hope this helps,
Regards,
Jonathan
-Project Manager
-Red Gate Software Ltd