[1] Views

Question 1

How does a View differ from a table?

Answer 1

A view is a query that can call one table or many joined tables.

Question 2

What is the syntax for making a View?

Answer 2

CREATE VIEW schema.viewname
WITH SCHEMABINDING
AS
SELECT...
FROM
WITH CHECK OPTION

Question 3

What is the purpose of Schema Binding?

Answer 3

It guarantees that the underlying table structures cannot be altered without dropping the view.

Question 4

In what situation is the use if a View better than using a table? Provide an example.

Answer 4

To simplify the query. example: for a dataset whose data comes from multiple tables joined together.

Question 5

Give the four View options and their purposes.

Answer 5

WITH ENCRYPTION: other cannot read the underlying query.
WITH SCHEMABINDING: underlying table cannot be altered without dropping the view.
WITH VIEW_METADATA: returns the metadata of the view, not the table
WITH CHECK OPTION: it restricts modification to only rows that match the filter condition

Question 6

Provide four restrictions on Views

Answer 6

1. Cannot pass parameters to a View.
2. Cannot add an ORDER BY to the select statement of a View.
3. Cannot create a table from a View.
4. Cannot reference temporary tables.

Question 7

What is a Partitioned View?

Answer 7

One which combines multiple tables with a UNION statement.