How can we help you today? How can we help you today?

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.
shawn.mayer
0

Comments

2 comments

Sort by
  • Eddie D
    Hi Shawn

    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
    Eddie D
    0
  • shawn.mayer
    I too was unable to replicate this on a new database so I investigated further. One of the databases was set to compatibility level 90 and the other 100. When I set them both to 100 the issue went away.
    shawn.mayer
    0

Add comment

Please sign in to leave a comment.