Schema Binding in SQL server

Schema Binding :
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:
1. The objects should be referred to by their owner names (two part name - [ownername.objectname] ).
2. SELECT * is not permitted.

We see that using below scripts.
create table schema_binding_table
(
id int,
msg varchar(10)
)

insert into schema_binding_table values
(1,'msg1'),
(2,'msg2'),
(3,'msg3')

select * from schema_binding_table

id msg
----------- ----------
1 msg1
2 msg2
3 msg3

(3 row(s) affected)

Chance for erros while creating view with schema binding:
The below error messgae clearly mention that SELECT * is not permitted whille creating the view with schema binding.
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT * FROM schema_binding_table

Msg 1054, Level 15, State 6, Procedure schema_binding_VIEW, Line 2
Syntax '*' is not allowed in schema-bound objects.

Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view.

CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM schema_binding_table

Msg 4512, Level 16, State 3, Procedure schema_binding_VIEW, Line 2
Cannot schema bind view 'schema_binding_VIEW' because name 'schema_binding_table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Successful syntax:
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM dbo.schema_binding_table

Command(s) completed successfully.
We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

1. Drop table:

drop table schema_binding_table
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'schema_binding_table' because it is being referenced by object 'schema_binding_VIEW'.
2. Alter table:alter table schema_binding_table alter column msg varchar(20)
Msg 5074, Level 16, State 1, Line 1
The object 'schema_binding_VIEW' is dependent on column 'msg'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN msg failed because one or more objects access this column.

Read More