Selecting Data from a Single View | Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step

You can think of a view as a stored query that encapsulates the complexity of a query and presents the desired data to users or applications. When you create a view, SQL Server 2005 only stores the definition for the view. No data is stored unless the view will be indexed. Therefore, a view is basically a SELECT statement stored in a database for later use. Once a view is created, you can obtain results from the SELECT statement by querying the view instead of running the complex SELECT statement against the base tables.

Creating a View

You can create a view graphically in SQL Server Management Studio or by using the CREATE VIEW statement.

Creating a View in SQL Server Management Studio

Start SQL Server Management Studio by selecting Programs Microsoft SQL Server 2005 SQL Server Management Studio from the Start menu. Connect to the appropriate SQL Server instance.
From Object Explorer, expand the AdventureWorks database.
Right-click the Views folder and select New View from the context menu.
The Add Table dialog box appears. Choose the Department, Employee, and EmployeeDepartmentHistory tables. Click Add, and then click Close.
Select the Name column on the Department table. Select the NationalIDNumber and Title columns from the Employee table.
Click outside of the tables you added and select Properties Window from the View menu. In the Properties window, choose HumanResources in the Schema property.
Click the Save View toolbar button and enter vTest as the view name.

Creating a View Using T-SQL

The following sample code creates a view called ProductSales in the Sales schema that provides information about the units sold for each product. You can access this sample code from \Ch08\SampleCh0801.sql.

 USE AdventureWorks GO CREATE VIEW Sales.ProductSales AS SELECT     Production.Product.Name AS Product,            SUM(Sales.SalesOrderDetail.OrderQty) AS ProductSales FROM       Production.Product INNER JOIN                Sales.SalesOrderDetail ON Production.Product.ProductID =                    Sales.SalesOrderDetail.ProductID INNER JOIN                        Sales.SalesOrderHeader ON                            Sales.SalesOrderDetail.SalesOrderID =                                Sales.SalesOrderHeader.SalesOrderID GROUP BY Production.Product.Name

In SQL Server 2005, a view can contain a maximum of 1,024 columns. You can create views only in the local database.

Tip	Using the ORDER BY clause inside a view does not guarantee that the results will be ordered when you query the view.

Obtaining Information about Views

Once a view is created in a database, you can obtain information about the view from the sys.views catalog view. The following is a sample query of the sys.views catalog view. You can access this sample code from \Ch08\SampleCh0802.sql.

 USE AdventureWorks GO SELECT * FROM sys.views

You can also view the original code by querying the sys.sql_modules catalog view. The following example provides information about the definition for the AdventureWorks views. You can access this sample code from \Ch08\SampleCh0803.sql.

 USE AdventureWorks GO SELECT name, definition FROM sys.sql_modules INNER JOIN sys.views     ON sys.sql_modules.object_id=sys.views.object_id

Before you modify, rename, or delete a view, it is important to know what objects depend on the view. For example, changing the name or definition of a view can cause dependent objects to fail if the dependent objects are not updated to reflect the changes that have been made to the view. The stored procedure sp_depends allows you to determine the identity of the base objects on which the view depends. The following code describes how to obtain information about the objects used in the Sales.ProductSales view. You can access this sample code from \Ch08\SampleCh0804.sql.

 USE AdventureWorks GO EXECUTE sp_depends 'Sales.ProductSales'

If you execute the code above, you should receive output resembling that found in Table 8-1.

Table 8-1: Sample Output for sp_depends
Name	Type	Updated	Selected	Column
Sales.SalesOrderDetail	user table	no	yes	SalesOrderID
Sales.SalesOrderDetail	user table	no	yes	OrderQty
Sales.SalesOrderDetail	user table	no	yes	ProductID
Sales.SalesOrderHeader	user table	no	yes	SalesOrderID
Production.Product	user table	no	yes	ProductID
Production.Product	user table	no	yes	Name

You can also obtain dependency information graphically within SQL Server Management Studio.

Accessing Dependency Information

Right-click the desired view and choose View Dependencies from the context menu.
Select the Objects On Which [ view ] Depends option.

You can now navigate through the dependencies tree, obtaining information about tables, columns, indexes, and user-defined types.

You can query a view using a SELECT statement in the same way that you query a table. The following sample code demonstrates how to query the Sales.ProductSales view. You can access this sample code from \Ch08\SampleCh0805.sql.

 USE AdventureWorks GO SELECT * FROM Sales.ProductSales

Creating View Options

SQL Server 2005 allows you to configure views using attributes in the CREATE VIEW statement. The following list provides a description of various view attributes.

