A small problem with filtered indexes

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

A small problem with filtered indexes

Postby Igor.Tochansky » Fri May 04, 2012 2:58 pm

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)
Igor.Tochansky
 
Posts: 3
Joined: Fri May 04, 2012 2:34 pm

Postby Brian Donahue » Mon May 07, 2012 3:27 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Steps to reproduce

Postby Igor.Tochansky » Tue May 08, 2012 10:59 am

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":
Code: Select all
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):
Code: Select all
create table TBL
(
   col1 int not null
)

create index IX1_TBL on TBL (col1) where col1 = 0
Igor.Tochansky
 
Posts: 3
Joined: Fri May 04, 2012 2:34 pm

Postby Brian Donahue » Tue May 08, 2012 2:07 pm

Thanks - I think this should work with compatibility level 90. I've logged a bug for this (SC-5759).
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Tue May 08, 2012 3:02 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Igor.Tochansky » Tue May 08, 2012 6:13 pm

Sorry for this, you are right: filtered indexes have been added since 2008 server.
Probably, you should close the bug.
Igor.Tochansky
 
Posts: 3
Joined: Fri May 04, 2012 2:34 pm


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest