Filtered index where clause not displayed in comparison view

Jason Hannas

This is similiar to an issue I had with a version of SQL Compare 8 last year, which did get fixed. Essentially, a table with a filtered index will show up as identical in the comparison results, but if you actually look at the comparison display, it will show differences. This led me to mistrust the comparison results until I was able to figure out why. The problem was my database compatibility level was set to 90 on one server and 100 on the other.

Here is code to duplicate the issue:

USE master ;
CREATE DATABASE RedGate2005 ;
ALTER DATABASE RedGate2005 SET COMPATIBILITY_LEVEL = 90 ;
GO
USE RedGate2005 ;
CREATE TABLE Test (
     ID int,
     String varchar(100),
     IsObsolete bit
    )
CREATE INDEX ixTest_IDString_NN ON Test (ID) WHERE IsObsolete = 0 ;
GO
CREATE DATABASE RedGate2008 ;
ALTER DATABASE RedGate2008 SET COMPATIBILITY_LEVEL = 100 ;
GO
USE RedGate2008 ;
CREATE TABLE Test (
     ID int,
     String varchar(100),
     IsObsolete bit
    )
CREATE INDEX ixTest_IDString_NN ON Test (ID) WHERE IsObsolete = 0 ;
GO

If you run a compare with SQL Compare 9.0.0.79, you will see the following results: