SQL Server Performance Forum – Threads Archive
Clustered index on date column?
Hi all! I’ve just joined the forums, my apologies if this query is already covered in detail somewhere here, but I couldn’t find a specific answer. Am I right in thinking that if you have a searchable table with a date column and your search query sorts by date by default, the date column is a good choice for the clustered index, rather than the primary key? My date column initially defaults to GetDate(), but it gets updated to GetDate() again if a user renews the record in the future, so the order of the IDs in this table will not necessarily be in sync with the order of the date values. I presume doing this would make lookups by id a bit slower, but the main need for speed is in the searches, which tend to return large results sets initially sorted by date. Or are there other reasons for avoiding putting a clustered index on a date column? Also, is there any meaningful difference in terms of if / when one should do this between SQL Server 2000 and SQL Server 2005? Any advice would be much appreciated! Thanks, TomCheck this out:http://www.sqlskills.com/blogs/kimberly/CategoryView.aspx?category=Indexes The DATETIME data type is relatively wide at 8 bytes. This will affect any other nonclustered index on that table, as the clustered index keys are stored there as bookmark. Depending on how many rows are going to be stored in the table, this "overhead" might have a significant impact on the number of pages needed for the indices. This in turn requires SQL Server to read more pages than probably necessary which then results in more I/O activity. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
]]>