What is the best way to store DateTime in SQL Server to get the best lookup speed on a large table?

What's the best way to store DateTime

in SQL Server to get the best lookup speed for a large table? The table contains records and one row should contain the date and time.

Searches are similar to

Value > '2008-1-1' AND Value < '2009-1-1'



Value > '2008-1-1' AND Value < '2008-31-1'


and etc.

What's better? DateTime

with an index? Unixstamp at long

with index? Multiple fields int

like year, month, day, etc.? Or something different?


source to share

3 answers

I would use the smallest datatype that supports whatever level of date-time precision you need. eg

  • datetime2 if you need high precision up to 100 nanoseconds (6-8 bytes) (SQL 2008+)
  • datetime for precision up to 3.33ms (8 bytes)
  • smalldatetime for minute precision (4 bytes)
  • date for precision to day (no time preservation, 3 bytes) (SQL 2008+)

You are not saying how big the table you are talking about is. But there are strategies for working with improving query performance in addition to the standard indexing strategy (e.g. partitioning, filtered indexes)



Your example only displays the date and time element. If you only want a date, then use the relatively new DATE type instead of DATETIME. It is smaller and the index should be fast.



If you choose a smaller data type, you can store more records on a single page, so there will be less I / O, so it will run faster.

Of course, introducing indexes can improve performance. Indexes should contain as few columns as possible to store the maximum number of records per page.


Premature optimization is the root of all evil

For the former, you must store the date as a date, given the precision you want. If you later run into some performance issues, you need to figure out which queries are having performance issues and may introduce some indexes, but you should make sure that those indexes cover your queries.



All Articles