How can we help you today? How can we help you today?

Bug in Smart Rename re: Filtered Indexes

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.
GGinBerkeley
0

Comments

4 comments

Sort by
  • 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
    0
  • 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
    0
  • 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
    David Priddle
    0
  • rick
    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.
    rick
    0

Add comment

Please sign in to leave a comment.