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

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?

Answer 1

When you script the index you need to make sure the scripting option is not set to SQL Server 2005.

Tools / Options / SQL Server Object Explorer / Scripting / Script for server version

I suspect this is the problem (and it happened to me, too).