CODEWITHSUNDEEP
sql-server
Jeti
Jeti

Reputation: 246

Proper way to index date & time columns

I have a table with the following structure:

CREATE TABLE MyTable (
   ID int identity,
   Whatever varchar(100),
   MyTime time(2) NOT NULL,
   MyDate date NOT NULL,
   MyDateTime AS (DATEADD(DAY, DATEDIFF(DAY, '19000101', [MyDate]), 
                          CAST([MyDate] AS DATETIME2(2))))
)

The computed column adds date and time into a single datetime2 field.

Most queries against the table have one or more of the following clauses:

... WHERE MyDate < @filter1 and MyDate > @filter2
... ORDER BY MyDate, MyTime
... ORDER BY MyDateTime

In a nutshell, date is usually used for filtering, and full datetime is used for sorting.

Now for questions:

  1. What is the best way to set indices on those 3 date-time columns? 2 separate on date and time or maybe 1 on date and 1 on composite datetime, or something else? Quite a lot of inserts and updates occur on this table, and I'd like to avoid over-indexing.

  2. As I wrote this question, I noticed the long and kind of ugly computed column definition. I picked it up from somewhere a while ago and forgot to investigate if there's a simpler way of doing it. Is there any easier way of combining a date and time2 into a datetime2? Simple addition does not work, and I'm not sure if I should avoid casting to varchar, combining and casting back.

Upvotes: 4

Views: 18982

Answers (3)

richardtallent
richardtallent

Reputation: 35373

The answer isn't in your indexing, it's in your querying.

A single DateTime field should be used, or even SmallDateTime if that provides the range of dates and time resolution required by your application.

Index that column, then use queries like this:

 SELECT * FROM MyTable WHERE
    MyDate >= @startfilterdate
    AND MyDate < DATEADD(d, 1, @endfilterdate);

By using < on the end filter, it only includes results from sometime before midnight of that date, which is the day after the user-selected "end date". This is simpler and more accurate than adding 23:59:59, especially since stored times can include microseconds between 23:59:59 and 00:00:00.

Using persisted columns and indexes on them is a waste of server resources.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Having date and time in two separate columns may seem peculiar but if you have queries that use only the date (and/or especially only the time part), I think it's a valid decision. You can create an index on date only or on time or on (date, whatever), etc.

What I don't understand is why you also have the computed datetime column as well. There s no reason to store this value, too. It can easily be calculated when needed.

And if you need to order by datetime, you can use ORDER BY MyDate, MyTime. With an index on (MyDate, MyTime) this should be ok. Range datetime queries would also be using that index.

Upvotes: 2

marc_s
marc_s

Reputation: 754408

Unfortunately, you didn't mention what version of SQL Server you're using ....

But if you're on SQL Server 2008 or newer, you should turn this around:

your table should have 

MyDateTime DATETIME

and then define the "only date" column as 

MyDate AS CAST(MyDateTime AS DATE) PERSISTED

Since you make it persisted, it's stored along side the table data (and now calculated every time you query it), and you can easily index it now.

Same applies to the MyTime column.

Upvotes: 4

Related Questions