Suppose you are using SQL Server 2005, and you want to
truncate your DATETIME value, leaving only date part of it, as follows:
DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101')
Suppose you want to reuse this code and wrap it in a scalar
UDF:
CREATE FUNCTION
dbo.TruncatedDateTime(@d
DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101')
END
GO
It is well known that scalar UDFs may be slow, but how slow
is this particular one, as compared to the expression which it wraps? That’s
quite easy to figure out. I populated a table with one million dates from one
of my production tables, as follows:
SELECT TOP
1000000 LastModifiedDate INTO dates
FROM data.MyProductionTable
And I ran these two queries:
SELECT COUNT(*) FROM(
SELECT DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101') AS d FROM dates
) t WHERE
d>'20220203'
(snip)
SQL Server
Execution Times:
CPU time = 343 ms, elapsed time = 347 ms.
SELECT COUNT(*) FROM(
SELECT dbo.TruncatedDateTime(LastModifiedDate) AS d FROM Dates
) t WHERE
d>'20220203'
(snip)
SQL Server
Execution Times:
CPU time = 26109 ms, elapsed time = 26651 ms.
Note that the UDF slowed the query down almost one thousand
times, as expected. However, let us consider a better alternative – a table-valued UDF, as follows:
CREATE FUNCTION
dbo.TableValuedTruncatedDateTime(@d DATETIME)
RETURNS TABLE
AS
RETURN( SELECT
DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101') AS
TruncatedDateTime);
GO
SELECT COUNT(*) FROM(
SELECT TruncatedDateTime AS d FROM Dates CROSS APPLY dbo.TableValuedTruncatedDateTime(Dates.LastModifiedDate)
) t WHERE
d>'20220203'
(snip)
SQL Server
Execution Times:
CPU time = 344 ms, elapsed time = 347 ms.
Note that the query using the table-valued UDF runs just as
fast as the original one. As you have seen, CROSS APPLY allowed you to reuse
your date-truncating code without taking a performance hit.