A colleague was getting a duplicate key error when trying to add a new column to a table via the Table Designer in SQL Server Management Studio (2008R2 - not tested on other versions), despite there being no violating data in the table. After a bit of digging around, I tracked the problem down to what appears to be a bug in Table Designer when there is a unique, filtered index in place on the table and the table is being recreated (i.e. you’re adding a new column, but not at the end after all the existing columns).
Steps to reproduce
In SSMS in Tools -> Options -> Designers -> Table and Database Designers, uncheck the “Prevent saving changes that require table re-creation” option
Create table:
CREATE TABLE [dbo].[Test]
(
Column1 INTEGER NOT NULL,
Column2 INTEGER NOT NULL,
Column3 INTEGER NULL
);
- Create dummy data:
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 1);
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, NULL); -- OK as Column3 is NULL
- Now run the following, duplicate key error is correctly thrown:
-- Errors, Duplicate Key exception as expected
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 2);
So at this point we have 2 rows in the table, no violations of the unique filtered index.
Right click the table in SSMS -> Design
Insert a new column “Column4” before Column3 and the press Save.
The error that occurs is:
'Test' table
- Unable to create index 'IX_Test_Column1_Column2'.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Test' and the index name 'IX_Test_Column1_Column2'. The duplicate key value is (1, 1).
The statement has been terminated.
So what it appears to be doing, is losing the WHERE filter on the index. This can be confirmed by clicking “Generate change script” in the Table Designer instead of Save - at the end of the generated script:
CREATE UNIQUE NONCLUSTERED INDEX IX_Test_Column1_Column2 ON dbo.Test
(
Column1,
Column2
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Now, if there was no data in the table, or there were no rows with the same Column1 and Column2 value combination when you go into Table Designer, then you can save the table change and be blissfully unaware that the filter has been lost from the index. i.e. repeat the repro steps again, but this time move step 3 and 4 (insert dummy data) to the end of the process. The previously OK 2nd data row will now error upon insert.
Personally, I almost never use the Table Designer and as a safeguard, will be recommending to the rest of the team that the “Prevent saving changes that require table re-creation” option is checked as a basic guard.
Update:
The following Connect items relating to this issue:
The filter expression of a filtered index is lost when a table is modified by the table designer
2008 RTM, SSMS/Engine: Table designer doesn’t script WHERE clause in filtered indexes
Referring to comments in that 2nd item, my “Script for server version” setting was set to “SQL Server 2008 R2”.
Sounds like this may have been addressed in SQL 2012, but still a problem in 2008/2008R2.