Sql-server – SQL Server comparing dates algorithm

datetimesql server

How is the comparison between dates (with times) actually implemented?

Consider the following query for example:

SELECT * 
FROM dbo.Table1 
WHERE DateTimeValue >= '2016-01-01 00:00:000' 
  AND DateTimeValue < '2016-01-03 00:00:000'

The above internal contains exact two days, no millisecond more or less.

So, how does SQL Server compare the values in the column with the values in the interval?

String comparison?

This seems not so bad, but if you see the execution plan, you'll notice that the SQL Server is using CONVERT_IMPLICIT to cast the string value to datetime.

Integer comparison?

This sounds ridiculous if you think how many milliseconds there are in one day.

Does anyone know how the comparison is implemented internally in SQL server ?

Best Answer

Assuming DateTimeValue is a DATETIME column or similar then both '2016-01-01 00:00:000' and '2016-01-03 00:00:000' are going to get cast to that type and the comparison is done using the natural date comparison routine. Of course if you have something odd like dates stored as strings then yes it may perform a string comparison.

Internally DATETIME is essentially a large integer type (it is actually implemented as two integers but for the purpose of this discussion that difference is purely academic) so the natural comparison is integer comparison.

The conversion of your input strings to DATETIME is not going to take significant resources: it will be done once for the whole query and not for every row that gets considered.

If you have a relevant index for DateTimeValue then a ranged query will be used on that index (a seek to 2016-01-01 or the first value above that then a scan down the index until the values seen stop being less than 2016-01-03. This should be fairly efficient though you will of course have heap/cluster lookups going on if that index isn't your clustered one or covers all the columns. If you look at the query plan for such a query in SSMS you should see that index seek and related row lookups clearly.

So it is an integer comparison, but the other way around to the one you call ridiculous (which you are right, would be silly). There will be a few integer comparisons as it works down the B-Tree of the index to find the first relevant row followed by one comparison per subsequent row as it asks "have we hit 2016-01-03 or higher yet?", not one comparison for every possible value in the range. It might help to think of how you would go about following the instruction "list all the words from your dictionary that are between index and iteration" - that is essentially what SQL Server is doing with the datetime range search.

Related Question