Background

Want to have a quick talk about Scaler Functions; specifically Schema Bound.

Transact SQL

DDL

Outline

We are going to create two functions, dbo.left10.No and dbo.left10.Yes.

dbo.left.No will be non-schema bound and dbo.left10.Yes will be schema bound.

Function

Function – [dbo].[left10.No]

Code
```use [tempdb]
go

if object_id('[dbo].[left10.No]') is null
begin

exec('create function [dbo].[left10.No]
(
@input varchar(100)
)
returns varchar(10)
as
begin

return reverse
(
left(@input, 10)
)

end
')

end
go

ALTER FUNCTION [dbo].[left10.No]
(
@input varchar(100)
)
returns varchar(30)
as
begin

return reverse
(
left(@input, 10)
)

end
go

```

Function – [dbo].[left10.Yes]

Code
```use [tempdb]
go

if object_id('[dbo].[left10.Yes]') is null
begin

exec('create function [dbo].[left10.Yes]
(
@input varchar(100)
)
returns varchar(10)
as
begin

return reverse
(
left(@input, 10)
)

end
')

end
go

ALTER FUNCTION [dbo].[left10.Yes]
(
@input varchar(100)
)
returns varchar(10)
with schemabinding
as
begin

return reverse
(
left(@input, 10)
)

end
go

```

DML

Outline

In our sample exercise we compare the effect of updating a table with a non-schema bound Scaler function against updating a similar table with a schema bound Scaler function.

Steps

1. Non-Schema Bound Function
• Create Table 1
• Update data with non-schema bound function
2. Schema Bound Function
• Create Table 2
• Update data with schema bound function

Code

```
use [tempdb]
go

set nocount on
go

set statistics io on
go

set XACT_ABORT on
go

if object_id('[dbo].tbl1') is not null
begin

drop table [dbo].tbl1

end
go

if object_id('[dbo].[tbl2]') is not null
begin

drop table [dbo].[tbl2]

end
go

create table [dbo].[tbl1]
(
[id] int not null identity(1,1)
, [data] varchar(60) not null
, [dataProcessed] varchar(100) null
)

create table [dbo].[tbl2]
(
[id] int not null identity(1,1)
, [data] varchar(60) not null
, [dataProcessed] varchar(100) null
)

declare @lMaxNumberofRecords int

set @lMaxNumberofRecords = 100000

;with
L0 as (select 1 as C union all select 1)       --2 rows
,L1 as (select 1 as C from L0 as A, L0 as B)    --4 rows
,L2 as (select 1 as C from L1 as A, L1 as B)    --16 rows
,L3 as (select 1 as C from L2 as A, L2 as B)    --256 rows
,L4 as (select 1 as C from L3 as A, L3 as B)    --65536 rows
,L5 as (select 1 as C from L4 as A, L4 as B)    --4,294,967,296 rows
,Nums as
(
select

row_number()

over
(

order by
(
select 0
)
) as N

from L5
)

insert into tbl1
(
[data]
)
select
CAST (newid() as varchar(36))
from Nums
where N<=@lMaxNumberofRecords

;with
L0 as (select 1 as C union all select 1)       --2 rows
,L1 as (select 1 as C from L0 as A, L0 as B)    --4 rows
,L2 as (select 1 as C from L1 as A, L1 as B)    --16 rows
,L3 as (select 1 as C from L2 as A, L2 as B)    --256 rows
,L4 as (select 1 as C from L3 as A, L3 as B)    --65536 rows
,L5 as (select 1 as C from L4 as A, L4 as B)    --4,294,967,296 rows
,Nums as
(
select

row_number()

over
(

order by
(
select 0
)
) as N

from L5
)

insert into tbl2
(
[data]
)
select
CAST (newid() as varchar(36))
from Nums
where N<=@lMaxNumberofRecords

go

print replicate('=', 160)
go

update statistics [dbo].[tbl1] with fullscan
go

update statistics [dbo].[tbl2] with fullscan
go

update tbl
set    dataProcessed = [dbo].[left10.No](tbl.[data])
from   tbl1 tbl
go

print replicate('=', 160)
go

update tbl
set    dataProcessed = [dbo].[left10.Yes](tbl.[data])
from   tbl2 tbl
go

print replicate('=', 160)
go

drop table [dbo].[tbl1]
go

drop table [dbo].[tbl2]
go

```

Query Plan

Explanation
1. We can see that the Non-Schema bound function has a Table Spool

Statistics

Statistics I/O

Statistics I/O – Textual
```================================================================================
Update table 1 ...
================================================================================================================================================================
Updated table 1
================================================================================================================================================================
Update table 2 ...
================================================================================================================================================================