Creating and Managing Views in SQL Server 2008 : Definition of Views & Using Views

Definition of Views

Views are a logical way of viewing data in the underlying physical tables. They are tied to a SELECT statement that retrieves data from one or more tables or views in the same database or a different database. In most cases, there is no physical storage of data associated with the view, and the SELECT that is associated with the view is run dynamically whenever the view is referenced.

The following T-SQL statement can be used to create a simple view in the Adventureworks2008 database:

CREATE VIEW [dbo].[vw_CustomerAddress]
AS
SELECT     Sales.Customer.CustomerID, Sales.Customer.AccountNumber,
 Person.Address.AddressLine1,
 Person.Address.StateProvinceID, Person.Address.City,
 Person.Address.PostalCode
FROM Sales.Customer
 INNER JOIN Person.Person
  ON Sales.Customer.PersonID = Person.Person.BusinessEntityID
 INNER JOIN Person.BusinessEntityAddress
  ON Person.Person.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
  INNER JOIN Person.Address   ON Person.BusinessEntityAddress.AddressID =
Person.Address.AddressID


					  

The vw_CustomerAddress view in this example selects from four different tables in the Adventureworks2008 database: Sales.Customer, Person.Person, Person.Business EntityAddress, and Person.Address. After the view is created, it can be used in the FROM clause of another SELECT statement. The following data retrieval example uses the newly created view:

select c.AccountNumber, s.OrderDate, c.city , c.StateProvinceId
  from vw_CustomerAddress c
   INNER JOIN Sales.SalesOrderHeader s
    ON c.CustomerID = s.CustomerID
 WHERE StateProvinceId = 14
  AND s.OrderDate = '9/21/01'
 ORDER BY c.city


AccountNumber OrderDate                city                StateProvinceId
AW00020060    2001-09-21 00:00:00.000  Runcorn             14
AW00011333    2001-09-21 00:00:00.000  Newcastle upon Tyne 14

You can see from the sample SELECT that the view is treated much like a table that is referenced in a SELECT statement. The view can be joined to other tables, individual columns from the view can be selected, and those columns can be included in the ORDER BY clause. All the retrieval is done dynamically when the view is referenced, and the underlying tables that are part of the view definition are implicitly accessed, without the need to know the underlying structure of the view.

Using Views

Views are useful in many scenarios. Some of the most common scenarios include the following:

• Simplifying data manipulation

• Focusing on specific data

• Abstracting data

• Controlling access to data

Simplifying Data Manipulation

Views can be used to simplify data access. Common queries that utilize complex joins, UNION queries, and more involved SQL can be defined as views. This minimizes the amount of complex code that must be written or rewritten and provides a simple way of organizing your common data access.

SQL Server 2008 comes with a set of system views that demonstrate the views’ capability to mask complex queries and simplify data manipulation. These system views include catalog views, information schema views, and compatibility views. In many cases, the definition of these views is hidden, but some of them can be analyzed using the sp_helptext system procedure. For example, sys.triggers, a catalog view defined in SQL Server 2008, has the following definition associated with it:

CREATE VIEW sys.triggers AS
   SELECT o.name,
    object_id = o.id,
    parent_class = o.pclass,
    parent_class_desc = pc.name,
    parent_id = o.pid,
    type = o.type,
    type_desc = n.name,
    create_date = o.created,
    modify_date = o.modified,
    is_ms_shipped = sysconv(bit, o.status & 1),       — OBJALL_MSSHIPPED
    is_disabled = sysconv(bit, o.status & 256),       — OBJTRG_DISABLED
    is_not_for_replication = sysconv(bit, o.status & 512),    — OBJTRG_NOTFORREPL
    is_instead_of_trigger = sysconv(bit, o.status & 1024)    — OBJTRG_INSTEADOF
   FROM sys.sysschobjs o
   LEFT JOIN sys.syspalnames n ON n.class = 'OBTY' AND n.value = o.type
   LEFT JOIN sys.syspalvalues pc ON pc.class = 'UNCL' AND pc.value = o.pclass
   WHERE o.type IN ('TA','TR') AND o.pclass <> 100
      AND has_access('TR', o.id, o.pid, o.nsclass) = 1


					  

To select the relevant data from the sys.triggers view, you need only reference the columns in the view that are of interest, and the complexity of the view is hidden. The following query demonstrates the simplicity of a SELECT statement against the sys.triggers view:

select name, type, create_date
 from sys.triggers
 where name like 'i%'

You can see from the sys.triggers example why the folks at Microsoft are big proponents of views. Complex queries such as the sys.triggers view can be written and tested once, and subsequent data retrieval can be accomplished by selecting from the view.

Focusing on Specific Data

Views allow users or developers to focus on the specific data elements they need to work with. Tables that contain hundreds of columns or columns that have limited value for the end user can be filtered with a view such that only the relevant data elements are returned.

The HumanResources.vEmployee view in the Adventureworks2008 database is a good example of a view that focuses on specific data and simplifies data access. The view definition follows:

ALTER VIEW [vEmployee]
AS
SELECT
    e.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,e.[JobTitle]
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,sp.[Name] AS [StateProvinceName]
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName]
    ,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[BusinessEntityAddress] bea
    ON bea.[BusinessEntityID] = e.[BusinessEntityID]
    INNER JOIN [Person].[Address] a
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.BusinessEntityID = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea   ON p.[BusinessEntityID] =
ea.[BusinessEntityID]


					  

The HumanResources.vEmployee view filters out much of the data that is sensitive or superfluous when gathering the basic information about an employee.

Abstracting Data

