SQL Server Home
The filter expression of a filtered index is lost when a table is modified by the Table Designer
as Won't Fix
5/30/2009 5:14:14 AM
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).
SQL Server 2008 SP1
Tools (SSMS, Agent, Profiler, etc.)
Windows Vista 64-bit SP1
Operating System Language
Steps to Reproduce
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.
to post a comment.
Please enter a comment.
on 11/7/2013 at 10:36 PM
The bug is fixed in SQL Server 2012, but it is not fixed in SQL Server 2008 or SQL Server 2008 R2. The "Won't fix" resolution probably applies to the specific version mentioned in this bug report.
on 11/6/2013 at 5:47 AM
Is this fixed or not? The below comment says it is fixed, but the Bug status shows "Closed won't fix" (which would be a great pity!)
on 8/5/2011 at 3:47 AM
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".
SQL Server Manageability Team
on 3/29/2011 at 2:17 AM
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.
SQL Server Manageability
on 7/14/2010 at 9:28 PM
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.
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 ?
on 9/22/2009 at 9:44 AM
Probably a dupe of https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=362699
to post a workaround.
Please enter a workaround.
© 2013 Microsoft