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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 462053 Comments
Status Closed Workarounds
Type Bug Repros 13
Opened 5/30/2009 5:14:14 AM
Access Restriction Public


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).
Sign in to post a comment.
Posted by Razvan Socol 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.
Posted by Will Rayer 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!)
Posted by Microsoft 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".

Sravanthi Andhavarapu,
SQL Server Manageability Team
Posted by Microsoft 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
Posted by Microsoft 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.
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