Filtered Index and Statistics in SQL Server 2008

Filtered Index and Statistics in SQL Server 2008

By : Kasim Wirama, MCDBA

 

Up to SQL Server 2005, there is no way to create index for only subset rows in a table though some value is needed to query. Imagine that a table has million number of rows and only certain value in a column is queried in filtering using WHERE clause of SELECT query. It needs additional space to keep tracking for those million of rows and rebuild or reorganize those index will take longer to finish. SQL Server 2008 introduces filtered index and statistics that will give you better option to generate index only subset of rows that are needed to satisfy query with certain criteria. The syntax of index declaration in SQL Server 2008 is :

CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX <index name>
ON <schema name>.<table name> (<column to be index>,….)
[INCLUDE (<covered column,…..>)]
[WHERE <column to be filtered> <operand> <value for filtering>]

Operand here includes >, >=, <, <=, IS NULL, IS NOT NULL, IN, <>, =. BETWEEN currently isn’t supported in SQL Server 2008 filtered index. Some limitation in filtered index is that it doesn’t support computed column, indexed view, UDT, hierarchyid data type, and geometry/geography data type.

To demonstrate use of filtered index, I use AdventureWorks database for SQL Server 2008.

Example :

1.       To filter currencyRateID column only for not null value in sales.salesorderheader table, create the following filtered index here :

 create index ix_currencyrateid on sales.salesorderheader(currencyrateid)

where currencyrateid is not null

 

This index will be used by optimizer for this following query :

select * from Sales.SalesOrderHeader as sohwhere soh.CurrencyRateID = 6486 

Execution plan for the query is :

  |--Compute Scalar(DEFINE:([soh].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber] as [soh].[SalesOrderNumber], [soh].[TotalDue]=[AdventureWorks].[Sales].[SalesOrderHeader].[TotalDue] as [soh].[TotalDue]))

       |--Nested Loops(Inner Join, OUTER REFERENCES:([soh].[SalesOrderID]))

            |--Compute Scalar(DEFINE:([soh].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID],0),N'*** ERROR ***')))

            |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[ix_currencyrateid] AS [soh]), SEEK:([soh].[CurrencyRateID]=(6486)) ORDERED FORWARD)

            |--Compute Scalar(DEFINE:([soh].[TotalDue]=isnull(([AdventureWorks].[Sales].[SalesOrderHeader].[SubTotal] as [soh].[SubTotal]+[AdventureWorks].[Sales].[SalesOrderHeader].[TaxAmt] as [soh].[TaxAmt])+[AdventureWorks].[Sales].[SalesOrderHeader].[Freight] as [soh].[Freight],($0.0000))))

                 |--Clustered Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [soh]), SEEK:([soh].[SalesOrderID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID]) LOOKUP ORDERED FORWARD)

 

Notice the bold section, optimizer uses ix_1 index for query with currencyrateid filter.

 

2.       Filtered index can also covered range, for example a query just query freight cost between 300 and 1000, so this index will satisfy the query condition.

 create index ix_300_to_1000 on sales.salesorderheader(freight)

where freight >= 300 and freight <= 1000;

 

Here is query sample where optimizer will use ix_300_to_1000 index.

select * from Sales.SalesOrderHeader as sohwhere soh.Freight between 500 and 700; 

Execution plan for above query is :

  |--Compute Scalar(DEFINE:([soh].[SalesOrderNumber]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber] as [soh].[SalesOrderNumber], [soh].[TotalDue]=[AdventureWorks].[Sales].[SalesOrderHeader].[TotalDue] as [soh].[TotalDue]))

       |--Nested Loops(Inner Join, OUTER REFERENCES:([soh].[SalesOrderID], [Expr1004]) WITH UNORDERED PREFETCH)

            |--Compute Scalar(DEFINE:([soh].[SalesOrderNumber]=isnull(N'SO'+CONVERT(nvarchar(23),[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID],0),N'*** ERROR ***')))

            |    |--Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[ix_300_to_1000] AS [soh]), SEEK:([soh].[Freight] >= ($500.0000) AND [soh].[Freight] <= ($700.0000)) ORDERED FORWARD)

            |--Compute Scalar(DEFINE:([soh].[TotalDue]=isnull(([AdventureWorks].[Sales].[SalesOrderHeader].[SubTotal] as [soh].[SubTotal]+[AdventureWorks].[Sales].[SalesOrderHeader].[TaxAmt] as [soh].[TaxAmt])+[AdventureWorks].[Sales].[SalesOrderHeader].[Freight] as [soh].[Freight],($0.0000))))

                 |--Clustered Index Seek(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [soh]), SEEK:([soh].[SalesOrderID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID]) LOOKUP ORDERED FORWARD)

 

Notice the bold section, it seems that optimizer uses filtered index (ix_300_to_1000).

 

Not only SQL Server 2008 could create filtered index, but also similar syntax applies to statistics. Here is the syntax for filtered statistics :

CREATE STATISTICS <statistics name>
ON <schema name>.<table name> (<column to be index>,….)
[WHERE <column to be filtered> <operand> <value for filtering>]

Share this post: | | | |
Published Sunday, September 21, 2008 9:55 AM by Kasim.Wirama
Filed under:

Comments

# re: Filtered Index and Statistics in SQL Server 2008

Saturday, November 15, 2008 10:46 AM by vidyifi

88TnuD  <a href="vakhnnjhmpgp.com/.../a>, [url=http://ngnkvqkyktai.com/]ngnkvqkyktai[/url], [link=http://jekaeuewegrj.com/]jekaeuewegrj[/link], http://sgvjevssqjso.com/

Powered by Community Server (Commercial Edition), by Telligent Systems