--
-- BASED ON CODE BY TIBOR KARASZI at: http://www.karaszi.com/sqlserver/info_datetime_rid_sec_perf_v2.asp
-- WITH ADDITIONS BY Nigel Rheam http://www.kebabShopBlues.co.uk
--
-- Test to assess the perf difference between string method vs DATEADD method vs CAST/FLOOR 
-- to set time portion to zero, now also using a non-clustered index. 
-- 
-- It will be advantageous to pre-size the tempDB datafile to about 1GB before running the script that inserts values into the table
--

USE tempdb 
GO 

IF OBJECT_ID('dbo.date_table_2') IS NOT NULL DROP TABLE dbo.date_table_2 
GO 

CREATE TABLE dbo.date_table_2
	(
	some_value VARCHAR(10) NOT NULL,
    some_int INT NOT NULL,
	date_time DATETIME NOT NULL
	);


-- 10,000,000 rows will blow up tempdb data file to about 670MB (343MB data + 331MB indexes)
-- It will take 10minutes or so to build this table and index! It will take a lot less time if you pre-size TempDB to have a 1GB file size
DECLARE @dt DATETIME;
SET @dt = getdate() - 50;		-- 50 days ago! (10,000,000 seconds covers about 115 days, so we are creating records from 50days ago to now +65 days or thereabouts)
DECLARE @i INT;
SET @i = 0;

WHILE @i < 10000000
BEGIN
  -- INSERT values into the table. Every tenth row is forced to be out of time sequence...
  IF (@i % 10 != 0)
    INSERT INTO dbo.date_table_2(some_value, some_int, date_time)
    VALUES ('SV' + CAST(@i AS VARCHAR(8)),
	  	    @i,
            @dt);
  ELSE
    -- Insert every tenth value out-of-date-sequence. This is to remind us that the values in the table are NOT sequential :)
    INSERT INTO dbo.date_table_2(some_value, some_int, date_time)
    VALUES ('SV' + CAST(@i AS VARCHAR(8)),
	  	    @i,
            @dt - 1);	-- the day value less a day
  SET @i = @i + 1;
  SET @dt = DATEADD(second, 1, @dt);						-- Add a second to the time we will store next
END;

CREATE NONCLUSTERED INDEX [IX_date_table_2_date_time] ON [dbo].[date_table_2] 
(
	[date_time] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

-- Just to demonstrate the min, and max values etc
SELECT min(date_time), max(date_time), count(*)
from dbo.date_table_2;

--
-- First Tests. Remember this is with the 'worst' performing date conversion
-- As we are just counting entries on a particular date, the index contains enough information
--
DECLARE @t datetime 

-- Pure string manipulation. Bad comparison
SET @t = GETDATE() 

SELECT COUNT(*) 
FROM dbo.date_table_2
WHERE CONVERT(CHAR(8), date_time, 112) = CONVERT(CHAR(8), GETDATE() - 1, 112) -- GETDATE() - 1 is Yesterday
OPTION (MAXDOP 1); 

SELECT DATEDIFF(ms, @t, GETDATE()) AS "String Baseline - Bad comparison - Index DOES cover this result set"


-- Pure string manipulation. Better comparison
SET @t = GETDATE() 

SELECT COUNT(*) 
FROM dbo.date_table_2
WHERE date_time >= CONVERT(CHAR(8), GETDATE() - 1, 112) -- GETDATE() - 1 is Yesterday
AND   date_time <  CONVERT(CHAR(8), GETDATE(), 112)     
OPTION (MAXDOP 1); 

SELECT DATEDIFF(ms, @t, GETDATE()) AS "String Baseline - Better comparison - Index covers result set"

--
-- Second Test.
-- Return results from the main table, the index contains does not contain enough information by itself
-- Just for testing we are using one of the DATEADD time-stripping mechanisms
--

-- Pure string manipulation. Bad comparison
SET @t = GETDATE() 

SELECT some_value
FROM dbo.date_table_2
WHERE DATEADD(DAY, 0, DATEDIFF(DAY, 0, date_time)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE() - 1)) -- GETDATE() - 1 is Yesterday
OPTION (MAXDOP 1); 

SELECT DATEDIFF(ms, @t, GETDATE()) AS "TEST 2: Bad comparison (column value converted) - Index DOES NOT cover this result set"


-- Pure string manipulation. Better comparison
SET @t = GETDATE() 

SELECT some_value
FROM dbo.date_table_2
WHERE date_time >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE() - 1)) -- GETDATE() - 1 is Yesterday
AND   date_time <  DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))     
OPTION (MAXDOP 1); 

SELECT DATEDIFF(ms, @t, GETDATE()) AS "TEST 2: Better comparison - Index DOES NOT cover result set"
