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:
CREATE VIEW dbo.vEmployeeList
Select EmployeeID, ([FirstName] + ' ' + [LastName]) as FullName
INNER JOIN Person.Contact
Person.Contact.ContactID = HumanResources.Employee.ContactID
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
ALTER TABLE Person.Contact DROP COLUMN LastName
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."
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!