Flashcards in Chapter 9 Designing and Creating Views, Inline Functions, and Synonyms Deck (41)
What are views?
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.
How do you create a view?
You name the view and then specify the SELECT statement that will constitute the view.
CREATE VIEW Sales.OrderTotalsByYear
Does the CREATE VIEW statement have to come first when you create a view?
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.
Can you specify the set of output columns following the view name when creating a view?
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)
What does the WITH ENCRYPTION option do?
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.
What does the WITH SCHEMABINDING option do?
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.
What does the WITH VIEW_METADATA option do?
When specified, returns the metadata of the view instead of the base table.
What does the WITH CHECK OPTION option do?
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".
Do view names have to be unique?
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.
What are some examples of schema scoped objects?
(1) Views, (2) Tables, (3) Stored procedures, (4) Functions, (5) Synonyms
What are some restrictions of views?
(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.
Are the results of a view ever ordered?
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.
Why does a view only allows to have a single SELECT statement?
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.
Is data stored with a view?
No. Normally, a view is just a definition by a SELECT statement of how the results should be built. No data is stored.
What is an indexed view?
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.
What happens when you query a view?
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.
How do you alter a view?
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
How do you drop a view?
You can use the DROP VIEW command.
DROP VIEW Sales.OrderTotalsByYear.
What does the OBJECT_ID function do?
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 ...
What are the restrictions for modifying data through a view?
(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.
What does DML stand for?
DML stands for "Data Manipulation Language". It is used to retrieve, store, modify, delete, insert, and update data in a database.
What are partitioned views?
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.
How can you explore view metadata in T-SQL?
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
What are inline table-valued functions?
Inline table valued functions simulate a parameterized view.
CREATE FUNCTION Sales.fn_OrderTotalsByYear()
SELECT ... FROM ...
What are the restrictions of table valued functions?
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.
What options does the inline function provide?
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.
What are synonyms?
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.
How do you create a synonym?
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
Can synonyms refer to other synonyms?
Synonyms cannot refer to other synonyms. You cannot "chain" synonyms.