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

more ▼

asked Nov 04 '09 at 11:40 AM in Default

Blackhawk-17 gravatar image

11.8k 28 30 35

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

2 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 '09 at 12:56 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

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 '09 at 02:47 PM TimothyAWiseman
Both very good points - I don't use ssms, so I tend to forget about it's foibles - I especially don't like the schema binding removal - because it makes it easy for someone to trash a schema...
Nov 04 '09 at 05:08 PM Matt Whitfield ♦♦
(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 '09 at 11:43 AM

John Sansom gravatar image

John Sansom
897 2

The inclusion of Stored Procedures in your answer prevented me from accepting it.
Nov 04 '09 at 01:03 PM Blackhawk-17
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Nov 04 '09 at 11:40 AM

Seen: 15780 times

Last Updated: Nov 04 '09 at 11:40 AM