THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rick Heiges

News about SQL Server and the SQL Server Community

Filtered Indexes

In SQL Server 2008, you can have what is called a "Filtered Index".  What does this mean? 

An index may be created for a subset of rows in your table.  For instance you could create a filtered index for a "state" column if the value is "CA" for California; the rest of the states would not have a value.  When you create your index, simply add a WHERE clause to activate this feature. 

In the state example, it probably does not make sense to have an index just for California, but there are cases where this could save a lot of space.  For example, if a table had a column that for most rows had no data, a filtered index could be built on it where the column is not null. 

I used FoxPro in a previous life.  This was a feature available to us way back in the 90s.  We used FoxPro and this feature in DOS, WfW3.11, and SCO Unix.  It has been a while, but there was a time when we created an index just for this one particular customer because of the proceessing time it took.  Disk space was more scarce back then and filtered indexes helped us squeeze the most out of it.  I am happy to see this feature in SQL Server 2008. I have only started to play with this and expect that some limitations will pop-up. 

 

Published Tuesday, March 18, 2008 1:47 PM by RickHeiges

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul Nielsen said:

Hey Rick,  I agree! I think filtered indexes will be one of the big hits in SQL Server 2008.

The example I've been using is a manufacturing production system that contains millions of work orders but only 2K are currently open work orders. A filtered index where status = 'open' would be perfect.

-Paul

March 18, 2008 4:41 PM
 

jerryhung said:

Very nice, I didn't read that in the new features for SQL 2008

will be eager to try it out, to compare the performance difference

March 20, 2008 11:43 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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