To first understand the word Schema Binding .. let us first know where we use it .... I am sure we are all aware abotu SQL Views... View is nothing but a Virtual Table in SQL, which is created for many purposes , eg simplify stuff, security of data , less space required.... etc
Let us consider a DBA created a View called vw_table1 for a table dbo.table1 in Database without schemabinding option.. and later after sometimes..someone tries to add a new column in the table.. the table gets changed, however the View will break . you might have to drop and recreate the view in this case .
In order to avoid this situation we make use of ScehmaBinding .. Schema binding will lock the table preventing any changes that may change the table schema.
View with Schemabinding syntax
CREATE VIEW vw_table1 WITH
schemabinding AS
Select Id,Number,Address
FROM dbo.Employees
GO
Let us consider a DBA created a View called vw_table1 for a table dbo.table1 in Database without schemabinding option.. and later after sometimes..someone tries to add a new column in the table.. the table gets changed, however the View will break . you might have to drop and recreate the view in this case .
In order to avoid this situation we make use of ScehmaBinding .. Schema binding will lock the table preventing any changes that may change the table schema.
View with Schemabinding syntax
CREATE VIEW vw_table1 WITH
schemabinding AS
Select Id,Number,Address
FROM dbo.Employees
GO
No comments:
Post a Comment