Thursday, 6 May 2010

Schema binding in SQL Server

I recently had to make changes to a database table – simple changes, just adding 2 NULL columns. However, I was confronted with an error because the table in question was bound to a view created with SCHEMABIND.

Schema binding allows dependencies to be created between database object so that changes cannot be made accidentally which might otherwise break a dependant object. In the case of a view it would be possible to add, alter or drop table columns the view depends on. Schema binding prevents this.

To modify a database object that participates in schema binding you must drop or alter the bound object to remove the schema binding before making the necessary changes.

Views are not schema bound by default. To ensure views participate in schema binding use the "WITH SCHEMABINDING" clause when creating them.

USE [DatabaseNameHere]

SELECT … snip …
FROM [dbo].[TableNameHere] 

Schema binding is commonly used with objects like views and User Defined Functions (UDF).

Note that for UDFs schema binding can result in performance gains due to the way SQL Server prepares query plans etc.