Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.

Bug in Smart Rename re: Filtered Indexes

GGinBerkeleyGGinBerkeley Posts: 6
edited August 19, 2015 9:49AM in SQL Prompt
I encountered a bug when trying to rename a table that contains a filtered index. The generated script from Smart Rename does not include the Where clause on the filtered index, resulting in a runtime error.

To repro:
CREATE TABLE [Foo](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SSN] [char](9) NULL,
       CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [IXUF_Foo_SSN] ON [Foo]
(
	[SSN] ASC
)
WHERE ([SSN] IS NOT NULL)
ON [PRIMARY]
GO


Add a few records to Foo - some with SSN as Null, and with SSN entered. The filtered index ensures only unique SSN's, but allows them to be NULL if the SSN has not been entered.

Now...try to Smart Rename table Foo. It will fail due to data, because the IXUF_Foo_SSN constraint is missing its WHERE clause. The generated SQL forgot to include the filtered part of the index.

Comments

Sign In or Register to comment.