CLR - Pro SQL Server Internals - page 258

-- 262,144 rows
;with N1(C) as (select 0 union all select 0)
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2)
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2)
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2)
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2)
,N6(C) as (select 0 from N4 as T1 cross join N4 as T2 cross join N2 AS T3)
,Nums(Num) as (select row_number() over (order by (select null)) from N6)
insert into dbo.Numbers(Num)
select Num from Nums;
Invocation of the T-SQL scalar function introduces higher overhead when compared to its CLR counterpart. Let's
prove that with a test where we will use functions that accept an integer value as a parameter and return 1 when this
value is even. The CLR implementation is shown in Listing 13-7.
Listing 13-7. Invocation overhead: CLR functions
[Microsoft.SqlServer.Server.SqlFunction(
IsDeterministic=true,
IsPrecise=true,
DataAccess=DataAccessKind.None
)]
public static SqlInt32 EvenNumberCLR(SqlInt32 num)
{
return new SqlInt32((num % 2 == 0) ? 1 : 0);
}
[Microsoft.SqlServer.Server.SqlFunction(
IsDeterministic=true,
IsPrecise=true,
DataAccess=DataAccessKind.True
)]
public static SqlInt32 EvenNumberCLRWithDataAccess(SqlInt32 num)
{
return new SqlInt32((num % 2 == 0) ? 1 : 0);
}
There is a set of the attributes specified for each function. Those attributes describe the function behavior, and
they can help Query Optimizer generate a more efficient execution plan.
In our case, there are three attributes specified. IsDetermenistic tells if the function is deterministic, and it
always returns the same result for specific parameter values and database states. Our function is deterministic—even
numbers are always even. As a counter example, you can think about getdate() system function, which is not
deterministic—results will be different every time it is called.
IsPrecise describes if functions involve imprecise calculations, for example, using floating-point operations.
Finally, the DataAccess attribute indicates if a function performs any data access. If this is the case, SQL Server
calls the function in a different context that will allow it to access the data in the database. Setting up such a context
introduces additional overhead during the functional call, which you will see in our tests below.
T-SQL implementation of those functions is shown in Listing 13-8.