Using the same setup as in the SQL Fiddle. With the new USE HINTs that make doing some cardinality estimation time travel possible, we can see when things changed. This appears only to have been tested up to 2008R2. I know that this has a long-standing Great Answer® from Martin, but I wanted to add in some changes to the behavior here in newer versions of SQL Server. The following table shows the number of conjuncts guessed and the resultant selectivity as a function of input table cardinality of N: So in that case it appears the estimate is derived from this formula: If all rows in the table have the same datetime and it matches the predicate (e.g. The statistics aren't ignored completely. The CAST( AS DATE) query incorrectly estimates that only one row will match and produces a plan with key lookups. The second (range) query correctly estimates that 100 will match and uses a clustered index scan. This can be seen in an amended version of your SQL Fiddle.Īll 100 rows in the table now match the predicate (with datetimes 1 minute apart all on the same day). < '20130102' then discards all the rows.Īnother disadvantage of relying on it is that the cardinality estimates may not be as accurate as with the traditional range query. SELECT DISTINCT DATEADD(MINUTE, Num, Date)ĪND DateTimeCol Scalar Operator(),Įnd: DateTimeCol = '20130101'. Somewhat surprisingly this is not the same range as your literal values.Ĭreating a table with a row per page and 1440 rows per day CREATE TABLE T SQL Server calls an internal function GetRangeThroughConvert to get the start and end of the range. The mechanism behind the sargability of casting to date is called dynamic seek.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |