MSSQL The most optimal query for a date range during the day or a daily range

Question

I wanted to know what the “best practice” approach to a query is with a set of date and time records with a date (no time).

I use several queries that return records based on a date range from a record set that uses the datetime data type, which means that every record needs to be checked using a range.

Request example:

Select * FROM Usages where CreationDateTime between '1/1/2012' AND '1/2/2012 11:59:59' 

I know that using BETWEEN is a hog resource, and checking the date and time data type will always be a very rich resource, but I would like to hear what others are using (or will be using) in this situation.

Can I get any performance improvement converting a datetime record to Date as:

 Select * FROM Usages where CONVERT(DATE,CreationDateTime) between '1/1/2012' AND '1/2/2012' 

Or maybe make a check less / more than?

 Select * FROM Usages where (CreationDateTime > '1/1/2012') AND (CreationDateTime < '1/2/2012 11:59:59') 

Answer 1

What, in your opinion, you know is wrong.

Neither the use of BETWEEN data types nor DATETIME is a hog resource.

When indexing a column that the column is really DATETIME and not VARCHAR (), and that you are not wrapping the field in a function, everything will be fine and fast.

However, I would use >= and < . Not for performance, but for logical correctness.

 WHERE myField >= '20120101' AND myField < '20120102' 

This will work whether the field contains hours, minutes or even (with mythical data type) peak seconds.

With an index in the field, it will also give a range scan.

You will not get faster. No tricks or features required.

Answer 2

There are several considerations regarding dates.

First, you want to be sure that the appropriate indexes are used. In general, this means that you are not executing functions in a column. This applies to data types other than dates, but acts as predominant for understanding dates. Therefore, CONVERT () is a bad idea in terms of performance, assuming the column is indexed.

Secondly, you want to avoid unnecessary conversions between formats. Thus, a function call must be made for each line. Instead, converting a constant string to a date / time occurs once at compile time. The first one is less effective. Another reason to avoid CONVERT (). However, in many queries, other processing (such as merging) is much more time consuming than conversions, so this may not be important.

As for the choice between “between” and signed transactions. Best practice is to use "<" and ">" and "> =" and "<=". This makes the logic clearer for dates and has no problem with things like seconds accurate to 3 ms.

As far as I know, between dates works just as efficiently using indexes as other types of fields. However, for accuracy and portability it is best to conduct individual comparisons.

So, the third version will be preferable.