7.0 doesnt have functions at all
Here is fragment of function I used before:
SELECT @.MessageCount = COUNT(RecordID)
FROM Msgs
WHERE dbo.GetGroup(Type) = dbo.GetGroup(@.Type)
AND RecordID <> @.RecordID
AND Receiver = @.Receiver
AND StartDate IS not NULL
AND dbo.CheckDateTime(
dbo.ExtractDate(@.StartDate),
dbo.ExtractDate(@.EndDate),
dbo.ExtractTime(@.StartDate),
dbo.ExtractTime(@.EndDate),
dbo.ExtractDate(StartDate),
dbo.ExtractDate(EndDate),
dbo.ExtractTime(StartDate),
dbo.ExtractTime(EndDate),
dbo.UsesTime(@.Type),
dbo.UsesTime([Type])) = 1
RETURN @.MessageCount
I replaced most of functions used in query with procedures. But how to implement query in better way if I cannot use functions?You should use scalar UDFs sparingly especially in SELECT statements. It hurts performance, limits plan choices and complicates performance troubleshooting. And using scalar UDFs for simple expressions is even more problematic. So remove the UDFs and replace them with the expression directly so it will work in any version of SQL Server and you can get the best performance also.
No comments:
Post a Comment