How could you ensure that your VIEW is binded to your table in such a way that the tables cannot be deleted without deleting the VIEW. (SCHEMABINDING)

Finally I got some time to write this post; yes it is quite possible with the schemabing clause, creating a view with SCHEMABINDING option locks the underlying tables and prevents any changes that may change the table schema.

Imagine that you have created a view and you have altered the schema of underlying table (deleted one column).Next time when you run your view, it will fail. Here is when SCHEMABINDING comes into picture

lets do some practical on this.. we having a able named  DEMOFORSCHEMABINDING_TAB.

select * from demoforschemabinding_tab

id          name                 department           salary         pf_contribution

----------- -------------------- ----------------- -------------- -----------------

1           Aadita               IT                   35000.00       12000.00

2           Aakarsh              Sofware              45000.00       13000.00

3           Aaliya               Insurance            56000.00       13500.00

4           Falak                Account              35000.00       12000.00

(4 row(s) affected)

Now we are trying to create a view without SCHEMABINDING option

create view vw_without_schmeabinding_demoforschemabinding_tab

as (select * from demoforschemabinding_tab)

Command(s) completed successfully.

select * from vw_without_schmeabinding_demoforschemabinding_tab

id          name          department  salary   pf_contribution

----------- --------      ----------  ------   ---------------

1           Aadita         IT         35000.00  12000.00

2           Aakarsh        Sofware    45000.00  13000.00

3           Aaliya         Insurance  56000.00  13500.00

4           Falak          Account    35000.00  12000.00

(4 row(s) affected)

Now let’s try to drop a column say pf_contribution

alter table demoforschemabinding_tab

drop column pf_contribution

Command(s) completed successfully.

Let’s see the table data.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- --------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Yes the column has been dropped. Now it is having only four columns.

Let find the same record from the view we have created in the above step.

select * from vw_without_schmeabinding_demoforschemabinding_tab

Msg 4502, Level 16, State 1, Line 1

View or function 'vw_without_schmeabinding_demoforschemabinding_tab' has more column names specified than columns defined.

The reason for this error message: The underlying schema (structure) of table has been changed on which the view was created.

So the question of the discussion is how to ensure that the schema (structure) of the table cannot be modified or dropped unless that view is dropped or changed. Otherwise, the Database Engine raises an error when executing ALTER statement on the table.

The answer to this question is using SCHEMABINDING. Let’s take an example to explain SCHEMABINDING.

select * from demoforschemabinding_tab

id          name                 department           salary

----------- -------------------- -------------------- -----------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now we will try to create a view on this table with SCHEMABINDING

create view vw_with_schmeabinding_demoforschemabinding

with schemabinding

as ( select id,name,department,salary from dbo.demoforschemabinding_tab)

Command(s) completed successfully.

Let’s run a select statement with the above created view

select * from vw_with_schmeabinding_demoforschemabinding

id          name                 department           salary

----------- -------------------- -------------------- ---------------------------------------

1           Aadita               IT                   35000.00

2           Aakarsh              Sofware              45000.00

3           Aaliya               Insurance            56000.00

4           Falak                Account              35000.00

(4 row(s) affected)

Now let’s try to modify the structure of the underlying table DEMOFORSCHEMABINDING_TAB

alter table demoforschemabinding_tab

drop column salary

Msg 5074, Level 16, State 1, Line 1

The object 'vw_with_schmeabinding_demoforschemabinding' is dependent on column 'salary'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN salary failed because one or more objects access this column.

The ALTER fails and gives the message 'vw_with_schmeabinding_demoforschemabinding' is depends on column salary so it cannot be modified or dropped. So this is the benefit of schema binding in view that is it will not allow to modify the underlying structure of table if any view is depends on the table schema.

Post Reference: Vikram Aristocratic Elfin Share