# script: scripts/09_sargability.sql | database: lab-cst | started (UTC): 2026-10-16 01:49:57
/* 09 - SARGability (CST database)
   Table tzlab.Events stores UTC (the recommended "UTC at rest" design), ~200k rows over 30 days,
   nonclustered index on EventTimeUtc. Question: "how many events since local midnight today?"
     A) AT TIME ZONE applied to the COLUMN            -> correct, but is it a scan?
     B) convert the local boundary to UTC ONCE        -> correct and seekable
     C) compare the UTC column to GETDATE()-based local boundary (the gdoc's "just use GETDATE()" idea)
        -> seekable but WRONG when the column holds UTC, because GETDATE() is now Central time
     D) same as C but on a column stored in LOCAL time -> seekable and correct (the case the gdoc means) */
SET NOCOUNT ON;
PRINT '=== 09 SARGability on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')';
=== 09 SARGability on lab-cst (effective tz: Central Standard Time)
DROP TABLE IF EXISTS tzlab.Events;
CREATE TABLE tzlab.Events
(
    EventID       int IDENTITY(1, 1) CONSTRAINT PK_tzlab_Events PRIMARY KEY,
    EventTimeUtc  datetime2(3) NOT NULL,
    EventTimeLocal datetime2(3) NOT NULL,   -- same instant, stored as naive Central time
    Payload       char(200)    NOT NULL DEFAULT ('x')
);
-- ~200k events evenly spread over the last 30 days (UTC), with the matching local value.
WITH n AS (
    SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
)
INSERT tzlab.Events (EventTimeUtc, EventTimeLocal)
SELECT u, CAST(u AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' AS datetime2(3))
FROM (SELECT DATEADD(SECOND, -CAST(i * 12.96 AS int), SYSUTCDATETIME()) AS u FROM n) x;
CREATE INDEX IX_Events_EventTimeUtc   ON tzlab.Events (EventTimeUtc);
CREATE INDEX IX_Events_EventTimeLocal ON tzlab.Events (EventTimeLocal);
SELECT COUNT(*) AS rows_loaded, MIN(EventTimeUtc) AS min_utc, MAX(EventTimeUtc) AS max_utc FROM tzlab.Events;
rows_loaded|min_utc|max_utc
-----------|-------|-------
200000|2026-09-16 01:49:58.132|2026-10-16 01:49:46.132

PRINT '--- 09.0 Boundaries';
SELECT CAST(CAST(GETDATE() AS date) AS datetime2(3)) AS local_midnight_naive,
       CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3)) AS local_midnight_in_utc;
--- 09.0 Boundaries
local_midnight_naive|local_midnight_in_utc
--------------------|---------------------
2026-10-15 00:00:00.000|2026-10-15 05:00:00.000

PRINT '--- 09.1 Estimated plans (SHOWPLAN_TEXT, not executed)';
--- 09.1 Estimated plans (SHOWPLAN_TEXT, not executed)
SET SHOWPLAN_TEXT ON;
-- A
SELECT COUNT(*) FROM tzlab.Events
WHERE EventTimeUtc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' >= CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE 'Central Standard Time';
StmtText
--------
-- A
SELECT COUNT(*) FROM tzlab.Events
WHERE EventTimeUtc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' >= CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE 'Central Standard Time';

StmtText
--------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1005],0)))
       |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
            |--Index Scan(OBJECT:([lab-cst].[tzlab].[Events].[IX_Events_EventTimeUtc]),  WHERE:((([lab-cst].[tzlab].[Events].[EventTimeUtc] AT TIME ZONE N'UTC') AT TIME ZONE N'Central Standard Time')>=(CONVERT(datetime2(3),CONVERT(date,getdate(),0),0) AT TIME ZONE N'Central Standard Time')))

-- B
DECLARE @fromUtc datetime2(3) = CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= @fromUtc;
StmtText
--------
-- B
DECLARE @fromUtc datetime2(3) = CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= @fromUtc;

StmtText
--------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1003],0)))
       |--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
            |--Index Seek(OBJECT:([lab-cst].[tzlab].[Events].[IX_Events_EventTimeUtc]), SEEK:([lab-cst].[tzlab].[Events].[EventTimeUtc] >= [@fromUtc]) ORDERED FORWARD)

-- C
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
StmtText
--------
-- C
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeUtc >= CAST(CAST(GETDATE() AS date) AS datetime2(3));

StmtText
--------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1004],0)))
       |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
            |--Index Seek(OBJECT:([lab-cst].[tzlab].[Events].[IX_Events_EventTimeUtc]), SEEK:([lab-cst].[tzlab].[Events].[EventTimeUtc] >= CONVERT(datetime2(3),CONVERT(date,getdate(),0),0)) ORDERED FORWARD)

-- D
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeLocal >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
StmtText
--------
-- D
SELECT COUNT(*) FROM tzlab.Events WHERE EventTimeLocal >= CAST(CAST(GETDATE() AS date) AS datetime2(3));

StmtText
--------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1004],0)))
       |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
            |--Index Seek(OBJECT:([lab-cst].[tzlab].[Events].[IX_Events_EventTimeLocal]), SEEK:([lab-cst].[tzlab].[Events].[EventTimeLocal] >= CONVERT(datetime2(3),CONVERT(date,getdate(),0),0)) ORDERED FORWARD)

SET SHOWPLAN_TEXT OFF;
PRINT '--- 09.2 Executed with STATISTICS IO (logical reads appear in the messages)';
SET STATISTICS IO ON;
--- 09.2 Executed with STATISTICS IO (logical reads appear in the messages)
PRINT 'A) AT TIME ZONE on the column';
SELECT 'A' AS q, COUNT(*) AS cnt FROM tzlab.Events
WHERE EventTimeUtc AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' >= CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE 'Central Standard Time';
A) AT TIME ZONE on the column
q|cnt
-|---
A|5786

Table '[tzlab].[Events]'. Scan count 1, logical reads 425, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
PRINT 'B) boundary converted to UTC once';
DECLARE @fromUtc datetime2(3) = CAST(CAST(CAST(GETDATE() AS date) AS datetime2(3)) AT TIME ZONE CURRENT_TIMEZONE_ID() AT TIME ZONE 'UTC' AS datetime2(3));
SELECT 'B' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeUtc >= @fromUtc;
B) boundary converted to UTC once
q|cnt
-|---
B|5786

Table '[tzlab].[Events]'. Scan count 1, logical reads 16, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
PRINT 'C) UTC column vs local GETDATE() boundary (seekable but semantically wrong)';
SELECT 'C' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeUtc >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
C) UTC column vs local GETDATE() boundary (seekable but semantically wrong)
q|cnt
-|---
C|7175

Table '[tzlab].[Events]'. Scan count 1, logical reads 19, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
PRINT 'D) LOCAL column vs local GETDATE() boundary';
SELECT 'D' AS q, COUNT(*) AS cnt FROM tzlab.Events WHERE EventTimeLocal >= CAST(CAST(GETDATE() AS date) AS datetime2(3));
D) LOCAL column vs local GETDATE() boundary
q|cnt
-|---
D|5786

Table '[tzlab].[Events]'. Scan count 1, logical reads 16, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SET STATISTICS IO OFF;
# sqlcmd exit code: 0