Bug in Smart Rename re: Filtered Indexes

GGinBerkeley

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.

David Priddle

Hi GGinBerkeley,

Thanks for sending this through! We can reproduce this here and I'm looking into a fix now.

Best regards,

David

David Priddle

Hi GGinBerkeley,

We've got a build working locally but since the fix touches a few risky areas we'd like to do a bit more testing before we release it to the public. We'll have a private build ready for you to test soon.

Best regards,

David

David Priddle

Hi GGinBerkeley,

We've got a private build for you try here with filtered indexes supported.

Do let me if this solves your issue.

Best regards,

David

rick@syncbak

This issue still/again exists in SQL Prompt Version 9.5.19.11631.
In renaming a column used in the where clauses of filtered indexes, the column name was not modified in the CREATE INDEX statements.