We help IT Professionals succeed at work.

SCHEMABINDING option for UDF - SQL Server

Hi,

Can you please explain to me in simple terms why we are going to use

SCHEMABINDING option for UDF

Please provide me a simple example on this. Please provide me code. NO articles please ...

Thanks
Comment
Watch Question

here is the link which will give you an idea.

http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx

I know you TOLD that no articles please, but writing detailed description with code would be like one article so it is good to reference already well prepared link. Above link is not a BIG ARTICLE. simple small article with code snippet, if you don't understand anything out of that, kindly get back to me.

Author

Commented:
Does it add spooler operator "SELECT" stmts also ?

Thanks

Author

Commented:
BTW: What is spool ?
there are mainly two types of spool we need to beware of, table spool, index spool, which you can see in query execution plan. spool slow up the performance of the TSQL. more details, you can find in below link:

http://blogs.msdn.com/b/sqlperf/archive/2007/08/30/spool-operators-in-query-plan.aspx
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
>> simple terms why we are going to use SCHEMABINDING option for UDF

In simple terms

1, for functions that reference sql objects, it creates a dependency and does not allow changes to the base objects until the function is dropped. this ensures the function will always work as intended

2, for functions that do not reference any sql object (e.g. takes 2 inputs and performs math on them), it creates a "pseudo-dependency" such that whenever it is used, SQL Server does not need to perform additional functions like scanning system tables or temp tables to ensure the schema is not changed while the query (with the function) is running.  It increases performance slightly because of this.
see below script.

create table TestViewUpdate
(
fName varchar(10),
lName varchar(10)
)
GO

insert into testViewUpdate VALUES('RITESH','SHAH')
GO

create function getTable()
RETURNS @tab TABLE 
(
[FirstName] [nvarchar](50) NULL, 
[LastName] [nvarchar](50) NULL
)
WITH SCHEMABINDING
AS
BEGIN
INSERT INTO @tab
SELECT fName,lName from dbo.TestViewUpdate
RETURN
end
GO

SELECT * FROM dbo.GETTABLE()
go

DROP TABLE TESTVIEWUPDATE --can't drop table as it is used in function and function is SCHEMABINDING

Open in new window