Nonclustered Index not detecting Where is not null
I have an index that is created like so:
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_table] ON [dbo]. ([column]) WHERE ([column] IS NOT NULL)
SQL Compare doesn't seem to be detecting the where is not null part and says it should be replaced with:
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_table] ON [dbo]. ([column])
Which fails, because several of the rows are null.
I dropped the index on both databases being compared and recreated them to verify they were the same.
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_table] ON [dbo]. ([column]) WHERE ([column] IS NOT NULL)
SQL Compare doesn't seem to be detecting the where is not null part and says it should be replaced with:
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_table] ON [dbo]. ([column])
Which fails, because several of the rows are null.
I dropped the index on both databases being compared and recreated them to verify they were the same.
Comments
What version of SQL Compare are you using? Also SQL version?
I am unable to replicate your issue using SQL Compare V11.0.0.365 and SQL 2008.
Many Thanks
Eddie
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com