Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms

Question 1

What are views?

Answer 1

In SQL Server, you can use views to store and re-use queries in the database. Views appear just like tables in that you can select from them and filter the results. You can even insert, update, and delete rows through views.

Every views is defined by a SELECT statement which can reference multiple base tables as well as other views.

Views are a way of simplifying the underlying complexity required to join multiple tables together making it easier for users or application to access data.

They provide a level of abstraction between the end user and the database.

Question 2

How do you create a view?

Answer 2

You name the view and then specify the SELECT statement that will constitute the view.

CREATE VIEW Sales.OrderTotalsByYear
WITH SCHEMABINDING
AS

SELECT …
FROM …

GO

Question 3

Does the CREATE VIEW statement have to come first when you create a view?

Answer 3

Yes. The CREATE VIEW statement must be the first statement in a batch. You cannot put other T-SQL statements ahead of it or make the CREATE VIEW statement conditional by putting it inside an IF statement.

Question 4

Can you specify the set of output columns following the view name when creating a view?

Answer 4

Yes. You can specify the set of output columns following the view name, although the view is more self-documenting if the column names are specified in the SELECT statement e.g.

CREATE VIEW Sales.OrderTotalsByYear(orderyear, qty)
AS

SELECT …
FROM …

GO

Question 5

What does the WITH ENCRYPTION option do?

Answer 5

Using WITH ENCRYPTION, you can specify that the view text should be stored in an obfuscated manner. This makes it difficult for users to discover the SELECT text of the view.

Question 6

What does the WITH SCHEMABINDING option do?

Answer 6

Using WITH SCHEMABINDING, you can bind the view to the table schemas of the underlying tables which guarantees that the underlying table structures cannot be altered without dropping the view.

Question 7

What does the WITH VIEW_METADATA option do?

Answer 7

When specified, returns the metadata of the view instead of the base table.

Question 8

What does the WITH CHECK OPTION option do?

Answer 8

When specified, this option prevents any updates through the view that would cause some rows to no longer satisfy the WHERE clause of the view. WITH CHECK OPTION prevents “disappearing rows”.

Question 9

Do view names have to be unique?

Answer 9

Yes. Every view is a database object. Thew view cannot have the same schema name and object name combination as any other schema-scoped objects in the database.

Question 10

What are some examples of schema scoped objects?

Answer 10

(1) Views, (2) Tables, (3) Stored procedures, (4) Functions, (5) Synonyms

Question 11

What are some restrictions of views?

Answer 11

(1) You cannot add an ORDER BY to the SELECT statement in a view A view must appear just like a table, (2) You cannot pass parameters to a view, (3) A view cannot reference variables inside the SELECT statement, (4) A view cannot create a table (permanent or temp), (5) A view can reference only permanent tables; a view cannot reference a temp table.

Question 12

Are the results of a view ever ordered?

Answer 12

No Results of a view are never ordered. You must add your own ORDER BY when you SELECT from the view. You can include an ORDER BY in a view only by adding the TOP operator or OFFSET FETCH clause. But even then, the results of the view will not be ordered.

Question 13

Why does a view only allows to have a single SELECT statement?

Answer 13

A key requirement is that a view will return only one result set so that the view can always appear as though it were a table. However, you can combine SELECT statements that return the same result sets by using a UNION/UNION ALL.

Question 14

Is data stored with a view?

Answer 14

No. Normally, a view is just a definition by a SELECT statement of how the results should be built. No data is stored.

Question 15

What is an indexed view?

Answer 15

Indexed views have a unique clustered index and materialize the data. The actual results of the view query are stored on disk in the clustered index structure.

Question 16

What happens when you query a view?

Answer 16

When you query a regular nonmaterialized view, the SQL Server Query Optimizer combines your outer query with the query embedded in the view and processes the combined query. When you look at query plans based on queries that SELECT from views, you will see the referenced base tables in the query plan, but not the view itself.

Question 17

How do you alter a view?

Answer 17

You can use the ALTER VIEW command to change the view’s structure and add/remove properties. An ALTER VIEW simply redefines how the view works by reissuing the entire view definition, e.g.

ALTER VIEW Sales.OrderTotalsByYear
   WITH SCHEMABINDING
AS
SELECT ...
FROM ...
GO

Question 18

How do you drop a view?

Answer 18

You can use the DROP VIEW command.

DROP VIEW Sales.OrderTotalsByYear.

Question 19

What does the OBJECT_ID function do?

Answer 19

Returns the database object identification number of a schema-scoped object.

The ‘V’ parameter in the OBJECT_ID function looks for views in the current database and then returns an object id if a view with that name is found.

You can do something like:

IF OBJECT_ID(‘Sales.OrderTotalsByYear’, ‘V’) IS NOT NULL
DROP VIEW …
GO

CREATE VIEW…

Question 20

What are the restrictions for modifying data through a view?

Answer 20

(1) DML statements must reference exactly one table at a time no matter how many tables the view references, (2) The view columns must directly reference table columns and not be expressions or functions (no aggregates like SUM, MAX, MIN), (3) You cannot modify a view column that is computed from a UNION/UNION ALL, CROSS JOIN, EXCEPT, or INTERSECT, (4) You cannot modify a view resulting from a grouping, e.g. DISTINCT or GROUP BY, (5) You cannot modify a view that has a TOP operator or OFFSET FETCH in the SELECT statement.

