Transact SQL – Scaler Functions – Schema Bound

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
    • Add data
    • Update data with non-schema bound function
  2. 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

queryplan.20190107.0820am

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

Statistics

Statistics I/O
Statistics I/O – Image

statistics.io.20190107.0818am

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
  1. We can see that the Non-Schema bound function
    • References a Worktable
    • And, has more IO against the targeted table

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s