I have a scalar-valued function called DATEONLY that returns DATEADD(DD,0, DATEDIFF(DD,0, @DATETIME)), just like this:
CREATE FUNCTION [DBO].[DATEONLY] ( @DATETIME DATETIME )
RETURNS DATETIME
BEGIN
RETURN DATEADD(DD, 0, DATEDIFF(DD, 0, @DATETIME))
END
When I select a table using my function, the SQL Server Profiler counts a high RowCounts number than if I used directly DATEADD(DD,0, DATEDIFF(DD,0, @DATETIME)).
In my Dropxbox's Public Folder you can find a script.sql that can reproduce what I am talking about and you can also find a Trace.trc from my SQL Server Profiler.
script.sql: https://www.dropbox.com/s/gwbh54jqas7fhhc/script.sql
trace.trc: https://www.dropbox.com/s/gwbh54jqas7fhhc/Trace.trc
Just to simplify, look the RowCounts below.
SELECT DATEADD(DD,0, DATEDIFF(DD,0, INCOMING)) AS DATA, COUNT(*) AS SOULS
FROM HELL
GROUP BY DATEADD(DD,0, DATEDIFF(DD,0, INCOMING))
RowCounts = 6
SELECT DBO.DATEONLY(INCOMING) AS DATA, COUNT(*) AS SOULS
FROM HELL
GROUP BY DBO.DATEONLY(INCOMING)
RowCounts = 32
In my real scenario, those 32 rows turns into millions RowCounts. If they are THE SAME THING, what's going on?! How can i optimize that to prevent changing my whole application?
Thanks a lot!