A small problem with filtered indexes

Igor.Tochansky

Hello!

I have downloaded a SQL Compare today and found a small bug.
The problem is about filtered indexes.

In fact, they are compared right and are not included into the deployment script if they are the same.
However, I was confused a little bit when i was reviewing differences of my tables. I noticed that a "source" table never includes "where" clause for indexes but a "target" table does.

Version of Source Server:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Version of Target Server
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2789.0 (X64) Sep 28 2011 17:10:21 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

Brian Donahue

I'm sorry to say I cannot reproduce the problem that you describe. Comparing two live databases, I see the WHERE clause on the index on both sides. The only reliable way of getting this to happen is if you had set the target database to compatibility level 80. When this is done, the database behaves like SQL Server 2000, in which filtered indexes are not supported.

Igor.Tochansky

Hello Brian!

I reproduced it today on databases located on this env.:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


You are right, the problem is about compatibility levels.
I used value of "90" (2005) for "source" DB and value of "100" for "target" DB.
Both levels support filtered indexes.

The script to create "source":

create table TBL
(
	col1 int null
)

create index IX1_TBL on TBL (col1) where col1 = 0

The script to create "target" (the column is not null):

create table TBL
(
	col1 int not null
)

create index IX1_TBL on TBL (col1) where col1 = 0

Brian Donahue

Thanks - I think this should work with compatibility level 90. I've logged a bug for this (SC-5759).

Brian Donahue

Oh, maybe not - I just ran your script on a real SQL 2005 and got the error

Msg156, Level 15, Line 6
Incorrect syntax near the keyword 'where'.

I'm going to close the bug.

Igor.Tochansky

Sorry for this, you are right: filtered indexes have been added since 2008 server.
Probably, you should close the bug.