Using Schema Binding Option in User Defined Functions

Using Schema Binding Option in User Defined Functions:

General notion is using User Defined functions(UDF) slows down your query performance and so was not a ideal method for choosing a function when the same does not
touch any tables inside the function.

If one looks at the execution plan of where UDFs are involved you could find a item named Spooler which by normal terms means as stores data from input into a temp table
to optimise the rewinds and this would be used only when Schemabinding is not set for a function and this causes a overhead in terms of performance.

Before going to much detail into our example let us understand what is schema binding?

Let us suppose I decide to drop or rename a table from a database then all of my existing procedures/functions/views would get affected while executing with error as “ Object doesnot exist” to avoid this SQL has a feature called as Schema Binding which helps not to drop tables when the same is referred in procedures/functions/views and
the same can be specified while creating proc as
Ex: Create Procedure A with SchemaBinding as begin -- Code Logic End
Let us see the above in terms of a example.
1) First step we create DOCS_HISTORY table with two columns(DocID and DocName)
2) Insert some 10000 rows into the table
3) Create 2 functions one with Schemabinding and other none and which just adds value of ‘WO’ or ‘W’ for the input
4) Call the 2 functions in UPDATE and use getdate function before and after to track which function performs better.

SET NOCOUNT ON

-- Create DOCS_HISTORY Table
IF EXISTS(SELECT 'X' FROM sysobjects WHERE type = 'U' and name = 'DOCS_HISTORY')
DROP TABLE DOCS_HISTORY
GO

CREATE TABLE DOCS_HISTORY
(
DOC_ID INT NOT NULL IDENTITY (1, 1),
DOC_NAME VARCHAR(100) NOT NULL
)
GO


ALTER TABLE DOCS_HISTORY ADD CONSTRAINT PK_DOCS_HISTORY PRIMARY KEY CLUSTERED(DOC_ID)
GO

--Insert 10000 rows
Declare @I int
Select @I = 1

WHILE @I < i =" @I+1" type =" 'FN'" name =" 'Return_WO_SchemaBinding')" type =" 'FN'" name =" 'Return_W_SchemaBinding')" doc_name =" dbo.Return_WO_SchemaBinding(DOC_NAME)" doc_name =" dbo.Return_W_SchemaBinding(DOC_NAME)">With Schemabinding took 2 seconds to complete when compared to 5 seconds which gives amazing 60% better performance