Systems Engineering and RDBMS

Filtered Indexes and Statistics in SQL Server 2008

Posted by decipherinfosys on March 20, 2008

Filtered indexes have been introduced in SQL Server 2008 as a means of optimizing the non-clustered indexes. Suppose that you have a column which has a range of status values ranging from 0 to 99. However, the application only queries the data that lies between 20 and 30. A filtered index will be suited for this scenario since it will have the data in a well defined subset. So, what would be the benefit of having this index on a sub-set of the data? Storage definitely is the first thing that comes to mind i.e. the storage needs for the index will be reduced which goes hand in hand with lower maintenance overhead since the rebuild or update of the stats will take lesser time in this case. In addition to these, it is good for performance as well since it is smaller in size as compared to the regular non-clustered index.

And there are other creative ways of using it as well. If you have read one of our previous blog post on ways to allow a unique index in SQL Server and DB2 LUW to allow more than one unique value, what we had provided was a work-around. One can make use of filtered indexes to create such a constraint. Let’s use the same example that we had used in that post:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

insert into test_uq (col2) values (’abc’);
insert into test_uq (col2) values (’xyz’);
insert into test_uq (col2) values (Null);
insert into test_uq (col2) values (Null);

Now, let’s create the Unique index as a filtered index:

create unique index Decipher.TEST_UQ_IND_1 on Decipher.test_uq(col2) where Decipher.test_uq(col2) is not null;

By making use of the filtered index, we have removed the NULL values while enforcing the constraint. Another thing to keep in mind is that filtered statistics have also been introduced in SQL Server 2008 (to see the difference between indexes and statistics, see our previous blog post). Filtered Statistics are essentially created on a sub-set of the data to help improve the performance of queries that select from a defined sub-set (say only specific status codes, only open orders, only specific billing codes etc.).

You can read up more in the online BOL version.

3 Responses to “Filtered Indexes and Statistics in SQL Server 2008”

  1. […] those columns in here that had values provided for them. Sparse columns also work very well with filtered indexes. So, to sum up, the advantages are […]

  2. […] if you are using SQL Server 2008, you can use filtered indexes as […]

  3. […] Decipherinfosys along with a good description on the difference of indexes and statistics if you need some […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: