Red Gate forums :: View topic - A small problem with filtered indexes
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

A small problem with filtered indexes

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Igor.Tochansky



Joined: 04 May 2012
Posts: 3

PostPosted: Fri May 04, 2012 2:58 pm    Post subject: A small problem with filtered indexes Reply with quote

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)
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6587

PostPosted: Mon May 07, 2012 3:27 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Igor.Tochansky



Joined: 04 May 2012
Posts: 3

PostPosted: Tue May 08, 2012 10:59 am    Post subject: Steps to reproduce Reply with quote

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:

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:

create table TBL
(
   col1 int not null
)

create index IX1_TBL on TBL (col1) where col1 = 0
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6587

PostPosted: Tue May 08, 2012 2:07 pm    Post subject: Reply with quote

Thanks - I think this should work with compatibility level 90. I've logged a bug for this (SC-5759).
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6587

PostPosted: Tue May 08, 2012 3:02 pm    Post subject: Reply with quote

Oh, maybe not - I just ran your script on a real SQL 2005 and got the error
Quote:
Msg156, Level 15, Line 6
Incorrect syntax near the keyword 'where'.

I'm going to close the bug.
Back to top
View user's profile Send private message
Igor.Tochansky



Joined: 04 May 2012
Posts: 3

PostPosted: Tue May 08, 2012 6:13 pm    Post subject: Reply with quote

Sorry for this, you are right: filtered indexes have been added since 2008 server.
Probably, you should close the bug.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group