View is the virtual table which is defined by the query.
The select statement can not include ODER BY clause, unless we specify the TOP or FOR XML. otherwise the CREATE VIEW will fail with
Msg 1033, Level 15, State 1, Procedure vHRdepartment, Line 6
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
check view definition
with check option
When we insert the row does not fit in the view definition. It wont show up in the result set.
To avoid such row being insert, we can create use with check option on the view.
Lets try to insert the row
And we will get the below error
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
We still can insert the row to the base table. the CHECK OPTION only affect on the view level.
Once with encryption is enable, we no longer access the view definition.
With schemabinding is to prevent modification of base table could cause view become invalid.
Lets update the base table.
The add column will works fine. Lets update the view with schemabinding.
Above alter will fail because the schemabinding will require we explicit defined what column we want the view to see. can not use *. and the table and view name must use 2 part name. EX: dbo.v3
Msg 1054, Level 15, State 6, Procedure v3, Line 4
Syntax '*' is not allowed in schema-bound objects.
Lets try to alter the base table
If the alter does not affect the view. then it is ok to alter.
If the alter affect the view, then the alter will fail
Msg 3728, Level 16, State 1, Line 1
'c2' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.