View in SQL Server

What is View in SQL?

• View is a virtual table.
• it won’t occupies any memory space for the data.
• it is referencing to the other table like pointer concept in programming language.
• View definition contains only SELECT statement.
• Views simplify the user perception of the database
• View improves the data security by preventing undesired access
• View faciliates the provision of additional data independence
• View doesn’t allow ORDER BY clause in view definition.

Can You Drop a table, If it has a view?

If the view is created with SCHEMABINDING clause from Table or views then base object can’t delete otherwise you can.

Why SQL Server Doesn’t allow ORDER BY Clause in View definition?
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, also if you use the ORDER BY Clause then it will lead to major performance issue, sothat by default SQL server doesn’t allow this clause in view definition.

If you try to create view with ORDER BY clause it will throw the following error.
Msg 1033, Level 15, State 1, Procedure , Line 2
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.