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?
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.
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 ?