MSSQL Most optimal query for date range for a day, or day range

Question

I wanted to find out what is the "best practices" approach to a query against a record set of datetime with a date (no time).

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

Example of a query would be:

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

I know using BETWEEN is a resource hog, and that checking a datetime data type of a date is always going to be very resource intense, but I would like to hear what others use (or would use) in this situation.

Would I get any type of performance increase converting the datetime record to a Date like:

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

Or possibly doing a check of less then / greater then?

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

Answer 1

What you think you know is not correct.

Neither using BETWEEN or DATETIME data types is a resource hog.

Provided that you index the column, that the column really is a DATETIME and not a VARCHAR(), and that you don't wrap the field in a function, everything will be nice and quick.

That said, I would use >= and < instead. Not for performance, but logical correctness.

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

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

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

You won't get any faster. No tricks or functions needed.