Sql-server – Only Filtered condition gets deleted from index after datatype change using SSMS

indexsql-server-2008-r2ssms

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?

Best Answer

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

enter image description here

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

Related Solutions

SQL Server Hierarchical Order – Parent-Child Tree Hierarchical ORDER BY

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

SQL Server – How to Return Both New and Old Values During an UPDATE

The default READ COMMITTED transaction isolation level guarantees that your transaction will not read uncommitted data. It does not guarantee that any data you read will remain the same if you read it again (repeatable reads) or that new data will not appear (phantoms).

These same considerations apply to multiple data accesses within the same statement.

Your UPDATE statement produces a plan that accesses the Transactions table more than once, so it is susceptible to effects caused by non-repeatable reads and phantoms.

Multiple access

There are multiple ways for this plan to produce results you do not expect under READ COMMITTED isolation.

An example

The first Transactions table access finds rows that have a status of WaitingList. The second access counts the number of entries (for the same job) that have a status of Booked. The first access may return only the later transaction (the earlier one is Booked at this point). When the second (counting) access occurs, the earlier transaction has been changed to WaitingList. The later row therefore qualifies for the update to Booked status.

Solutions

There are several ways to set the isolation semantics to get the results you are after. One option is to enable READ_COMMITTED_SNAPSHOT for the database. This provides statement-level read consistency for statements running at the default isolation level. Non-repeatable reads and phantoms are not possible under read committed snapshot isolation.

Other remarks

I have to say though that I would not have designed the schema or query this way. There is rather more work involved than should be necessary to meet the stated business requirement. Perhaps this is partly the result of the simplifications in the question, in any case that is a separate question.

The behaviour you are seeing does not represent a bug of any kind. The scripts produce correct results given the requested isolation semantics. Concurrency effects like this are also not limited to plans which access data multiple times.

The read committed isolation level provides many fewer guarantees than are commonly assumed. For example, skipping rows and/or reading the same row more than once is perfectly possible.