Compare date function performance.sql

---------------------------------------------------------
--- DEMO TABLE WITH 4 MILLION ROWS:


DROP TABLE #dates;

CREATE TABLE #dates (
    [date]              date NOT NULL,
    [datetime]          datetime NOT NULL,
    [datetimeoffset_0]  datetimeoffset(0) NOT NULL,
    [datetimeoffset_3]  datetimeoffset(3) NOT NULL,
    [datetimeoffset_7]  datetimeoffset(7) NOT NULL,
    [datetime2_0]       datetime2(0) NOT NULL,
    [datetime2_3]       datetime2(3) NOT NULL,
    [datetime2_7]       datetime2(7) NOT NULL,
    [smalldatetime]     smalldatetime NOT NULL,
    [time]              time NOT NULL
);

INSERT INTO #dates
VALUES (SYSDATETIME(), SYSDATETIME(),
        SYSUTCDATETIME(), SYSUTCDATETIME(), SYSUTCDATETIME(), 
        SYSDATETIME(), SYSDATETIME(), SYSDATETIME(),
        SYSDATETIME(), SYSDATETIME());

WHILE (@@ROWCOUNT<2000000)
    INSERT INTO #dates
    SELECT * FROM #dates;





---------------------------------------------------------
--- COMPILE RESULTS:




DROP TABLE IF EXISTS #results;

CREATE TABLE #results (
    [function]      varchar(50) NOT NULL,
    type_1          varchar(20) NOT NULL,
    type_2          varchar(20) NULL,
    query_plan      xml NOT NULL,
    duration_ms     bigint NOT NULL,
    is_convert_explicit bit NULL
);

CREATE UNIQUE INDEX UQ ON #results ([function], type_1, type_2);





DECLARE @template varchar(100), @type_1 varchar(20), @type_2 varchar(20), @sql nvarchar(max), @start datetime2(7);

