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
WITH SCHEMABINDING
AS
SELECT …
FROM …
GO
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)
AS
SELECT …
FROM …
GO
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 WITH SCHEMABINDING AS SELECT ... FROM ... GO
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 …
GO
CREATE 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() RETURNS TABLE AS RETURN ( SELECT ... FROM ... ); GO
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.
What types of objects can a synonym refer to?
(1) Tables (including temp tables), (2) Views, (3) User-defined functions (scalar, table-valued, inline), (4) Stored procedures, (5) CLR assemblies.
What does DDL stand for?
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.
Can you reference a synonym in a DDL statement such as ALTER?
No. You cannot reference a synonym in a DDL statement. You must reference the base object instead.
How do you drop a synonym?
You can drop a synonym using the DROP SYNONYM statement, e.g. DROP SYNONYM dbo.Categories.
Is there an ALTER SYNONYM statement?
No. There is no ALTER SYNONYM statement. You must drop it and re-create it to make any changes.
Does the object that a synonym points to need to actually exist?
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.
How can synonyms simplify queries?
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.
What happens when you drop an object referenced by a synonym.
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.
What permission do you need to create a synonym?
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.
Does a synonym store T-SQL or any data?
No, a synonym is just a name. All that is stored with a synonym is the object it refers to.
Does a synonym expose metadata?
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.
What does the SCHEMA_NAME function do?
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
