SQL Server 2008 and Testing – Filtered Indexes

SQL Server 2008, due to be released within a few months, has a ton of new features. I’ve been looking at RC0 (release candidate 0), trying to determine the implications SQL Server 2008 has for testing. The first step in such an analysis is to understand exactly what the new features of SQL Server 2008 are. One of the interesting new features is a new filtered index. A regular index on a column in a table greatly increases the speed of SELECT statements which search the indexed column in very much the same way that a book index greatly increases how quickly you can find a term in a book. The new filtered index feature of SQL allows you to create an index for a specified range of values for a particular column. This could be useful if the majority of column values fall into a particular range, and yet the column values can be greatly varied. For example, suppose you have a database of book information. And suppose that the vast majority of your book prices fall between $19.99 and $49.99 but you also have a few books that cost less than $19.99 as well as a few books that costs several hundred dollars. You can create a special filtered index that will be used for searches for book prices between $19.99 and $49.99 as the following demo shows.
— Filtered Indexes Demo
use master
if exists(select name from sys.sysdatabases where name=’dbBooks’)
 drop database dbBooks
create database dbBooks
use dbBooks
create table tblBooks
b_id int primary key,
b_title varchar (300) not null,
b_price money null,
b_pages int
insert into tblBooks values(1111,’Amazing Atoms’,29.95,387)
insert into tblBooks values(2222,’Blazing Beakers’,49.00,422)
insert into tblBooks values(3333,’Cool Centrifuges’,35.49,512)
select * from tblBooks
— a normal index to increase performance on regular columns
create nonclustered index ixBooksTitle on tblBooks(b_title)
— a filtered index for columns where most data is in a range
create nonclustered index ixBooksPrice on tblBooks(b_price)
where (b_price >= 19.99 and b_price <= 49.99)
— selects now use faster filtered indexes if appropriate??
select b_title,b_price from tblBooks
where b_price >= 30.00 and b_price <= 39.99
— end script
As you can see, a filtered index is simply a regular SQL index with an added WHERE clause. The real trick is knowing when the performance improvement of creating a filtered index will outweigh the overhead cost of creating and storing the filtered index. I see this as something that a software tester would have to investigate. Additionally, in my demo script above, the filtered index might not help because my SELECT references both the b_title and b_price columns, therefore the execution may have to perform a scan of the entire table. You’d have to examine the execution plan to be sure. (In fact, as far as I can tell the filtered index is not used for the query).
This entry was posted in Software Test Automation. Bookmark the permalink.

One Response to SQL Server 2008 and Testing – Filtered Indexes

  1. Conan says:

    Don\’t forget about Reporting Services & the new report design tools! A great for any tester that needs to communicate results. 
    This is kind of a crazy question but have you ever talked to anyone about how you would test a report or Analysis Server cubes? Reports can be quite complex, the equivilant of a small distributed application.  I worked with the RS team testing the designer, but I always wondered how you would go about testing things like reports, analysis cubes, etc.

Comments are closed.