CODEWITHSUNDEEP
sqlsql-server
Ross
Ross

Reputation: 83

SQL Server: Nonclustered Date Index

I would like to optimize performance of my Logs table by adding an nonclustered index on my LogDate (DateTime) column to improve performance since most of my queries will include a time frame (From Date - To Date). The Thread bellow recommends rounding to the closest hour.

How to improve performance for datetime filtering in SQL Server?

I'm looking for an best practice example & advice of an index on a datetime column to improve performance.

Thanks in advance

Upvotes: 1

Views: 2112

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46330

The referenced answer in your question is either wrong or missing some critical details. Adding an additional datetime column with a rounded value will not reduce the index size. The datetime data type is a fixed 8 bytes regardless of the value stored. To realize a reduced index size, one would need to use a different data type, such as smalldatetime (which is only 4 bytes with resolution of no more than one minute), and/or use data compression.

If queries most often specify a datetime range alone, the clustered index should be on datetime column. A common anti-pattern I see in the wild is an clustered identity column primary key, which is serves no purpose. Log tables are an exception to the general rule that tables should have a primary key since they are not relational.

You may also need non-clustered indexes on frequently specified additional columns if they are fairly selective but should do so sparingly as to avoid impacting insert performance. Consider using datetime2 along with the maximum precision needed, possibly saving a byte or 2 per row. Also consider page compression for both the table and indexes. I've used this approach with multi-billion row log tables and realized excellent performance.

Upvotes: 1

Related Questions