locked
Only Filtered condition gets deleted from index after datatype change using SSMS RRS feed

  • Question

  • In SSMS if I create a filtered index we can see filtered condition in index properties. But if I change column type of a column involved in filtered index using SSMS , then after column type changes filtered index condition will be deleted .

    For example i created a filtered index for column of datatype varchar(50) . I changed datatype to varchar(100) by using SSMS. After changing datatype I check index properties for that column. Now only filtered condition was missing in properties.

    Then I tried to check what may be the reason behind this error. Now when I changed datatype I used "save change script window" and checked what SSMS really does. As we all know the SSMS recreates table index etc , but the index creation query here was missing the filtered condition. Below is part of "save change script window" query showing index creation which does not have filtered condition!!

    CREATE NONCLUSTERED INDEX filtered ON dbo.Table_1
    (
    t
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    So is this bug in SSMS? Or is there anything i am missing like settings in SSMS or anything else?

    Tuesday, November 4, 2014 11:29 AM

Answers

  • So is this bug in SSMS?


    Yes, the wizards and UI designer in SSMS are known to be a little bit buggy, you should always prefer to use plain T-SQL instead, especially in productive enviroment.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 4, 2014 11:53 AM
    Answerer