· 7 years ago · Feb 28, 2019, 08:42 PM
1--------------------------------------------------------------------------------------------------------------------------------------
2-- Data setup
3--------------------------------------------------------------------------------------------------------------------------------------
4
5SELECT @@VERSION
6--Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64)
7-- Oct 18 2018 23:11:05
8-- Copyright (C) 2017 Microsoft Corporation
9-- Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
10GO
11
12-- This repro works on any database, but we'll use tempdb since all servers have that database
13USE tempdb
14GO
15-- For this script, we use the most recent compatibility level
16-- However, the bug also reproduces with any of the following query hints:
17 -- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110'))
18 -- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120'))
19 -- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130'))
20ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 140
21GO
22
23DROP TABLE IF EXISTS #numbers
24SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n
25INTO #numbers
26FROM sys.all_columns c1
27CROSS JOIN sys.all_columns c2
28GO
29
30CREATE TABLE dbo.randomDateTimesIn2018 (
31 randomDateTime DATETIME NOT NULL
32)
33GO
34
35INSERT INTO dbo.randomDateTimesIn2018 WITH (TABLOCK) (randomDateTime)
36SELECT DATEADD(SECOND, secondOffset, DATEADD(DAY, dayOffset, '2018-01-01')) AS randomDateTime
37FROM (
38 SELECT ABS(CRYPT_GEN_RANDOM(16) % 365) AS dayOffset,
39 ABS(CRYPT_GEN_RANDOM(16) % 86400) AS secondOffset
40 FROM #numbers n1
41 CROSS JOIN #numbers n2
42) r
43GO
44
45CREATE STATISTICS stats_randomDateTime ON dbo.randomDateTimesIn2018 (randomDateTime) WITH FULLSCAN
46GO
47
48--------------------------------------------------------------------------------------------------------------------------------------
49-- Cardinality estimates with DATETIME and CAST AS TIME predicates are so skewed that estimates for the entire first half
50-- of the day yield 0 rows (token 1 row cardinality estimate). Any queries that use this type of predicate and then join
51-- to other tables are in for a lot of pain. We've seen billion row fact tables get put on the outer side of
52-- a nested loop due to this cardinality bug, and even if appropriate join types are chosen, memory grants may be far too low.
53
54-- NOTE: All "Estimated rows" numbers below are for the cardinality estimate just prior (to the right of) the stream aggregate
55--------------------------------------------------------------------------------------------------------------------------------------
56
57/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '0:00:00' AND '1:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
58/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '1:00:00' AND '2:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
59/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '2:00:00' AND '3:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
60/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '3:00:00' AND '4:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
61/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '4:00:00' AND '5:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
62/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '5:00:00' AND '6:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
63/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '6:00:00' AND '7:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
64/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '7:00:00' AND '8:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
65/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '8:00:00' AND '9:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
66/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '9:00:00' AND '10:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
67/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '10:00:00' AND '11:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
68/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '11:00:00' AND '12:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
69/* Estimated rows: 196489 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '12:00:00' AND '13:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
70/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '13:00:00' AND '14:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
71/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '14:00:00' AND '15:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
72/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '15:00:00' AND '16:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
73/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '16:00:00' AND '17:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
74/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '17:00:00' AND '18:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
75/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '18:00:00' AND '19:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
76/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '19:00:00' AND '20:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
77/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '20:00:00' AND '21:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
78/* Estimated rows: 1580820 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '21:00:00' AND '22:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
79/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '22:00:00' AND '23:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
80/* Estimated rows: 1 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '23:00:00' AND '24:00:00' AND randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01'
81GO
82/*
83-- This query can be used to automatically generate the queries above for each hour of the day
84DECLARE @templateSql VARCHAR(MAX) = 'SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN ''{h1}:00:00'' AND ''{h2}:00:00'' AND randomDateTime >= ''2018-03-01'' AND randomDateTime < ''2018-04-01''
85'
86SELECT REPLACE(REPLACE(@templateSql,'{h1}',n-1),'{h2}',n)
87FROM #numbers
88WHERE n BETWEEN 1 AND 24
89*/
90
91--------------------------------------------------------------------------------------------------------------------------------------
92-- Cardinality estimates with time predicate only are also heavily skewed, but not quite as badly
93--------------------------------------------------------------------------------------------------------------------------------------
94
95/* Estimated rows: 9737 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '0:00:00' AND '1:00:00'
96/* Estimated rows: 9735 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '1:00:00' AND '2:00:00'
97/* Estimated rows: 9735 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '2:00:00' AND '3:00:00'
98/* Estimated rows: 9735 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '3:00:00' AND '4:00:00'
99/* Estimated rows: 9735 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '4:00:00' AND '5:00:00'
100/* Estimated rows: 9735 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '5:00:00' AND '6:00:00'
101/* Estimated rows: 2797380 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '6:00:00' AND '7:00:00'
102/* Estimated rows: 45103 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '7:00:00' AND '8:00:00'
103/* Estimated rows: 45103 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '8:00:00' AND '9:00:00'
104/* Estimated rows: 84812 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '9:00:00' AND '10:00:00'
105/* Estimated rows: 142264 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '10:00:00' AND '11:00:00'
106/* Estimated rows: 142264 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '11:00:00' AND '12:00:00'
107/* Estimated rows: 922400 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '12:00:00' AND '13:00:00'
108/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '13:00:00' AND '14:00:00'
109/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '14:00:00' AND '15:00:00'
110/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '15:00:00' AND '16:00:00'
111/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '16:00:00' AND '17:00:00'
112/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '17:00:00' AND '18:00:00'
113/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '18:00:00' AND '19:00:00'
114/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '19:00:00' AND '20:00:00'
115/* Estimated rows: 43281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '20:00:00' AND '21:00:00'
116/* Estimated rows: 20005900 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '21:00:00' AND '22:00:00'
117/* Estimated rows: 205167 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '22:00:00' AND '23:00:00'
118/* Estimated rows: 112281 */ SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '23:00:00' AND '24:00:00'
119GO
120/*
121-- This query can be used to automatically generate the queries above for each hour of the day
122DECLARE @templateSql VARCHAR(MAX) = 'SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= ''2018-03-01'' AND randomDateTime < ''2018-04-01'' AND CAST(randomDateTime AS TIME) BETWEEN ''{h1}:00:00'' AND ''{h2}:00:00''
123'
124SELECT REPLACE(REPLACE(@templateSql,'{h1}',n-1),'{h2}',n)
125FROM #numbers
126WHERE n BETWEEN 1 AND 24
127*/
128
129--------------------------------------------------------------------------------------------------------------------------------------
130-- Wrapping the time cast with ISNULL() appears to fix the TIME cardinality bug, and the estimates are not almost exactly uniform
131-- This is surprising; the column was already non-nullable so wrapping ISNULL() around the CAST() cannot change the value (only the nullability)
132--------------------------------------------------------------------------------------------------------------------------------------
133
134/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '0:00:00' AND '1:00:00'
135/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '1:00:00' AND '2:00:00'
136/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '2:00:00' AND '3:00:00'
137/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '3:00:00' AND '4:00:00'
138/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '4:00:00' AND '5:00:00'
139/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '5:00:00' AND '6:00:00'
140/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '6:00:00' AND '7:00:00'
141/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '7:00:00' AND '8:00:00'
142/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '8:00:00' AND '9:00:00'
143/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '9:00:00' AND '10:00:00'
144/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '10:00:00' AND '11:00:00'
145/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '11:00:00' AND '12:00:00'
146/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '12:00:00' AND '13:00:00'
147/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '13:00:00' AND '14:00:00'
148/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '14:00:00' AND '15:00:00'
149/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '15:00:00' AND '16:00:00'
150/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '16:00:00' AND '17:00:00'
151/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '17:00:00' AND '18:00:00'
152/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '18:00:00' AND '19:00:00'
153/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '19:00:00' AND '20:00:00'
154/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '20:00:00' AND '21:00:00'
155/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '21:00:00' AND '22:00:00'
156/* Estimated rows: 637062 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '22:00:00' AND '23:00:00'
157/* Estimated rows: 860799 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND ISNULL(CAST(randomDateTime AS TIME),'00:00:00') BETWEEN '23:00:00' AND '24:00:00'
158GO
159
160--------------------------------------------------------------------------------------------------------------------------------------
161-- Adding a computed column is another way of working around the TIME cardinality bug
162-- This is not surprising; the computed column is eligible for auto-generated statistics
163--------------------------------------------------------------------------------------------------------------------------------------
164
165ALTER TABLE dbo.randomDateTimesIn2018 ADD randomTime AS CAST(randomDateTime AS TIME)
166GO
167
168/* Estimated rows: 305389 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '0:00:00' AND '1:00:00'
169/* Estimated rows: 305360 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '1:00:00' AND '2:00:00'
170/* Estimated rows: 305360 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '2:00:00' AND '3:00:00'
171/* Estimated rows: 305863 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '3:00:00' AND '4:00:00'
172/* Estimated rows: 304770 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '4:00:00' AND '5:00:00'
173/* Estimated rows: 301444 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '5:00:00' AND '6:00:00'
174/* Estimated rows: 302389 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '6:00:00' AND '7:00:00'
175/* Estimated rows: 303955 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '7:00:00' AND '8:00:00'
176/* Estimated rows: 300297 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '8:00:00' AND '9:00:00'
177/* Estimated rows: 303794 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '9:00:00' AND '10:00:00'
178/* Estimated rows: 301873 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '10:00:00' AND '11:00:00'
179/* Estimated rows: 302089 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '11:00:00' AND '12:00:00'
180/* Estimated rows: 304769 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '12:00:00' AND '13:00:00'
181/* Estimated rows: 302472 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '13:00:00' AND '14:00:00'
182/* Estimated rows: 304480 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '14:00:00' AND '15:00:00'
183/* Estimated rows: 304086 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '15:00:00' AND '16:00:00'
184/* Estimated rows: 303649 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '16:00:00' AND '17:00:00'
185/* Estimated rows: 303649 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '17:00:00' AND '18:00:00'
186/* Estimated rows: 304442 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '18:00:00' AND '19:00:00'
187/* Estimated rows: 301245 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '19:00:00' AND '20:00:00'
188/* Estimated rows: 305346 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '20:00:00' AND '21:00:00'
189/* Estimated rows: 303304 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '21:00:00' AND '22:00:00'
190/* Estimated rows: 304239 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '22:00:00' AND '23:00:00'
191/* Estimated rows: 225145 */SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE randomDateTime >= '2018-03-01' AND randomDateTime < '2018-04-01' AND CAST(randomDateTime AS TIME) BETWEEN '23:00:00' AND '24:00:00'
192GO
193
194--------------------------------------------------------------------------------------------------------------------------------------
195-- Cleanup
196--------------------------------------------------------------------------------------------------------------------------------------
197
198DROP TABLE IF EXISTS dbo.randomDateTimesIn2018
199GO