Database Reference
In-Depth Information
Listing 13-8.
Invocation overhead: T-SQL functions
create function dbo.EvenNumber(@Num int)
returns int
with schemabinding
as
begin
return (case when @Num % 2 = 0 then 1 else 0 end)
end;
create function dbo.EvenNumberInline(@Num int)
returns table
as
return
(
select (case when @Num % 2 = 0 then 1 else 0 end) as Result
);
We use scalar and inline multi-statement functions in our test, and we measure average execution time for the
statements shown in Listing 13-9. The results are shown in Table
13-2
.
Listing 13-9.
Invocation overhead: Test statements
set statistics time on
-- CLR UDF - no data access context
select count(*)
from dbo.Numbers
where dbo.EvenNumberCLR(Num) = 1
-- CLR UDF - data access context
select count(*)
from dbo.Numbers
where dbo.EvenNumberCLRWithDataAccess(Num) = 1
-- TSQL - Scalar UDF
select count(*)
from dbo.Numbers
where dbo.EvenNumber(Num) = 1;
-- TSQL - Multi-statement UDF
select count(*)
from
dbo.Numbers n cross apply
dbo.EvenNumberInline(n.Num) e
where
e.Result = 1
set statistics time off
go
