CODEWITHSUNDEEP
sql-serversearchdatetimeindexing
AgentFire
AgentFire

Reputation: 9780

What is the best way to store DateTime in SQL Server to provide maximum search speed at large table?

What is the best way to store DateTime in SQL Server to provide maximum search speed for large table? The table contains the records, and one row has to contain the date and time.

The searches are like

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

or

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

etc.

Which is the best? The DateTime with index? The unixstamp in long with index? Multiple int fields like year, month, day ect.? Or something else?

Upvotes: 3

Views: 2006

Answers (3)

Tom Kris
Tom Kris

Reputation: 1227

If you choose smaller data type, you can store more records on a single page, so there will be less IO operations, so it will work faster.

Of cource, introducing indexes may improve performance. Indexes should include as little columns as possible, to store maximum ammount of records on a single page.

But...

Premature optimization is root all of evil

For the first you should store date as date, considering precision you need. If later you face some performance issues, you have to investigate which queries have performance issues and may be introduce some indexes, but you have to make sure those indexes cover you queries.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

I'd use the smallest datatype that supports what level of datetime accuracy you need. e.g.

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

You don't mention how large a table you are talking. But there are strategies for dealing with improving query performance on top of standard indexing strategy (e.g. partitioning, filtered indices)

Upvotes: 2

Jimbo
Jimbo

Reputation: 2537

Your example shows only date and no time element. If you only need date then use the relatively new DATE type instead of DATETIME. It is smaller and with an index should be fast.

Upvotes: 1

Related Questions