FAQ on View in SQL Server

FAQ on View in SQL Server

80. What is View? Use? Syntax of View?
A view is a virtual table made up of data from base tables and other
views, but not stored separately.
• Views simplify users perception of the database (can be used to
present only the necessary information while hiding details in
underlying relations)
• Views improve data security preventing undesired accesses
• Views facilite the provision of additional data independence

81. Does the View occupy memory space?
No

82. Can u drop a table if it has a view?
Views or tables participating in a view created with the SCHEMABINDING
clause cannot be dropped, unless the view is dropped or changed so
that it no longer has schema binding. In addition, ALTER TABLE
statements on tables that participate in views having schema binding
will fail if these statements affect the view definition.
If the view is not created using SCHEMABINDING, then we can drop the
table.

83. Why doesn't SQL Server permit an ORDER BY clause in the definition
of a view?
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, we can't fully explain the restriction here. However, if you
need to be able to specify an ORDER BY clause in a view, consider
using the following workaround:
USE pubs
GO
CREATE VIEW AuthorsByName

AS
SELECT TOP 100 PERCENT *
FROM authors
ORDER BY au_lname, au_fname
GO
The TOP construct, which Microsoft introduced in SQL Server 7.0, is
most useful when you combine it with the ORDER BY clause. The only
time that SQL Server supports an ORDER BY clause in a view is when it
is used in conjunction with the TOP keyword. Note that the TOP keyword
is a SQL Server extension to the ANSI SQL-92 standard.

For More SQL SERVER Frequently Asked Interview Questions