The filter expression of a filtered index is lost when a table is modified by the Table Designer

The filter expression of a filtered index is lost when a table is modified by the Table Designer by Razvan Socol

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	462053
Opened:	5/30/2009 5:14:14 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When the Table Designer is used to modify a table that has a filtered index, if the modification requires the table to be recreated, the filtered index is recreated without the filter condition.

This may cause an error right away (if there is data that would violate the uniqueness without the filter condition) or worse, the error can pass undetected, causing problems months later when data is entered into the table (when the DB developer may already have forgotten which is the correct filter condition).

Details (expand)

English

SQL Server 2008 SP1

Tools (SSMS, Agent, Profiler, etc.)

Windows Vista 64-bit SP1

US English

1. Create a table with a filtered index, for example:

CREATE TABLE T1(x int PRIMARY KEY, y int, z bit)
INSERT INTO T1 VALUES (1,1,0), (2,1,0), (3,1,1), (4,2,0), (5,2,0), (6,2,1), (7,3,0), (8,3,0), (9,3,1)
CREATE UNIQUE INDEX I ON T1(y) WHERE z=1

2. In Management Studio, go to Tools / Options / Designers / Table and Database Designers and uncheck "Prevent saving changes that require table re-creation"

3. In Object Explorer, right click on the table and choose Design

4. Make a modification that requires the table to be re-created (for example, move the y column after the z column) and click Save.

5. Click Generate Change Script and look for the "CREATE UNIQUE INDEX" statement.

At step 4, the following error appears:

Unable to create index 'I'.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.T1' and the index name 'I'. The duplicate key value is (1).

At step 5, we can see that the script to recreate the index does not contain the filter condition ("WHERE z=1").

The table should be modified correctly and the index should be recreated as it was, with the filter condition.

X64

0 attachments

Posted by Microsoft on 8/5/2011 at 3:47 AM

Hi,

Thanks for reporting this issue. This issue has been fixed in internal builds and will be available in an upcoming release of SQL Server "Denali".

Thanks,
Sravanthi Andhavarapu,
SQL Server Manageability Team

Posted by Microsoft on 3/29/2011 at 2:17 AM

Hello:

Greetings from the Microsoft SQL Server Manageability Team.

We triaged this bug along with several others that had come in. Unfortunately, given the work involved in this fix, and our schedule/resources, we regret to tell you that, we will not be able to fix this issue in the near future.

Thanks much for writing into Microsoft.

Cheers,

Chandramouli
SQL Server Manageability

Posted by Microsoft on 7/14/2010 at 9:28 PM

Hi,
Thanks for the update. the designers do three things: They either alter or drop and recreate entities for you. As of now, the drop and recreate scenarios havent been enhanced to support all new 2008 features. That is the reason we also surface an warning saying, modifying tables using designers for the drop and recreate scenario is not adviced. They will probably be a different solution when we revamp our designers. But for now, this is a known issue. Do not use the designer for drop and re-create scenarios,
You could use scripts for the same.
Regards
Vinod

Posted by Gio on 2/27/2010 at 10:20 PM

Original bug has been submitter more than 18 months ago!!!!!
Error is very subtle and dangerous since opening and saving a table changes filters and make data insertion impossibile in some conditions

If you have the following condition:
Column A => nvarchar(33), null

and you wnat to have a unique index allowing for duplicate null values (column A values should be unique when different from null, but multiple null values should be accepted).

Result: Idx_Table_Column A is unique with filter "Column_A is not null";
Then you open the table in design mode, add a field and save: the result is the filter condition being changed and reset to the database without any warning; removing implicitly the "Column_A is not null" makes impossible to insert data in the table due to uniqueness feature of the index....

Any other way of having a unique index but still allowing for multiple null values (which is by default in ANSI std but not in SQL server) and having a unique index when value is not null ?

We also updated our test server to SQL Server SP1 Cumulative Update 6, BUT THE BUG IS STILL THERE!!!

How long will it take to sort it out @MS ?

Posted by Brandon.Tucker on 9/22/2009 at 9:44 AM

Probably a dupe of https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=362699