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'
or
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
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)
source to share
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.
But...
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.
source to share