Clever Geek Handbook

SQL Server: Indexing Date Column in Log Table

Example table:

CREATE TABLE Log (
    logID    int identity
    logDate  datetime
    logText  varchar(42)
)

logID is already indexed because it is the primary key, but if you want to query this table, most likely you want to use logDate as a constraint. However, both logID and logDate will be in the same order, because logDate will always be set to GETDATE ().

Does it make sense to add an additional non-clustered index to logDate given that fast writes are important for the log table.

+2


source to share


3 answers


Make clustered index logDate, logID (in order). Since the datetime "grows", it shouldn't cost you anything extra. logID saves you from having to insert two log records at the same time (can happen)



+6


source


If you have a lot of requests with

WHILE LogDate > ......

(or something similar) - yes, of course!



A clustered index on the LogID won't help if you select by date - so if it's a common operation, a non-clustered index on a day will definitely help a lot.

Mark

+3


source


Arthur argues with considering the logdate, logid clustered index, since you will be able to run duplicates in a datetime precision window (3.33ms on SQL 2005).

I would also think ahead of time if the table would be big and table splitting should be used to allow the log to have a sliding window and old data to be deleted without the need for long delete operations. It all depends on the number of log entries and whether you have a corporate version.

0


source






More articles:


All Articles