SQL Server: Indexing date column in a log table
indexing, logging, sql-server, sql-server-2005
Solution
Make a clustered index logDate, logID (in that order). As datetime is "growing" this should not cost anything extra. logID saves you from inserting two log entries at the same time (could happen)
Problem
Example table: ``` CREATE TABLE Log ( logID int identity logDate datetime logText varchar(42) ) ``` logID is already indexed because it is a primary key, but if you were to query this table you would likely want to use logDate as a constraint. However, both logID and logDate are going to be in the same order because logDate would always be set to GETDATE(). Does it make sense to put an extra non-clustered index on logDate, taking into account that for the Log table it is important to have fast writes.