x

What is SCHEMABINDING?

What is the purpose of the WITH SCHEMABINDING clause and where can it be used?

more ▼

asked Nov 04, 2009 at 11:40 AM in Default

avatar image

Blackhawk-17
12k 30 35 42

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

WITH SCHEMABINDING can be used in Views and T-SQL Functions, but not in Stored Procedures.

Objects that are schema bound can have their definition changed, but objects that are referenced by schema bound objects cannot have their definition changed.

Schema binding effectively states that the meta-data which is created at object creation time can then be relied upon to be accurate at all times, and use of sp_refreshsqlmodule is not necessary.

Schema binding can also significantly increase the performance of user defined functions in SQL Server 2005 and above. However, caution should be applied, as this is definitely not always the case.

more ▼

answered Nov 04, 2009 at 12:56 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

A beautiful answer, but I think there are a couple of things worth adding.

First, while "but objects that are referenced by schema bound objects cannot have their definition changed." is technically true, you cannot generally rely on schemabinding as a way to prevent changes by people with appropriate permissions. SSMS will give you a warning, but then remove schemabinding in order to make changes ordered through the gui.

Also, it is worth noting that schemabinding is required for a view if the view is going to be indexed.

Nov 04, 2009 at 02:47 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

Schema binding is a way of ensuring that the objects referenced within a function or view, do not change their definition in any way that would break the binded object. Objects are prevented from changing their defintions while they are schema bound.

more ▼

answered Nov 04, 2009 at 11:43 AM

avatar image

John Sansom
897 2 4

(comments are locked)
10|1200 characters needed characters left

@ Matt Whitfield ♦♦

Hi I have tried to alter the table which was schema bonded. It allows. But you mentioned "objects that are referenced by schema bound objects cannot have their definition changed."

am I wrongly understood?

 CREATE TABLE Sampletbl ( col1 DATETIME ) 
 
 INSERT  INTO Sampletbl
 VALUES  ( GETDATE() + 1 )
 
 CREATE FUNCTION SampleFunction ( )
 RETURNS DATETIME
     WITH SCHEMABINDING
 AS 
     BEGIN 
 
         DECLARE @t DATETIME
 
         SELECT  @t = col1
         FROM    dbo.Sampletbl
 
         RETURN @t
 
     END
 
 SELECT  dbo.SampleFunction()
 
 DROP TABLE Sampletbl
 
 ALTER TABLE Sampletbl ADD col2 VARCHAR(100)
more ▼

answered May 17, 2014 at 02:34 PM

avatar image

vignesh.ms
1

(comments are locked)
10|1200 characters needed characters left

When you run the SQL provided above, the DROP TABLE Sampletbl will fail with a SCHEMABINDING error. In order to drop the table, the function needs to be dropped first.

The beauty of schemabinding in a large environment with lots of views, functions, and stored procs, is that it helps ensure that when you make changes to an object, you are forced to consider all dependent objects at the same time, rather than miss something and get burned later.

more ▼

answered May 17, 2014 at 03:44 PM

avatar image

epollack
11 1

(comments are locked)
10|1200 characters needed characters left

WITH SCHEMABINDING is allowed in Stored Procedures as of SQL Server 2014, 2016, and Azure, and most likely beyond now.

more ▼

answered Oct 02 at 11:41 AM

avatar image

EricI
0

which doesn't actually answer the question...

Oct 02 at 11:42 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x79
x34
x26
x8

asked: Nov 04, 2009 at 11:40 AM

Seen: 43396 times

Last Updated: Oct 03 at 02:03 AM

Copyright 2016 Redgate Software. Privacy Policy