A view is a named SELECT query. Unlike ordinary tables, a view is not part of the physical schema; it is a dynamic, virtual table that collect data from base tables (or views). Effective use of views in database design helps improve performance and manageability. The data that is accessible through a view is not stored in the database as a distinct object, except of indexed views. (Indexed views are described later.) Queries against views are written in the same way that queries are written against tables.
Views can filter the base tables vertically, horizontally, or both. Vertical filtering limits the columns that the view returns. Horizontal filtering limits the rows that the view returns. There are four basic types of view:
- Standard Views. Standard views combine data from one or more base tables (or views) and return particular columns and rows. Any computations, such as joins or aggregations, are performed during query execution for each query that references the view.
- System Views. SQL Server provides system views, which show details of the system catalog of the state of SQL Server. In SQL Server 2005, Dynamic management views (DMVs) were introduced and enhanced in every edition since then. DMVs provide dynamic information about the state of SQL Server, such as information about the current sessions or the queries those sessions are executing.
- Indexed Views. Indexed views materialize the view through the creation of a clustered index on the view. This is usually done to improve query performance and will consume disk space. You can avoid complex joins or lengthy aggregations at execution time by pre-calculating results. Indexed views are discussed later.
- Partitioned Views. Partitioned views unite data from multiple tables. One column in the view defines which underlying table stores the data and CHECK constraints on the table enforce it. Distributed partitioned views are formed when a UNION operation combines the tables on separate instances of SQL Server.
Advantages of Views
Views are generally used to focus, simplify, and customize the tables in the database for each user. Views can enable users to focus on a subset of data that is relevant to them. You can also use views as security mechanisms by allowing users to access data through the view, without granting them permissions to access the underlying base tables directly.
Creating a view enables you to isolate the data for export functions in external applications. Views also provide a backward-compatible interface to simulate a table that previously existed, but whose schema has changed. For example, if a Customer table has been split into two tables, OnlineCustomer and InstoreCustomer, a Customer view can cover the two new tables to make it appear that the Customer table still exists. This would enable applications to query the data without requiring any changes.
Reporting applications often need to execute complex queries to retrieve the report data. Rather than embedding this logic in the reporting application, a view can supply the data that the reporting application requires in a much simpler format.
SQL Server provides information about its configuration through a series of system views. These views also provide metadata that describes both the objects that you create in the database and the objects that SQL Server provides. There are four types of System Views:
- Catalog Views. These views primarily retrieve metadata about tables and other objects in databases.
- Compatibility Views. Earlier versions of SQL Server provided a set of virtual tables that were exposed as system views. For backward compatibility, a set of “compatibility” views enable applications that used the virtual tables to continue to work. However, these views are deprecated and you should not use them for new development work.
- Information Schema Views. Each database engine vendor uses different methods of storing and accessing metadata. The International Organization for Standardization (ISO) has standards for Structured Query Language (SQL). So, a standard mechanism was designed; This interface is provided by the views in the INFORMATION_SCHEMA schema. The most commonly INFORMATION_SCHEMA views are:
|INFORMATION_SCHEMA.CHECK_CONSTRAINTS||Returns one row for each CHECK constraint on an object that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.COLUMNS||Returns one row for each column of an object that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.PARAMETERS||Returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS||Returns one row for each FOREIGN KEY constraint on a table that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.ROUTINE_COLUMNS||Returns one row for each column returned by the table-valued functions that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.ROUTINES||Returns one row for each stored procedure and function that can be accessed by the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL.|
|INFORMATION_SCHEMA.TABLE_CONSTRAINTS||Returns one row for each table constraint on an object that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.TABLE_PRIVILEGES||Returns one row for each table privilege that is granted to or granted by the current user in the current database.|
|INFORMATION_SCHEMA.TABLES||Returns one row for each table that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.VIEW_COLUMN_USAGE||Returns one row for each column of a view that can be accessed by the current user in the current database.|
|INFORMATION_SCHEMA.VIEW_TABLE_USAGE||Returns one row for each table that can be accessed by the current user in the current database that is used in a view.|
|INFORMATION_SCHEMA.VIEWS||Returns one row for views that can be accessed by the current user in the current database.|
- Dynamic Management Views. DMVs provide a relational method for querying the internal state of a SQL Server instance Which are discussed in the next topic.
Dynamic Management Views
SQL Server 2005 introduced the concept of dynamic management objects (DMOs). These objects include dynamic management views (DMVs) and dynamic management functions (DMFs). Each object is used to return internal state information of SQL Server. DMOs have a sys.dm_ prefix. The difference between DMVs and DMFs is that DMFs have parameters passed to them.
DMOs enable user to view and monitor the internal health and performance of a server along with its configuration. They also have an important role in assisting with troubleshooting problems (such as blocking issues) and with performance tuning.
To create a view, the database owner must grant you permission to do so. Views can be based on other views instead of being based on the underlying tables. Up to 32 levels of nesting are permitted. You should take care when nesting views deeply because it can become difficult to understand the complexity of the underlying code and to troubleshoot performance problems.
Views have no natural output order. Queries that access the views should specify the order for the results. You can use the ORDER BY clause in a view, but only with a clause such as the TOP clause.
If you specify the WITH SCHEMABINDING option, the underlying tables cannot be changed. To create the indexed views, you also must use the WITH SCHEMABINDING option.
Expressions that are returned as columns need to be aliased. It is also common to define column aliases in the SELECT statement within the view definition, but you can also provide a column list after the name of the view.
The following code example shows column aliases after the name of the view:
CREATE VIEW HumanResources.EmployeeList (EmployeeID, FamilyName, GivenName) AS SELECT HRE.BusinessEntityID, PP.LastName, PP.FirstName FROM HumanResources.Employee HRE LEFT JOIN Person.Person PP ON HRE.BusinessEntityID = PP.BusinessEntityID;
Dropping a view removes the definition of the view and all permissions that are associated with it. Even if a view is re-created with exactly the same name and definition, permissions that were formerly associated with the view are removed.
The DROP VIEW statement enables you to drop multiple views via a comma-delimited list, as shown in the following example:
DROP VIEW Sales.vSalesPerson, Sales.vSalesPersonSalesByFiscalYears; GO
After a view is defined, you can modify its definition without dropping and re-creating the view. The main advantage of using ALTER VIEW statement is that any permissions that are associated with the view are retained. Altering a view also involves less code than dropping and re-creating a view.
The following example display how to modify the view’s definition:
ALTER VIEW HumanResources.EmployeeList ([Employee ID], [Full Name], [Job Title]) AS SELECT HRE.BusinessEntityID, COALESCE (PP.FirstName + ' ' + PP.LastName, ''), HRE.JobTitle FROM HumanResources.Employee HRE LEFT JOIN Person.Person PP ON HRE.BusinessEntityID = PP.BusinessEntityID; GO
Ownership Chains and Views
One of the key reasons for using views is to provide a layer of security abstraction. So, that access is given to views and not to the underlying tables. For this mechanism to function correctly, an unbroken ownership chain must exist.
When SQL Server executes a query, it checks the permissions of the executing principal on every object that is touched on the way. Therefore, if you run a select statement against a view that selects from a table, you need to have access to both the view and the underlying table. However, before checking the permissions on the underlying securable, SQL Server checks if the accessing object (the view) and the accessed securable (the table) have the same owner. If they have the same owner, permission checking on the accessed securable is completely skipped and access is granted. Otherwise, if the user does not have permission on the accessed securable, access will be denied.
Sources of Information About Views
You may need to see the definition of the view to understand how its data is derived from the source tables or to see the data that the view defines. By using the following Transact-SQL sources, you can reach the information about views and their definition:
/*List of views in database*/ SELECT * FROM sys.views; GO /*Query the definition of an object*/ SELECT OBJECT_DEFINITION(OBJECT_ID('HumanResources.EmployeeList')); GO /*Overall dependency*/ SELECT * FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID('HumanResources.EmployeeList'); GO /*Column-level dependency*/ SELECT * FROM sys.dm_sql_referenced_entities('HumanResources.EmployeeList','OBJECT'); GO
SQL Server Management Studio also provides access to a list of views in Object Explorer. This includes both system views and user-defined views. By expanding the view nodes in Object Explorer, you can see details of the columns, triggers, indexes, and statistics on the views.
It is possible to update data in the base tables by updating a view. Updates on views cannot affect columns from more than one base table. It is not possible to update columns contain aggregated values or any columns that are involved in grouping operations such as GROUP BY, HAVING, or DISTINCT.
It is also possible to modify a row in a view that the updated rows would not belong to the view. For example, a view that selected rows where the Country column contained the value US. Then, you could update the rows and set the Country column to the value UK. If the view was queried again, the row would seem to have vanished. To avoid this happening, you can specify the WITH CHECK OPTION clause when you define the view. It will check any rows that had been modified during data modifications would still be returned by the same view.
Encrypting View Definitions
Database developers often want to protect the definitions of their database objects. By adding the WITH ENCRYPTION clause when you are defining or altering a view, you would be able to obfuscating the definition of a view. It is important to keep copies of the source code for views when the view is created by using the WITH ENCRYPTION clause.
Encrypted code makes it harder to perform query tracing and problem diagnosis.
The encryption is not very strong. Many third-party tools exist that can decrypt the source code. you should not depend on this to protect your critical property.
CREATE VIEW HumanResources.EmployeeList WITH ENCRYPTION AS SELECT HRE.BusinessEntityID EmployeeID, PP.LastName FamilyName, PP.FirstName GivenName FROM HumanResources.Employee HRE LEFT JOIN Person.Person PP ON HRE.BusinessEntityID = PP.BusinessEntityID;
Partitioned views enable you to split the data in a large table into smaller tables. The data is partitioned between the tables based on ranges of data values in one of the columns.
Data ranges for each table in a partitioned view are defined in a CHECK constraint that is specified on the partitioning column. A UNION ALL statement combine selects of all of the tables into a single result set.
When you perform an INSERT operation on the view, SQL Server places the row into the correct underlying table from the view.
Partitioned views can be local or distributed. In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. In a distributed partitioned view, at least one of the participating tables resides on a different server.
It is possible to create clustered indexes over views. Indexed view is a view that has a clustered index. Indexed views can have a profound (positive) impact on the performance of queries in particular position.
They are also useful for maintaining pre-calculated aggregates or joins. When updates to the underlying data are made, SQL Server automatically makes updates to the data that is stored in the indexed view.
Indexed views have a negative impact on the performance of INSERT, DELETE, and UPDATE operations on the underlying tables, but they can also have a positive impact on the performance of SELECT queries on the view. They are most useful for data that is regularly selected, but much less frequently updated.
WITH SCHEMABINDING is an option that the view must have been created with before creating an index on the view. The WITH SCHEMABINDING option prevents changes to the schema and definition of the underlying tables while the view exists.
Now you understand why views are important and know how to create them. It is important to understand the potential performance impacts of using views. Consider that standard views do not appear in execution plans for queries because the views are not accessed. Execution Plans just can see the underlying objects that they reference. You should also avoid using SELECT * in a view definition. You should consider to effect and potential disadvantages of nesting views. Finally, you know how to combine the data from multiple tables into a single view, even if those tables are on different servers.
Hope this post was informative, please share it with others if you think it worth to read. Stay tuned to learn more about SQL Server.