SQL Server: Indexing date column in a log table

Question

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.

Answer 1

If you'll have lots of query with a

WHILE LogDate > ......

(or something similar) clause - yes, by all means!

The clustered index on the LogID will not help if you select by date - so if that's a common operation, a non-clustered index on the date will definitely help a lot.

Marc

Answer 2

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 logentries at the same time (could happen)

Answer 3

Arthur is spot on with as considering the clustered index on logdate, logid since you will potentialy run into duplicates within the window of the datetime accuracy (3.33ms on SQL 2005).

I would also consider in advance whether the table is going to be large and should use table partitioning to allow the log to have a sliding window and old data removed without the need for long running delete statements. That all depends on the quantity of log records and whether you have enterprise edition.