I want to
revisit database views. They act like a database table. They are defined by a
SELECT statement. Creation of the view must be the first statement in the
batch. The name of the view is in the same namespace as tables, procedures,
functions and synonyms. Thus, you cannot have a view with the same name as a
table. In general, you are not allowed to use ORDER BY in the view SQL. You
also cannot pass parameters into the view. And you cannot reference temporary
tables in the view SQL.
There are a
few options available to view creation. If you use SCHEMABINDING, then the
underlying tables that the view depends on cannot be changed with causing an
error. Then there is the WITH CHECK OPTION. That prevents any DML applied to
the view from either adding new rows into the view. It also prevents DML from
removing any rows out of the view.
Speaking of
DML with respect to views, there are certain rules that any such DML must
conform to if affecting views. The changes can only affect 1 table through the
view. There are scenarios where you cannot even do DML on a view:
·
If
the DML affects more than 1 underlying table
·
If
there are functions or expressions in the view SQL
·
If
there is a UNION in the view SQL
·
If
there is a TOP/OFFSET in the view SQL
·
IF
there is a DISTINT in the view SQL
If you want to inspect the metadata on a view,
consult sys.views.
