Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as:

SELECT * FROM Table WHERE DeletedDate IS NULL

Would it be best to add the DeletedDate to the clustered index or is it better practice to have a nonclustered index with the DeletedDate as an include?

share|improve this question
    
What RDBMS are you using? SQL Server? This sounds like a good candidate for a filtered index. Does the application really do a full column select (*)? Unless your nonclustered index is covering, you'll have key lookups. –  Thomas Stringer Apr 19 '13 at 19:25
    
Yes Sorry SQL Server 2008 R2 –  Lumpy Apr 19 '13 at 19:25
    
A large majority of the records will meet the Null criteria specified. Would I still want to use teh filtered index for this? I thought filtered indexes were most useful if they narrowed down the amount of records by a significant margine. –  Lumpy Apr 19 '13 at 19:31
    
The gains that you will see from having the predicate on the index as well as filtered statistics will depend largely on how filtered it is. Without seeing more information regarding your table and data, it is hard to give exact recommendations. But my comments (and answer) are food for thought. –  Thomas Stringer Apr 19 '13 at 19:39
    
@ThomasStringer - Thanks Thomas –  Lumpy Apr 19 '13 at 19:45
add comment

2 Answers

up vote 5 down vote accepted

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies). Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek. And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.

share|improve this answer
add comment

Clustered index key performs best to be unique, narrow, static and ever-increasing by itself. So in this case, the inclusion of DeletedDate actually result the clustered key becomes non-unique non-static (presuming the DeletedDate value could be changed).

The INCLUDE in the non-clustered index is useful to cover the query without having to perform a key lookup to the table. However, as the INCLUDE columns only stored in the index leaf level, it does not help in searching for the values of the query predicate (in this case, NULL)

A non-clustered index key of DeletedDate allows a more effective search on the range of records that satisfy the predicate (NULL). A filtered non-clustered index could further narrow down the subset (only NULL records) and provide a better performance as well as storage for the non-clustered index.

With your description showing that the query returns all columns from the table with a single predicate, You could create a single filtered non-clustered index key for DeletedDate with WHERE DeletedDate IS NULL. Test it and examine the execution plan.

share|improve this answer
    
@ypercube, that's correct. Edited my answer. –  Travis Gan Apr 19 '13 at 21:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.