Tools (SSMS, Agent, Profiler, etc.)
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").