Posted by Erland Sommarskog on 08/28/05 19:17

christopher.secord@gmail.com (christopher.secord@gmail.com) writes:
> Thanks for the advice. I've decided to add a column of type int
> (indentity) and make that the clustered index. The result will be the
> same as what I was looking for in indexing publishdate, but it's on an
> simple integer field.

Say that you have a query:

SELECT * FROM tbl WHERE publishdate BETWEEN '20050601' AND '20050630'

If you have a clustered index on publishdate, SQL Server will do a
clustered index seek in this interval and find the rows quickly. If you
cluster on the identity column instead, SQL Server will have to scan
the entire table. If you add a non-clustered index on publishdate, SQL
Server may use that index, but if the interval is too wide, it will estimate
that nc-index + bookmark lookup is too expensive, and scan the table
nevertheless.

This is because, there is no way that SQL Server can assert that the
date follows the identity column.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]