question

Blackhawk-17 avatar image
Blackhawk-17 asked

What is SCHEMABINDING?

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

database-designddlseeder-questionwith-clause
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
John Sansom avatar image
John Sansom answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vignesh.ms avatar image
vignesh.ms answered
@ 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)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

epollack avatar image
epollack answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

EricI avatar image
EricI answered
WITH SCHEMABINDING is allowed in Stored Procedures as of SQL Server 2014, 2016, and Azure, and most likely beyond now.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

which doesn't actually answer the question...
0 Likes 0 ·
GoldenT avatar image
GoldenT answered
SCHEMABINDING helps safely maintain user defined objects
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

EricI avatar image
EricI answered
The top answer does a great of answering the question, and in 2009 when it was written, Schemabinding was not allowed in stored procedures, but as of SQL Server 2014, that statement that said it was only for functions and views became incorrect for newer versions of SQL Server.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.