Friday, March 28, 2014

Protect your Tables with SQL Schemabinding


Imagine that you have created a standard view on a set of tables. What would happen if you deleted a column of the underlying table (altering the view’s “schema”)?

I'll tell you what happened to me... the next time when I ran my view, it failed, because it was be missing needed columns!

Here is when SCHEMABINDING comes to the rescue. Creating a view WITH SCHEMABINDING locks the underlying tables and prevents any changes that may change the table schema!
Here is an example of a view with SCHEMABINDING option:

USE AdventureWorks
GO
CREATE VIEW dbo.vEmployeeList
With SCHEMABINDING
As
Select EmployeeID, ([FirstName] + ' ' + [LastName]) as FullName
FROM HumanResources.Employee
INNER JOIN Person.Contact
on
Person.Contact.ContactID = HumanResources.Employee.ContactID
GO
You have now effectively prevented the use of the ALTER or DROP statement on the HR.Employees table.
Or have you?

If you try and drop the column

Use AdventureWorks
GO
ALTER TABLE Person.Contact DROP COLUMN LastName
GO

this will be your message:

"The object vEmployeeList' is dependent on column 'FirstName'.
ALTER TABLE DROP COLUMN FirstName failed because one or more objects access this column.
(Microsoft SQL Server, Error: 5074)"
On the other hand, if you attempt to remove a field in the table editor of the GUI studio you will be presented with this message:
" - Warning: The following schema-bound objects will be modified:
- View 'dbo.vEmployeeList': schema binding will be removed."
Be Careful!!!!
If you click "yes" your removal of the field in the table will succeed and your view will now be defunct!

SCHEMABINDING! Good luck gang!
 

No comments: