TimeCastCardinalityBug.sql

--------------------------------------------------------------------------------------------------------------------------------------
-- Data setup
--------------------------------------------------------------------------------------------------------------------------------------

SELECT @@VERSION
--Microsoft SQL Server 2017 (RTM-CU12) (KB4464082) - 14.0.3045.24 (X64) 
--	Oct 18 2018 23:11:05 
--	Copyright (C) 2017 Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
GO

-- This repro works on any database, but we'll use tempdb since all servers have that database
USE tempdb
GO
-- For this script, we use the most recent compatibility level
-- However, the bug also reproduces with any of the following query hints:
	-- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110'))
	-- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120'))
	-- OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130'))
ALTER DATABASE tempdb SET COMPATIBILITY_LEVEL = 140
GO

DROP TABLE IF EXISTS #numbers
SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n
INTO #numbers
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2
GO

CREATE TABLE dbo.randomDateTimesIn2018 (
	randomDateTime DATETIME NOT NULL
)
GO

INSERT INTO dbo.randomDateTimesIn2018 WITH (TABLOCK) (randomDateTime)
SELECT DATEADD(SECOND, secondOffset, DATEADD(DAY, dayOffset, '2018-01-01')) AS randomDateTime
FROM (
	SELECT ABS(CRYPT_GEN_RANDOM(16) % 365) AS dayOffset,
		ABS(CRYPT_GEN_RANDOM(16) % 86400) AS secondOffset 
	FROM #numbers n1
	CROSS JOIN #numbers n2
) r
GO

CREATE STATISTICS stats_randomDateTime ON dbo.randomDateTimesIn2018 (randomDateTime) WITH FULLSCAN
GO

--------------------------------------------------------------------------------------------------------------------------------------
-- Cardinality estimates with DATETIME and CAST AS TIME predicates are so skewed that estimates for the entire first half
-- of the day yield 0 rows (token 1 row cardinality estimate). Any queries that use this type of predicate and then join
-- to other tables are in for a lot of pain. We've seen billion row fact tables get put on the outer side of
-- a nested loop due to this cardinality bug, and even if appropriate join types are chosen, memory grants may be far too low.

-- NOTE: All "Estimated rows" numbers below are for the cardinality estimate just prior (to the right of) the stream aggregate
--------------------------------------------------------------------------------------------------------------------------------------

/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
/* 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' 
GO
/*
-- This query can be used to automatically generate the queries above for each hour of the day
DECLARE @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'' 
'
SELECT REPLACE(REPLACE(@templateSql,'{h1}',n-1),'{h2}',n)
FROM #numbers
WHERE n BETWEEN 1 AND 24
*/

--------------------------------------------------------------------------------------------------------------------------------------
-- Cardinality estimates with time predicate only are also heavily skewed, but not quite as badly
--------------------------------------------------------------------------------------------------------------------------------------

/* Estimated rows: 9737 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '0:00:00' AND '1:00:00'
/* Estimated rows: 9735 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '1:00:00' AND '2:00:00'
/* Estimated rows: 9735 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '2:00:00' AND '3:00:00'
/* Estimated rows: 9735 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '3:00:00' AND '4:00:00'
/* Estimated rows: 9735 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '4:00:00' AND '5:00:00'
/* Estimated rows: 9735 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '5:00:00' AND '6:00:00'
/* Estimated rows: 2797380 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '6:00:00' AND '7:00:00'
/* Estimated rows: 45103 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '7:00:00' AND '8:00:00'
/* Estimated rows: 45103 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '8:00:00' AND '9:00:00'
/* Estimated rows: 84812 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '9:00:00' AND '10:00:00'
/* Estimated rows: 142264 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '10:00:00' AND '11:00:00'
/* Estimated rows: 142264 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '11:00:00' AND '12:00:00'
/* Estimated rows: 922400 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '12:00:00' AND '13:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '13:00:00' AND '14:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '14:00:00' AND '15:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '15:00:00' AND '16:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '16:00:00' AND '17:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '17:00:00' AND '18:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '18:00:00' AND '19:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '19:00:00' AND '20:00:00'
/* Estimated rows: 43281 */		SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '20:00:00' AND '21:00:00'
/* Estimated rows: 20005900 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '21:00:00' AND '22:00:00'
/* Estimated rows: 205167 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '22:00:00' AND '23:00:00'
/* Estimated rows: 112281 */	SELECT COUNT(*) FROM dbo.randomDateTimesIn2018 WHERE CAST(randomDateTime AS TIME) BETWEEN '23:00:00' AND '24:00:00'
GO
/*
-- This query can be used to automatically generate the queries above for each hour of the day
DECLARE @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''
'
SELECT REPLACE(REPLACE(@templateSql,'{h1}',n-1),'{h2}',n)
FROM #numbers
WHERE n BETWEEN 1 AND 24
*/

--------------------------------------------------------------------------------------------------------------------------------------
-- Wrapping the time cast with ISNULL() appears to fix the TIME cardinality bug, and the estimates are not almost exactly uniform
-- This is surprising; the column was already non-nullable so wrapping ISNULL() around the CAST() cannot change the value (only the nullability)
--------------------------------------------------------------------------------------------------------------------------------------

/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
GO

--------------------------------------------------------------------------------------------------------------------------------------
-- Adding a computed column is another way of working around the TIME cardinality bug
-- This is not surprising; the computed column is eligible for auto-generated statistics
--------------------------------------------------------------------------------------------------------------------------------------

ALTER TABLE dbo.randomDateTimesIn2018 ADD randomTime AS CAST(randomDateTime AS TIME)
GO

/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
/* 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'
GO

--------------------------------------------------------------------------------------------------------------------------------------
-- Cleanup
--------------------------------------------------------------------------------------------------------------------------------------

DROP TABLE IF EXISTS dbo.randomDateTimesIn2018
GO