| Author |
Message |
Igor.Tochansky
Joined: 04 May 2012
Posts: 3
|
 Posted: Fri May 04, 2012 2:58 pm Post subject: A small problem with filtered indexes |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Mon May 07, 2012 3:27 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Igor.Tochansky
Joined: 04 May 2012
Posts: 3
|
| Posted: Tue May 08, 2012 10:59 am Post subject: Steps to reproduce |
|
|
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 |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Tue May 08, 2012 2:07 pm Post subject: |
|
|
Thanks - I think this should work with compatibility level 90. I've logged a bug for this (SC-5759).
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Tue May 08, 2012 3:02 pm Post subject: |
|
|
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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Igor.Tochansky
Joined: 04 May 2012
Posts: 3
|
| Posted: Tue May 08, 2012 6:13 pm Post subject: |
|
|
Sorry for this, you are right: filtered indexes have been added since 2008 server.
Probably, you should close the bug. |
|
| Back to top |
|
|
|
