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
Nice solution... thanks Joydeep
ReplyDeleteI 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.
Thanks "Vikas"
DeleteCan u provide me the sample of the SP
you can check for table.columnname or just columnname text by using a simple query
Deleteif you have admin permissions
if you have a dynamic query first check for table and then logic for dynamic query
A want to alter the column
ReplyDeleteif u want query just email to me
ReplyDeletesivaram1249@gmail.com
--Now I will build my stored procedure that accepts a WHERE clause. For the purpose of
ReplyDelete--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
create proc test2
ReplyDeleteas
begin
declare @sql varchar(100)
set @sql ='select * from mytable where col1=23'
exec (@sql)
end