Indexing Date/Time fields.

[Thomas Bartkus]

If we have a date/time field and are doing frequent queries
WHERE {date/time field} BETWEEN TimeA AND TimeB

Does it make sense, query speed wise, to create an index on the date/time
field?

The reason I ask is that TimeA and TimeB are significant down to seconds.
My *assumption* is that with a large amount of data scatter very few records
will contain duplicate date/time points. And that under these circumstances,
indexing will be of little value since every date/time point will need to be
examined in detail. Date/time fields are actually floating point numbers
after all.

In other words, the assumption I am making is that if most of the data in a
field is unique, then indexing will yield little or no query performance
benefit.

Is my "assumption" valid?
Would an index on the date/time field speed up this type of query?

Thomas Bartkus

[Thomas Bartkus]

"Thomas Bartkus" <to*@dtsam.com> wrote in message
news:5a********************@telcove.net...

If we have a date/time field and are doing frequent queries
WHERE {date/time field} BETWEEN TimeA AND TimeB

Does it make sense, query speed wise, to create an index on the date/time
field?

The reason I ask is that TimeA and TimeB are significant down to seconds.
My *assumption* is that with a large amount of data scatter very few records will contain duplicate date/time points. And that under these circumstances, indexing will be of little value since every date/time point will need to be examined in detail. Date/time fields are actually floating point numbers
after all.

In other words, the assumption I am making is that if most of the data in a field is unique, then indexing will yield little or no query performance
benefit.

Is my "assumption" valid?
Would an index on the date/time field speed up this type of query?

Okay. I'll answer my own question just in case anyone is interested.

I ran a few tests and my assumption was *wrong*.

Indexing on a field that contains mostly or entirely discrete values helps
the query speed substantially. Indexing on date/time fields or floating
point fields with lots of data scatter does speed up queries involving those
fields in a WHERE clause restriction.

This was a surprise to me!
Thomas Bartkus