Jun 6, 2006

Funny behavior in SQL Server 2000 UDF

This is one interesting observation in User defined functions (UDF) in SQL Server 2000.
Be careful when you use a group by and have a scalar UDF in the select. What you get out of a query depends on what the query execution plan is. Try this out..

I am creating a very simple function.

create function dbo.Fn1(@a int, @b int)
returns int
as
begin
return @a-@b
end


Now I create a temp table that I will use to query.

create table #temp (id int)
insert into #temp
select 1
union all select 2
union all select 3
union all select 4
union all select 5

-- Check out the first query and the second query results.
select dbo.Fn1(0,id), dbo.Fn1(0,0), dbo.Fn1(0,1), dbo.Fn1(0,2), dbo.Fn1(0,3)
from #temp

select dbo.Fn1(0,id), dbo.Fn1(0,0), dbo.Fn1(0,1), dbo.Fn1(0,2), dbo.Fn1(0,3)
from #temp
group by dbo.Fn1(0,id)


drop table #temp
Notice any difference in the results?

Explanation?? None that I have. Just a few words of caution. When you use scalar udf with group by clause, remember that you might have this issue. Please do let me know your thoughts.

This issue, however, seems to be fixed in SQL Server 2005.

0 comments:

Post a Comment