Views are virtual tables that represent the result
set of a select statement from one or more tables or other views. In
most cases, that is unless a view is indexed, a view is basically a
predefined query that is stored and executed whenever the view is
referenced in a query.
You can use a view to limit user
access to sensitive information in the underlying table. For example,
you can create a view that only includes a subset of columns in a table
and give the user access to the view instead of the underlying table.
You can also use a WHERE clause to limit the rows of data that are returned, ensuring the user can see only specific rows.
There are a few limitations you need to be aware of when creating a view. A SELECT statement in a view cannot include any of the following:
A COMPUTE or COMPUTE BY clause.
An ORDER BY clause without also including the TOP clause. The workaround for this is to select the top 100 percent, as in the following query: SELECT TOP 100 PERCENT FROM <table> ORDER BY <columns>.
The INTO keyword used to create a new table.
The OPTION clause.
A reference to a temporary table or table variable.
The example in Listing 1
creates a view that shows the name and department description of all
the employees in a single department. As you can see in the example, we
are able to use the view to hide the employee's social security number.
We are also able to provide more user-friendly column names by using an
alias. The WITH ENCRYPTION option
prevents the view definition from being displayed. You should be
careful when using the encryption option because you will not be able to
retrieve the definition from the database if you need it.
Example 1. Sample Code Used to Create an Encrypted View
USE AdventureWorks2008 GO
CREATE TABLE Employee (EmpID int NOT NULL CONSTRAINT PK_EMP PRIMARY KEY CLUSTERED, EmpFirstName Varchar(50), EmpLastName Varchar(50), EmpSSN Varchar(9), DepartmentID int) GO
CREATE TABLE Department (DepartmentID int NOT NULL CONSTRAINT PK_DEPT PRIMARY KEY CLUSTERED, DepartmentDscr Varchar(50)) GO
CREATE VIEW vMarketingEmployees WITH ENCRYPTION AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 1
|
Let's say you found out that the Marketing department is actually DepartmentID 2 instead of 1. You can use the ALTER VIEW statement shown in Listing 2
to make the changes. While making the changes, you also decide to
remove the encryption option. All you have to do to remove the
encryption is not to specify the option when running the ALTER VIEW statement.
Example 2. Syntax Used to Alter an Existing View
USE AdventureWorks2008 GO
ALTER VIEW vMarketingEmployees AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 2
|
To remove a view from the database, all you have to do is issue the DROP VIEW statement followed by the view name, as shown in Listing 3.
Example 3. Syntax to Remove a View from the Database
DROP VIEW vMarketingEmployees
|
1. Partitioned Views
Distributed partitioned
views are those that reference data across multiple servers and combine
the data to the user as a single result set. You can use distributed
partitioned views to form a federation of database servers, which are
separately managed servers used to spread the processing load for a
single application across multiple servers. Listing 4 shows an example of a distributed partitioned view.
NOTE
You can create a
local partitioned view by referencing only tables on the same server;
however, creating partitioned tables is the preferred method for locally
partitioning data.
Example 4. Common Syntax Used in a Distributed Partitioned View
CREATE VIEW vDistributedSample AS SELECT col1, col2, col3 FROM Server1.DBName.dbo.TableName UNION ALL SELECT col1, col2, col3 FROM Server2. DBName.dbo.TableName UNION ALL SELECT col1, col2, col3 FROM Server3.DBName.dbo.TableName
|
2. Updateable Views
You can use a view to insert, update, and delete data in the underlying tables as long as certain conditions are met:
All the columns being modified must be in the same base table.
The
columns must also directly reference the base table; you cannot modify
computed columns or columns that are derived from or affected by
aggregate functions.
If the WITH CHECK option is specified, as shown in Listing 5, the view cannot be updated in any way that would cause the updated record to disappear from the result set.
For example, given the view created in Listing 5, you could not run an UPDATE statement to set the DepartmentID = 2.
Example 5. Syntax to Create an Updatable View Using the WITH CHECK Option
USE AdventureWorks2008 GO
--Drop the view if it currently exists IF OBJECT_ID('dbo.vMarketingEmployees', 'V') IS NOT NULL DROP VIEW dbo.vMarketingEmployees;
GO
--Create a view using the WITH CHECK option CREATE VIEW vMarketingEmployees AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentID, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 1 WITH CHECK OPTION
|
If you look at Figure 1, you can see that trying to change DepartmentID fails, since it will violate the CHECK OPTION constraint, but changing Department is successful.
3. Indexed Views
If you have a process-intensive
view that is run often, you can improve performance and reduce the
processing time needed to execute the view by creating an index on the
view. The first index you create on a view must be a unique clustered
index, which causes the result set of the view to be stored in the
database. Thereafter, you can create nonclustered indexes.
Listing 6 shows an example of creating an indexed view. Notice the WITH SCHEMABINDING clause in the CREATE VIEW
statement. If you plan to create an index on a view, then you must
first create the view using that clause, which prevents any changes to
the underlying tables referenced by the view that would affect the view
definition. An indexed view is essentially a stored result set returned
by the view, so SQL Server does not have to process the query each time
the view is referenced. By creating the view WITH SCHEMABINDING, SQL Server can ensure that no underlying changes to the data will invalidate the stored results.
There are several other
requirements that must be met in order to create an indexed view. For
the specific requirements, refer to the topic "Creating Indexed Views"
in SQL Server Books Online.
Example 6. Syntax to Create an Indexed View
USE AdventureWorks2008 GO
CREATE VIEW vEmployees WITH SCHEMABINDING AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentID, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID GO --Create an index on the view CREATE UNIQUE CLUSTERED INDEX IDX_vEmployee_Dept ON vEmployees (DepartmentID); GO
|
Indexed views are best
suited for situations where the underlying data is rarely updated;
because as the data is updated in the base tables, the data must also be
updated in the indexed view to reflect the changes. If the data is
frequently updated, maintaining the index could actually lead to
performance issues instead of performance gains. There are many caveats
to using indexed views that could lead to an administrative headache, so
you shouldn't go around creating indexes on all your views. However,
there are certain situations, especially in a data warehouse
environment, where indexed views can provide an enormous performance
benefit.
You can create an indexed
view in any edition of SQL Server 2008, but you receive some extra
benefits when you are running the Enterprise Edition. In the Enterprise
Edition, the query optimizer can automatically take advantage of an
index created on a view, even if the view is not specifically referenced
in a query.