DECLARE cur CURSOR FOR
    SELECT fn.template, a.[name], b.[name], '
            DECLARE @count int;
        
            SELECT @count=COUNT('+
                REPLACE(REPLACE(fn.template, '%a', QUOTENAME(a.[name])),
                                             '%b', ISNULL(QUOTENAME(b.[name]), ''))+')
            FROM #dates
            OPTION (MAXDOP 1);' AS [sql]
    FROM (
        VALUES ('DATENAME(month, %a)'),
           
               ('DATEPART(month, %a)'),
               ('YEAR(%a)'), ('MONTH(%a)'), ('DAY(%a)'),
           
               ('DATEDIFF(month, %a, %b)'),
               ('DATEDIFF_BIG(month, %a, %b)'),

               ('EOMONTH(%a)') /*,
               ('DATETRUNC(%a)') */
        ) AS fn(template)
    CROSS JOIN tempdb.sys.columns AS a
    LEFT JOIN tempdb.sys.columns AS b ON a.[object_id]=b.[object_id] AND fn.template LIKE '%[%]b%'
    WHERE a.[object_id]=OBJECT_ID('tempdb.dbo.#dates');
OPEN cur;

FETCH NEXT FROM cur INTO @template, @type_1, @type_2, @sql;
WHILE (@@FETCH_STATUS=0) BEGIN;

    SET @start=SYSDATETIME();

    EXECUTE sys.sp_executesql @sql;

    INSERT INTO #results ([function], type_1, type_2, query_plan, duration_ms)
    SELECT @template, @type_1, @type_2, qp.query_plan, DATEDIFF_BIG(ms, @start, SYSDATETIME())
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    WHERE st.[text]=@sql

    FETCH NEXT FROM cur INTO @template, @type_1, @type_2, @sql;
END;

CLOSE cur;
DEALLOCATE cur;




---------------------------------------------------------
--- OUTPUT:





SELECT LEFT([function], CHARINDEX('(', [function])-1), type_1,
       MIN((CASE WHEN type_2='date' THEN NULLIF(duration_ms, 0) END))             AS [date],
       MIN((CASE WHEN type_2='datetime' THEN NULLIF(duration_ms, 0) END))         AS [datetime],
       MIN((CASE WHEN type_2='datetime2_0' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(0)],
       MIN((CASE WHEN type_2='datetime2_3' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(3)],
       MIN((CASE WHEN type_2='datetime2_7' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(7)],
       MIN((CASE WHEN type_2='datetimeoffset_0' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(0)],
       MIN((CASE WHEN type_2='datetimeoffset_3' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(3)],
       MIN((CASE WHEN type_2='datetimeoffset_7' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(7)],
       MIN((CASE WHEN type_2='smalldatetime' THEN NULLIF(duration_ms, 0) END))    AS [smalldatetime],
       MIN((CASE WHEN type_2='time' THEN NULLIF(duration_ms, 0) END))             AS [time]
FROM #results
WHERE [function] LIKE '%[%]b%'
GROUP BY [function], type_1
ORDER BY [function], type_1;



SELECT LEFT([function], CHARINDEX('(', [function])-1),
       MIN((CASE WHEN type_1='date' THEN NULLIF(duration_ms, 0) END))             AS [date],
       MIN((CASE WHEN type_1='datetime' THEN NULLIF(duration_ms, 0) END))         AS [datetime],
       MIN((CASE WHEN type_1='datetime2_0' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(0)],
       MIN((CASE WHEN type_1='datetime2_3' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(3)],
       MIN((CASE WHEN type_1='datetime2_7' THEN NULLIF(duration_ms, 0) END))      AS [datetime2(7)],
       MIN((CASE WHEN type_1='datetimeoffset_0' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(0)],
       MIN((CASE WHEN type_1='datetimeoffset_3' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(3)],
       MIN((CASE WHEN type_1='datetimeoffset_7' THEN NULLIF(duration_ms, 0) END)) AS [datetimeoffset(7)],
       MIN((CASE WHEN type_1='smalldatetime' THEN NULLIF(duration_ms, 0) END))    AS [smalldatetime],
       MIN((CASE WHEN type_1='time' THEN NULLIF(duration_ms, 0) END))             AS [time]
FROM #results
WHERE [function] NOT LIKE '%[%]b%'
GROUP BY [function]
ORDER BY [function];






WITH res AS (
    SELECT res.[function], res.type_1, res.type_2, res.duration_ms, ISNULL(ic.[name], '') AS implicit_conversion
    FROM #results AS res
    CROSS APPLY (VALUES (CAST(query_plan AS nvarchar(max)))) AS p([plan])
    CROSS APPLY (VALUES (SUBSTRING(p.[plan], NULLIF(CHARINDEX('CONVERT_IMPLICIT(date', p.[plan]), 0)+17, 100))) AS x1(s)
    CROSS APPLY (VALUES (LEFT(x1.s, NULLIF(CHARINDEX(',', x1.s), 0)-1))) AS ic([name]))

SELECT LEFT([function], CHARINDEX('(', [function])-1), type_1,
       MIN((CASE WHEN type_2='date' THEN implicit_conversion END)) AS [date],
       MIN((CASE WHEN type_2='datetime' THEN implicit_conversion END)) AS [datetime],
       MIN((CASE WHEN type_2='datetime2_0' THEN implicit_conversion END)) AS [datetime2(0)],
       MIN((CASE WHEN type_2='datetime2_3' THEN implicit_conversion END)) AS [datetime2(3)],
       MIN((CASE WHEN type_2='datetime2_7' THEN implicit_conversion END)) AS [datetime2(7)],
       MIN((CASE WHEN type_2='datetimeoffset_0' THEN implicit_conversion END)) AS [datetimeoffset(0)],
       MIN((CASE WHEN type_2='datetimeoffset_3' THEN implicit_conversion END)) AS [datetimeoffset(3)],
       MIN((CASE WHEN type_2='datetimeoffset_7' THEN implicit_conversion END)) AS [datetimeoffset(7)],
       MIN((CASE WHEN type_2='smalldatetime' THEN implicit_conversion END)) AS [smalldatetime],
       MIN((CASE WHEN type_2='time' THEN implicit_conversion END)) AS [time]
FROM res
WHERE [function] LIKE '%[%]b%'
GROUP BY [function], type_1
ORDER BY [function], type_1;



WITH res AS (
    SELECT res.[function], res.type_1, res.type_2, res.duration_ms, ISNULL(ic.[name], '') AS implicit_conversion
    FROM #results AS res
    CROSS APPLY (VALUES (CAST(query_plan AS nvarchar(max)))) AS p([plan])
    CROSS APPLY (VALUES (SUBSTRING(p.[plan], NULLIF(CHARINDEX('CONVERT_IMPLICIT(date', p.[plan]), 0)+17, 100))) AS x1(s)
    CROSS APPLY (VALUES (LEFT(x1.s, NULLIF(CHARINDEX(',', x1.s), 0)-1))) AS ic([name]))

SELECT LEFT([function], CHARINDEX('(', [function])-1),
       MIN((CASE WHEN type_1='date' THEN implicit_conversion END)) AS [date],
       MIN((CASE WHEN type_1='datetime' THEN implicit_conversion END)) AS [datetime],
       MIN((CASE WHEN type_1='datetime2_0' THEN implicit_conversion END)) AS [datetime2(0)],
       MIN((CASE WHEN type_1='datetime2_3' THEN implicit_conversion END)) AS [datetime2(3)],
       MIN((CASE WHEN type_1='datetime2_7' THEN implicit_conversion END)) AS [datetime2(7)],
       MIN((CASE WHEN type_1='datetimeoffset_0' THEN implicit_conversion END)) AS [datetimeoffset(0)],
       MIN((CASE WHEN type_1='datetimeoffset_3' THEN implicit_conversion END)) AS [datetimeoffset(3)],
       MIN((CASE WHEN type_1='datetimeoffset_7' THEN implicit_conversion END)) AS [datetimeoffset(7)],
       MIN((CASE WHEN type_1='smalldatetime' THEN implicit_conversion END)) AS [smalldatetime],
       MIN((CASE WHEN type_1='time' THEN implicit_conversion END)) AS [time]
FROM res
WHERE [function] NOT LIKE '%[%]b%'
GROUP BY [function]
ORDER BY [function];