Wednesday, March 28, 2012

Replacement of functions by procedures for SQL 7.0

Hello to everybody. I have problems with porting my sql function to elder version of SQL server.
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