If the view does not meet all of the requirements above and you still need to update against it, consider an INSTEAD OF trigger on the view to update the underlying tables.

Question 21

What does DML stand for?

Answer 21

DML stands for “Data Manipulation Language”. It is used to retrieve, store, modify, delete, insert, and update data in a database.

Question 22

What are partitioned views?

Answer 22

If you are not able to use table partitioning, you can manually partition your tables and create a view that applies a UNION statement across those tables. If the tables are local (one database or at least one instance), it is called a partitioned view or a local partitioned view. If the tables are spread across multiple SQL Server instances, the view is called a distributed partitioned view.

If you want the optimizer to take advantage of your partitioning and resolved queries efficiently using partition elimination, your view must satisfy a number of important conditions.

Question 23

How can you explore view metadata in T-SQL?

Answer 23

You can query the sys.views catalog view, e.g.

SELECT * FROM sys.views.

You can also query the INFORMATION_SCHEMA.TABLES system view, e.g.

SELECT * FROM INFORMATION_SCHEMA.TABLES

Question 24

What are inline table-valued functions?

Answer 24

Inline table valued functions simulate a parameterized view.

CREATE FUNCTION Sales.fn_OrderTotalsByYear()
RETURNS TABLE
AS
RETURN
(
   SELECT ... FROM ...
);
GO

Question 25

What are the restrictions of table valued functions?

Answer 25

In an inline table-valued function, the body of the function can only be a SELECT statement; you cannot declare variables and perform other T-SQL commands as you can with scalar UDFs and multistatement table valued functions.

Question 26

What options does the inline function provide?

Answer 26

Similar to a view, you can use WITH ENCRYPTION to make it difficult for users to discover the SELECT text of the function. You can also add WITH SCHEMABINDING which binds the function to the underlying table schemas. The referenced objects cannot be altered unless the function is dropped.

Question 27

What are synonyms?

Answer 27

Synonyms are names stored in a database that can be used as substitutes for other object names. These names are also scoped to the database, and qualified with a schema name.

Question 28

How do you create a synonym?

Answer 28

Assign a synonym name and specify the name of the database object it will be assigned to, e.g.

CREATE SYNONYM dbo.Categories FOR Production.Categories.

The synonym can then be queried (in this case without the schema since it’s been synonym’ed to dbo):

SELECT * FROM Categories

Question 29

Can synonyms refer to other synonyms?

Answer 29

Synonyms cannot refer to other synonyms. You cannot “chain” synonyms.

Question 30

What types of objects can a synonym refer to?

Answer 30

(1) Tables (including temp tables), (2) Views, (3) User-defined functions (scalar, table-valued, inline), (4) Stored procedures, (5) CLR assemblies.

Question 31

What does DDL stand for?

Answer 31

DDL stands for “Data Definition Language”. These statements are used to create and modify the structure of objects in the database, e.g. CREATE, ALTER, DROP.

Question 32

Can you reference a synonym in a DDL statement such as ALTER?

Answer 32

No. You cannot reference a synonym in a DDL statement. You must reference the base object instead.

Question 33

How do you drop a synonym?

Answer 33

You can drop a synonym using the DROP SYNONYM statement, e.g. DROP SYNONYM dbo.Categories.

Question 34

Is there an ALTER SYNONYM statement?

Answer 34

No. There is no ALTER SYNONYM statement. You must drop it and re-create it to make any changes.

Question 35

Does the object that a synonym points to need to actually exist?

Answer 35

No. The object does not need to actually exist, and SQL Server doesn’t test it. This is because of the late binding behavior of synonyms. The synonym is actually checked for existence when you use it in a T-SQL statement.

Question 36

How can synonyms simplify queries?

Answer 36

Synonyms can refer to objects in other databases in addition to objects referenced by linked servers. That makes it possible to drastically simplify queries in your database and potentially remove the need for 3/4-part references.

ReportsDB.Sales.Reports. to Sales.Reports.

Question 37

What happens when you drop an object referenced by a synonym.

Answer 37

If you drop an object in the database, it will be dropped whether or not a synonym references it. Any synonyms referencing the object are effectively orphans; they fail to work when someone tries to use them.

Question 38

What permission do you need to create a synonym?

Answer 38

To create a synonym, you must have the CREATE SYNONYM permission which inherits from the CONTROL SERVER permission. After you’ve created a synonym, you can grant other users permissions such as EXECUTE or SELECT to the synonym depending on the type of object the synonym stands for.

Question 39

Does a synonym store T-SQL or any data?

Answer 39

No, a synonym is just a name. All that is stored with a synonym is the object it refers to.

Question 40

Does a synonym expose metadata?

Answer 40

A synonym does not expose the metadata of the underlying table or view it points to. This could be seen as an advantage or disadvantage depending on the context.

Question 41

What does the SCHEMA_NAME function do?

Answer 41

You can use the SCHEMA_NAME function to display the schema name without having to join to sys.schemas table, e.g.

SELECT SCHEMA_NAME(schema_id) as schemaname