Wednesday 18 July 2012

Filtered Non Clustered Index


Index is a most important factor for boosting up database performance. Choosing the right index for right columns in the table objects is one of the challenge factors, where performance is the key factors.
Here in this article I am trying to discuss related to FILTER Index.
Introduction
In Microsoft SQL Server 2008 introduce a special type of index called FILTERED index. Here the filter means the WHERE cluse. So the Filtered index is the Index with WHERE clause.
A filtered index is an optimized no clustered index, especially suited to cover queries that select
from a well-defined subset of data.

A well define filtered index can increase the performance of the query. Before going to depth, we are trying to enlarge the feature of the filtered index
Feature of the Filtered Index
The features are mentioned bellow.
1.    A well defined filter index is smaller than the full-table non clustered index. Filtered statistics are more accurate than the full-table statistics as they cover the rows within the filtered index and hence improve the Query performance and plan quality.
2.    The index is maintained when the data manipulation language (DML) operation affects the data in the Index. A filtered index reduces the index maintenance cost as the size is smaller than the full-table non clustered index and only maintained when the data of the filtered index are affected.
3.    Filtered index can reduce disk storage for non clustered indexes when a full-table index is not necessary. You can replace a full-table non clustered index with multiple filtered indexes without significantly increasing the storage requirements.
Where it is most suitable
We must understand where the filter index is most suitable before implement it to get the higher degree of performance from the query.
There is a table that has huge amount of data and we often select only a subset of that data.  For an example, we have a lot of NULL values in a column and we want to retrieve records with only non-NULL values. In MS SQL Server 2008 this type of columns is called Sparse Column.

In another scenario we have several categories of data in a particular column, but we often retrieve data only for a particular category value.

By this I mean to say that in the Query of Indexed column the WHERE statement is more or less fixed and the same WHERE statement is used in non clustered Filtered Index.

Implementation

-- The Base Table

CREATE TABLE Employee
(
       EmpID         INT PRIMARY KEY CLUSTERED,
       EmpName              VARCHAR(100) NOT NULL,
       HireDate      DATE NULL,
       DOJ                  DATE NULL,
)
GO
-- Creating a non clustered index on HireDate column

CREATE NONCLUSTERED INDEX NC_Employee_HireDate
ON Employee(HireDate)
GO
-- Creating a non clustered Filetered index on DOJ column

CREATE NONCLUSTERED INDEX FI_Employee_DOJ
ON Employee(DOJ)
WHERE DOJ IS NOT NULL -- >>the filter criteria for the index
GO
-- Inserting Rrecords

DECLARE @intCounter INT = 1
WHILE @intCounter <= 10000
       BEGIN
       IF (@intCounter % 100) = 0
              BEGIN
                     INSERT INTO Employee(EmpID, EmpName, HireDate, DOJ)
                     VALUES (@intCounter, 'ABC' + CAST(@intCounter AS VARCHAR),
                     CAST((GETDATE() - @intCounter) AS DATE)
                     , CAST((GETDATE() - @intCounter) AS DATE))
              END
       ELSE
              BEGIN
                     INSERT INTO Employee(EmpID, EmpName, HireDate, DOJ)
                     VALUES (@intCounter, 'ABC' + CAST(@intCounter AS VARCHAR),
                            NULL, NULL)
              END
       SET @intCounter = @intCounter + 1
       END


Execution Plan Analysis



Note

If the rows returned from the query are beyond the filtered index criteria, the optimizer will not use the filtered index. In this scenario, if we use a query hint to use the filtered index then in that case it will generate Error 8622.

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.



Hope you like it.


Posted by: MR. JOYDEEP DAS

7 comments:

  1. Nice solution... thanks Joydeep

    I am facing one problem " I have to modify the column of a table , before modifying this I want check all dependent SPs so I am using dependent_object facility of SSMS but the problem is when the SP has dynamic query then its not working properly" In next article can you give the idea please.

    ReplyDelete
    Replies
    1. you can check for table.columnname or just columnname text by using a simple query
      if you have admin permissions

      if you have a dynamic query first check for table and then logic for dynamic query

      Delete
  2. if u want query just email to me

    sivaram1249@gmail.com

    ReplyDelete
  3. --Now I will build my stored procedure that accepts a WHERE clause. For the purpose of
    --this example, I will assume that the WHERE clause was built dynamically from the calling client application.


    use AdventureWorks
    --select * from [SalesHistory]
    create PROCEDURE usp_GetSalesHistory
    (
    @WhereClause NVARCHAR(2000) = NULL,
    @TotalRowsReturned INT OUTPUT
    )
    AS
    BEGIN
    DECLARE @SelectStatement NVARCHAR(2000)
    DECLARE @FullStatement NVARCHAR(4000)
    DECLARE @ParameterList NVARCHAR(500)

    SET @ParameterList = '@TotalRowsReturned INT OUTPUT'

    SET @SelectStatement = 'SELECT @TotalRowsReturned = COUNT(*) FROM SalesHistory '
    SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')
    PRINT @FullStatement
    EXECUTE sp_executesql @FullStatement, @ParameterList, @TotalRowsReturned = @TotalRowsReturned OUTPUT
    END
    GO
    --procedure execution
    DECLARE @WhereClause NVARCHAR(2000), @TotalRowsReturned INT
    SET @WhereClause = 'WHERE Product = ''Computer'''
    EXECUTE usp_GetSalesHistory
    @WhereClause = @WhereClause,
    @TotalRowsReturned = @TotalRowsReturned OUTPUT

    SELECT @TotalRowsReturned
    GO

    ReplyDelete
  4. create proc test2
    as
    begin
    declare @sql varchar(100)
    set @sql ='select * from mytable where col1=23'
    exec (@sql)
    end

    ReplyDelete