You are here:Home » tsql » Filtered Index - New feature of SQL Server 2008

Filtered Index - New feature of SQL Server 2008

Sometime maintenance of the indexes are taking so much time and they are high costly. Becuase of that i have excluded some of the haviely indexes from script of indexs maintance as all the rows of those tables are not used in the stored procedures to be quelified in where condition.

But the new feature of SQL Server 2008 - Filtered Index has resolved this problem. From this feature we can create index on table using where condition, means it will create an index on the quelified rows of columns.
It's really best feature because it will solve problem of storage of indexs and as well maintenance of the indexes and will require less storage. It will most usefull where we don't require to create an index on all the rows or frequenty updated data.

Let's see the some examples using "Filtered Index" feature and will check the execution plan using this index.

Creating Objects.

-- Dropping Indexes on the table
 
IF (OBJECT_ID('Ix_FilteredDate_WithoutFiltered') > 0)
DROP INDEX [Ix_FilteredDate_WithoutFiltered] ON [FilteredIndexTab] 
GO
IF (OBJECT_ID('Ix_NonFilteredDate_WithFiltered') > 0)
DROP INDEX [Ix_NonFilteredDate_WithFiltered] ON [FilteredIndexTab] 
GO

-- Dropping table if exists

IF (OBJECT_ID('FilteredIndexTab') > 0)
DROP TABLE [FilteredIndexTab]
GO

-- Creating table 

CREATE TABLE [FilteredIndexTab]
  (
     [Id]           INT PRIMARY KEY CLUSTERED
     ,[FilteredDate] DATE NULL
     ,[NonFilteredDate] Date NOT NULL
  )
GO 

-- creating noclustered index without filtered

CREATE NONCLUSTERED INDEX [Ix_NonFilteredDate_WithoutFiltered] ON [FilteredIndexTab]([NonFilteredDate])
GO

-- creating noclustered index with filtered in where condition

CREATE NONCLUSTERED INDEX [Ix_FilteredDate_WithFiltered] ON [FilteredIndexTab]([FilteredDate]) 
WHERE [FilteredDate] IS NOT NULL
GO

Inserting sample records.

-- Inserting records in created table

DECLARE @Counter INT = 1

WHILE(@Counter <= 50000)
BEGIN

INSERT INTO FilteredIndexTab([Id],[FilteredDate],[NonFilteredDate])
SELECT @Counter
, 
CASE  
  WHEN (@Counter%2) =0 
   THEN GETDATE()- @Counter 
 ELSE
   NULL 
 END  -- Pick some of NULL values

 , 
GETDATE() - @Counter -- Not pick any NULL values

SET @Counter += 1

END
GO
Checking Results.
-- Let's review performance of the script with bothe of the index created on table. 

-- Check the option "Include Actual Execution Plan" from Query menu or prees CTRL+M

SELECT [Id],[FilteredDate], [NonFilteredDate] FROM [FilteredIndexTab] 
WITH (INDEX (Ix_NonFilteredDate_WithoutFiltered))
WHERE [FilteredDate] IS NOT NULL
GO

SELECT [Id],[FilteredDate], [NonFilteredDate] FROM [FilteredIndexTab] 
WITH (INDEX (Ix_FilteredDate_WithFiltered))
WHERE [FilteredDate] IS NOT NULL
GO

Stop hurting SQL Server using this feature!!!