Encryption The encryption attribute encrypts the entries within sys.sql_modules that contain the text of the view definition. Once a view has been encrypted, it is no longer possible to see the original view definition. Therefore, you should document all view statements and be careful when encrypting them. The following example creates an encrypted view. You can access this sample code from \Ch08\SampleCh0806.sql.
```
 USE AdventureWorks GO CREATE VIEW dbo.TestEncryption WITH ENCRYPTION AS SELECT * FROM Production.Product 
```
Once you have created the encrypted view, try to obtain the view description from the sys.sql_modules catalog view using the following SELECT statement. You can access this sample code from \Ch08\SampleCh0807.sql.
```
 SELECT name, definition FROM sys.sql_modules INNER JOIN sys.views    ON sys.sql_modules.object_id=sys.views.object_id WHERE Name = 'TestEncryption' 
```
You will receive a NULL description due to the view encryption, even though you are the view owner.
Schemabinding The schemabinding attribute allows you to bind the view definition with the schema of the base tables queried in the view. When you specify the schema-binding attribute, base tables cannot be modified if the change affects the view definition. To use the schemabinding attribute, you must query the base objects with the two-part name (schema.table) for referenced objects. Schemabinding can be used if the view queries local database objects only. If you do not specify the schemabinding option, you must execute the sp_refreshview stored procedure when a base table is modified.
View_Metadata Client-side application program interfaces (APIs) have the option of requesting a browse-mode query to enable client-side APIs to implement updatable client-side cursors . This browse-mode metadata includes information about the base tables. You must specify the view_metadata option to support the delivery of browse-mode metadata.
Check By specifying the check option in the CREATE VIEW statement, the database engine forces all modification statements executed against the view to follow the criteria within the SELECT statement. When you modify a row through a view, the check option ensures that the data is visible after the modification is committed.

Modifying a View Definition

As with other database objects, you can modify a view definition through the SQL Server Management Studio graphical interface or you can use the ALTER VIEW statement. If you want to modify a view, you should write the entire statement again even though you only want to modify one view attribute. This step is important because, when you have encrypted a view, you cannot see the original view definition once it has been encrypted. The following example demonstrates how to add the view_metadata attribute to the dbo.TestEncryption view. You can access this sample code from \Ch08\SampleCh0808.sql.

 USE AdventureWorks GO ALTER VIEW dbo.TestEncryption WITH ENCRYPTION,VIEW_METADATA AS SELECT * FROM Production.Product

You can also remove a view with the DROP VIEW statement as shown in the following example. You can access this sample code from \Ch08\SampleCh0809.sql.

 USE AdventureWorks GO DROP VIEW dbo.TestEncryption

Important

Before you modify or drop a view, you should check for view dependencies. Dependencies could raise errors in the alter and drop operations.

Updating Data through a View

Although the primary reason for creating a view is to obtain information for queries and reports , you can execute INSERT, UPDATE, and DELETE sentences over the base tables through a view. To perform these actions, the view must meet the following conditions.

Modifications can only reference columns from one base table.
Columns cannot be derived.
Modified columns cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
The TOP clause cannot be specified in the view definition.

The following example demonstrates how to update the Person.Address.City column through the vEmployeeAddress view. You can access this sample code from \Ch08\SampleCh0810.sql.

 USE AdventureWorks GO CREATE VIEW HumanResources.vEmployeeAddress AS SELECT NationalIDNumber,LoginID,Title,AddressLine1,City,PostalCode FROM HumanResources.Employee INNER JOIN HumanResources.EmployeeAddress     ON HumanResources.Employee.EmployeeID =     HumanResources.EmployeeAddress.EmployeeID     INNER JOIN Person.Address    ON Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID GO UPDATE HumanResources.vEmployeeAddress   SET City = 'Everett' WHERE NationalIDNumber= 14417807

Partitioned Views

SQL Server 2005 allows you to create views that reference tables from different databases and servers. You can partition your data among several servers and then create views to consolidate the information. Suppose that you are working with a client table containing millions of rows. You can partition the table horizontally and filter clients by location. You could create a USA_Customers table, an EMEA_Customers table, and so on, and each table could be stored in a different SQL Server instance. You could then create a view that combined all of these tables and manage modifications to the data through INSTEAD OF triggers. INSTEAD OF triggers will be discussed in Chapter 12, Updating Data from Microsoft SQL Server 2005. This process is known as partitioned views and is a way to scale out your SQL Server environment.

Note	To query data from a remote instance, you should create a linked server before accessing the remote instance.

The following example works with a linked server named ISAN that connects to another SQL Server instance with a TestPartitionedViews database. This database also contains an EMEA_Customers table. The following Transact-SQL (T-SQL) sentences demonstrate how to create the local resources and partitioned views. You can access this sample code from \Ch08\SampleCh0811.sql.

 CREATE DATABASE TestPartitionedViews GO USE TestPartitionedViews GO CREATE TABLE USA_Customers (CustomerID int, CustomerName varchar(200), Region varchar(20)) GO CREATE VIEW Customers AS SELECT * FROM USA_Customers UNION SELECT * FROM ISAN.TestPartitionedViews.dbo.EMEA_Customers GO

You could then create an INSTEAD OF trigger to support modifications to the base tables through the Customers view.

Note	Partitioned views that access data in tables from the local server are supported in SQL Server 2005 for backward compatibility only. In SQL Server 2005, you should use partitioned tables as the preferred partitioning approach.