Data abstraction, in its simplest form, isolates the client code from changes to the underlying structure. A view can be used to implement data abstraction within your database schema. If, for example, you have client code that will retrieve data from a database table that is likely to change, you can implement a view that retrieves data from the underlying table. The client code will then reference the view and never access the underlying table directly. If the underlying tables change or the source of the data for the view changes, these changes can be isolated from the referencing client code.

To demonstrate this scenario, let’s look at the following SELECT statement, which retrieves data directly from the Sales.SalesOrderHeader table:

select TerritoryID, sum(TotalDue)
 from Sales.SalesOrderHeader
  group by TerritoryID
order by TerritoryID

The client code could certainly utilize this kind of query to retrieve the territory data. You may find, however, that the data retrieval would be better placed within a view if the summarized territory data were slated to be rolled up into an aggregate table at a later time. In this scenario, a view like the following could be created initially:

CREATE VIEW vw_TerritoryOrders AS
select TerritoryID, sum(TotalDue) 'TotalSales'
 from Sales.SalesOrderHeader
  group by TerritoryID

The client code that needs the territory data would then reference the vw_TerritoryOrders view. If the source of the territory data changes and it is rolled up in an aggregate table, the view can be changed to reflect the new source for the data, but the client code remains unchanged. The following example alters the vw_TerritoryOrders view such that the source of the data is changed:

ALTER VIEW vw_TerritoryOrders AS
select TerritoryID, SalesYTD 'TotalSales'
 from Sales.SalesTerritory

Changing a single view in these types of scenarios can be much easier than changing the client code that has direct references to the table. This type of abstraction also applies to partitioned views.

Controlling Access to Data

Views can be used as a security mechanism to limit a user’s access to specific data. This type of view security can be used to limit the columns that a user has access to or the rows that the user has access to. A view that limits the accessible columns can be referred to as vertical security, or column-level security. A view that restricts the rows that are returned is referred to as horizontal security, or row-level security.

With vertical security, a view is created that contains only the data elements or columns that you want to make visible. Columns that are sensitive in nature (for example, payroll data) can be excluded from a view so that they are not seen when the user selects from the view.

After the view is created, security can be granted on the view. If the owner of the objects referenced in the view is the same as the owner of the view itself, the user who is granted permission to the view does not need to have permission granted to the underlying objects. Listing 1 gives an example of this scenario.

Listing 1. Security with Views

USE adventureworks2008 go CREATE LOGIN OwnerLogin WITH PASSWORD = 'pw' CREATE USER OwnerLogin FOR LOGIN OwnerLogin EXEC sp_addrolemember N'db_owner', N'OwnerLogin' CREATE LOGIN NonOwnerLogin WITH PASSWORD = 'pw' CREATE USER NonOwnerLogin FOR LOGIN NonOwnerLogin —Connect as the OwnerLogin at this point Go CREATE VIEW OwnerView as select LoginID, JobTitle, BirthDate, Gender, HireDate, SalariedFlag from HumanResources.Employee go GRANT SELECT ON [dbo].[OwnerView] TO [NonOwnerLogin] —Connect as the NonOwnerLogin at this point —The following select succeeds because the owner of the —view that was granted permission is the same as the underlying —table in the view select * from OwnerView —The following SELECT against the underlying table fails —because the NonOwnerLogin does not have permission to —select from the table. He can only select through the view select * from HumanResources.Employee

Listing 1 outlines a scenario where one login creates a view that selects specific columns from the HumanResources.Employee table. The Employee table is part of the HumanResources schema, and it is owned by DBO. The view that is created is also owned by DBO because the login (OwnerLogin) that created the view is a member of the db_owner role. Ultimately, NonOwnerLogin is granted permission to the view. When the NonOwnerLogin user connects to the database, that user can select rows from the view and will see only the columns in the Employee table that have been selected in the view. If that user tries to select rows directly from the underlying HumanResources.Employee table, a permission-related error fires. Ownership chaining is the key to making this scenario work.

With ownership chaining, SQL Server automatically authorizes a user to access the underlying tables, views, or functions referenced in the view. This happens only if the view has the same owner as the underlying objects and the user has been granted permission to the view. If, however, you have various owners of the underlying objects that a view references, permissions must be checked at each level. If access is denied at any level, access to the view is denied. Ownership chaining was available in prior versions and is still available in SQL Server 2008 for backward compatibility.

Horizontal security can also be implemented with a view. With horizontal security, a WHERE clause is included in the view’s SELECT statement to restrict the rows that are returned. The following example demonstrates a simple view that utilizes horizontal security:

CREATE VIEW EmpViewHorizontal
  as
 select EmployeeID, BirthDate, Gender, HireDate, SalariedFlag
  from HumanResources.Employee
 where HireDate >  '3/1/03'

—Sample SELECT results from the view:

LoginID                 BirthDate   Gender   HireDate    SalariedFlag
adventure-works\syed0   1965-02-11  M        2003-04-15  1
adventure-works\lynn0   1961-04-18  F        2003-07-01  1
adventure-works\rachel0 1965-08-09  F        2003-07-01  1

Only the rows in the Employee table with a HireDate value greater than March 1, 2003, are returned when you select everything from the view. Separate views can be created based on geography, demographics, or any other data element that requires a different set of security.

Keep in mind that additional conditions can be applied when selecting from a view. You can utilize another WHERE clause in the SELECT statement that uses a view. This is demonstrated in the following example:

select * from EmpViewHorizontal
 where HireDate >= '7/1/03'
  and BirthDate > '1/1/65'

LoginID                 BirthDate  Gender  HireDate   SalariedFlag
adventure-works\rachel0 1965-08-09 F       2003-07-01 1

As you can see, a view with horizontal security restricts your initial result set but does not prevent you from applying additional conditions to obtain the desired result.