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
- Non-Schema Bound Function
- Create Table 1
- Add data
- Update data with non-schema bound function
- Schema Bound Function
- Create Table 2
- Add data
- 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
Image
Explanation
- We can see that the Non-Schema bound function has a Table Spool
Statistics
Statistics I/O
Statistics I/O – Image
Statistics I/O – Textual
================================================================================
Update table 1 ...
================================================================================================================================================================
Table 'tbl1'. Scan count 1, logical reads 145382, physical reads 125, read-ahead reads 654, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 202750, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Updated table 1
================================================================================================================================================================
Update table 2 ...
================================================================================================================================================================
Table 'tbl2'. Scan count 1, logical reads 45522, physical reads 0, read-ahead reads 658, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
================================================================================================================================================================
Updated table 2
================================================================================================================================================================
Statistics I/O – Explanation
- We can see that the Non-Schema bound function
- References a Worktable
- And, has more IO against the targeted table
