What are the challenges you face when working across database platforms? Take the survey
Options

Filtered index where clause not displayed in comparison view

Jason HannasJason Hannas Posts: 41 Bronze 3
edited March 2, 2011 11:19AM in SQL Compare Previous Versions
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:

FilteredIndexCompareIssue.png
Sign In or Register to comment.