DATABASE

Creating and Managing Views in SQL Server 2008 : Creating Views

7/13/2011 6:01:27 PM
You can create several different types of views in SQL Server 2008, including standard views, indexed views, and partitioned views. An indexed view has a unique clustered index defined on it that causes the view to be materialized. In other words, the creation of the index causes physical storage of the data related to the view’s index. Partitioned views join horizontally partitioned data from a set of distinct tables. They can be locally partitioned, meaning that the tables are on the same server; or they can be distributed, meaning that some of the tables exist on other servers. 

All types of views share a common set of restrictions:

  • Every column (including derived columns) must have a name.

  • The SELECT statement used in the view cannot include the COMPUTE BY clause or the INTO keyword.

  • The SELECT statement used in the view cannot include the ORDER BY clause.

  • The SELECT statement used in the view cannot contain temporary tables.

  • You cannot associate AFTER triggers with views, but you can associate INSTEAD OF triggers.

  • You cannot associate rules or default definitions with a view.

  • You cannot define a full-text index on a view.

A view can have a maximum of 1,024 columns. You can select all the columns for a view by using a SELECT * statement, but you need to use some caution when doing so. In particular, you must keep in mind that the view will not display columns that have been added to the view’s underlying tables after the view has been created. The fact that the new columns are not displayed can be a good thing but is sometimes overlooked. You can prevent changes to the underlying objects (for example, tables) by creating the view with SCHEMABINDING. SCHEMABINDING is discussed in the next section.

If you want the changes to the underlying objects to be reflected in the views, you can use the sp_refreshview stored procedure. This stored procedure updates the metadata for the specified non-schema-bound view.


SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('Person.Person')

To generate the executions for another object, you simply change the name of the object (that is, Person.Person) found at the end of the script to the name of the object you want to investigate.


With these guidelines in mind, you are now ready to create your view. Views can be created in SQL Server 2008 using T-SQL or SQL Server Management Studio (SSMS).

Creating Views Using T-SQL

The CREATE VIEW statement is used to create views with T-SQL. The syntax for the CREATE VIEW statement follows:

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement [ ; ]
[ WITH CHECK OPTION ]

<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

This statement and the related options are essentially the same in SQL Server 2008 as they were in SQL Server 2005 and SQL Server 2000. We first look at a simple example for creating a view with T-SQL and then delve into several other examples that utilize the view attributes. Listing 1 shows a sample T-SQL statement for creating a simple view.

Listing 1. Creating a Simple View with T-SQL
CREATE VIEW Sales.vw_OrderSummary as
select datepart(yy, orderdate) as 'OrderYear',
datepart(mm, orderdate) as 'OrderMonth',
sum(TotalDue) as 'OrderTotal'
from Sales.SalesOrderHeader
group by datepart(yy, orderdate), datepart(mm, orderdate)

There are several important aspects to notice in the example in Listing 1. First, all the columns in the SELECT statement are derived columns and do not simply reference a column in a table. You do not need to have a derived column in your view, but if you do, the derived column(s) must have a name or an alias assigned to it to be able to create the view. The column name allows you to reference the derived column when selecting from the view. If the derived columns in the SELECT statement are not named, the CREATE VIEW statement will fail.

Another notable characteristic of the simple view example is that an aggregate is used in the SELECT statement. Aggregates are allowed in views and are common implementations of views. Views with aggregates can be used instead of summary tables that denormalize data and use additional disk space. Keep in mind that the results of any view (including those with aggregates) are not returned in any particular order. Views cannot be created with the ORDER BY clause, but the ORDER BY clause can be utilized in a SELECT statement that references the view. The following example shows the first five rows of the vw_OrderSummary view created in Listing 27.2:

select top 5 * from Sales.vw_OrderSummary

OrderYear OrderMonth OrderTotal
---------- ---------- -----------------
2003 5 4449886.2315
2001 11 3690018.6652
2003 8 6775857.0745
2002 7 3781879.0708
2003 11 5961182.6761

You can see from the results of the SELECT that the summarized order information is not returned in any particular order. If you want to sort the results, you can treat the view like a table in a SELECT statement and use the ORDER BY clause to produce the desired results. The following example shows a SELECT statement from the vw_OrderSummary view and the ordered results:

select top 5 *
from Sales.vw_OrderSummary
where OrderYear >= 2004
order by OrderYear, OrderMonth

OrderYear OrderMonth OrderTotal
---------- ---------- ------------------
2004 1 3691013.2227
2004 2 5207182.5122
2004 3 5272786.8106
2004 4 4722890.7352
2004 5 6518825.2262

Tip

In many cases, it is best to create views that include the primary key columns from the underlying tables. This allows the views to be joined to other tables. Consider, for example, a view created on the Employee table in the Adventureworks2008 database. If you want to join that view to another table (such as EmployeeAddress), you need the primary key of the table (that is, Employee.EmployeeID) in the view.


Views can also be created with the following special view attributes: ENCRYPTION, SCHEMABINDING, and VIEW_METADATA. Each of these attributes and some other specialized views are discussed in the following sections.

ENCRYPTION

The ENCRYPTION attribute causes the view definition to be stored as encrypted text in sys.syscomments. This feature is also available for stored procedures and other database code that you may want to protect. One issue to consider when you create a view using the ENCRYPTION option is that this option prevents the view from being published as part of SQL Server replication.

The following example shows the creation of one of the prior views with the ENCRYPTION attribute:

IF  EXISTS (SELECT * FROM sys.views WHERE
object_id = OBJECT_ID(N'[Sales].[vw_OrderSummary]'))
DROP VIEW [Sales].[vw_OrderSummary]
GO

CREATE VIEW Sales.vw_OrderSummary
WITH ENCRYPTION AS
select datepart(yy, orderdate) as 'OrderYear',
datepart(mm, orderdate) as 'OrderMonth',
sum(TotalDue) as 'OrderTotal'
from Sales.SalesOrderHeader
group by datepart(yy, orderdate), datepart(mm, orderdate)
go

The following SELECT statement from sys.syscomments retrieves the text related to the encrypted view and shows that the view definition is not visible in the Text column:

SELECT id, OBJECT_NAME(ID) 'ViewName', text
FROM SYS.sysCOMMENTS
WHERE OBJECT_NAME(ID) LIKE '%vw_OrderSummary%'

id ViewName text
------------ ------------------- --------------------------------
919674324 vw_OrderSummary NULL

SCHEMABINDING

The SCHEMABINDING attribute binds a view to the schema of the underlying table(s) referenced in the view’s SELECT statement. This binding action prevents any changes to the underlying tables that would affect the view definition. For example, if you have a view that includes the Employee.Title column, this column cannot be altered or dropped in the Employee table. If schema changes are attempted on the underlying tables, an error message is returned, and the change is not allowed. The only way to make the change is to drop the view or alter the view to remove the SCHEMABINDING attribute.

Tip

Views created with SCHEMABINDING have been used in the past to simply prevent changes to the underlying schema. Any table for which you wanted to prevent schema changes was included in a view, and this essentially locked the definition of the table. This approach is no longer needed because you can accomplish the same thing using DDL triggers, which can react to schema changes and prevent them if desired.


VIEW_METADATA

When the VIEW_METADATA option is specified, SQL Server returns information about the view, as opposed to the base tables. This happens when browse-mode metadata is requested for a query that references the view via a database API. Browse-mode metadata is additional information returned by SQL Server to client-side DBLIB, ODBC, and OLE DB APIs, which allows them to implement client-side updatable cursors.

WITH CHECK OPTION

WITH CHECK OPTION forces all data modifications made through a view to adhere to the conditions in the view. The example shown in Listing 2 shows a view created using WITH CHECK OPTION.

Listing 2. View using a WITH CHECK OPTION
CREATE VIEW HumanResources.vw_MaleEmployees
AS
SELECT LoginID, Gender
FROM HumanResources.Employee
WHERE Gender = 'M'
WITH CHECK OPTION

The following UPDATE statement fails when executed against the view created in Listing 2 because the Gender change would cause it to no longer be seen by the view:

UPDATE HumanResources.vw_MaleEmployees
SET Gender = 'F'
WHERE LoginId = 'adventure-works\taylor0'

Creating Views Using the View Designer

SQL Server 2008 provides a graphical tool, called the View Designer, you can use to create views. This tool can be an invaluable aid when you are creating or modifying a view. The View Designer is equipped with four panes that provide the information relative to the view. Figure 1 shows the View Designer display for the Person.vStateProvinceCountryRegion view installed in the Adventureworks2008 database. To view an existing view in the View Designer, right-click on the view listed in the Object Explorer and select Design. To create a new view via the View Designer, right-click the Views node in the Object Explorer and select New View. An empty View Designer is displayed.

Figure 1. The View Designer window.

The View Designer has these four panes:

  • Diagram pane— Gives a graphical view of the tables that are part of the view. This includes the columns in the tables and relationships between the tables contained in the view.

  • Criteria pane— Displays all the columns selected in the view and allows for sorting, filtering, and other related column-oriented criteria.

  • SQL pane— Renders the T-SQL associated with the view.

  • Results pane— Shows the results of that view’s SELECT statement.

The panes in the View Designer are dependent on each other. If you add a WHERE clause in the SQL pane, the corresponding Filter value is added in the Criteria pane. Similarly, if you right-click in the Diagram pane and add a table to the view, the Criteria and SQL panes are updated to reflect this change.

Tip

One of the most amazing features of the View Designer is the capability to render a SQL statement into its graphical form. You can copy T-SQL into the SQL pane, and the View Designer reverse-engineers the tables into the Diagram pane, giving you a graphical display of the query. Some complex SQL statements cannot be rendered, but many of them can. Give it a try; you will be impressed.


You can control the View Designer via the Query Designer menu option as well. Adding a new table, verifying the T-SQL, and changing the panes displayed are just some of the options available on this menu.

Note

The View Designer does not allow you to set every attribute of a view. It is a great starting point for creating a view, but you need to set some attributes using T-SQL after creating the view. For example, you cannot specify WITH CHECK OPTION in the View Designer, but you can set it by altering the view after it has been created.

There is also no option to script a view from the View Designer. You must close the View Designer first, and then you are asked whether you want to save the view. If you click Yes, a prompt allows you to specify a name.


The Properties window displays information about the view and also allows you to enter additional view properties. If this window is not visible, you can select the Properties window from the View menu or simply press F4. The properties you can set on the view include (but are not limited to) a description, the schema that owns the view, and whether to bind the view to the schema. Figure 2 shows the Properties window for the Person.vStateProvinceCountryRegion view that we looked at earlier.

Figure 2. The view’s Properties window.


After defining a view using the panes in the View Designer and setting its properties, you can choose to save the view. You are prompted to give it a name. After you save the view, it appears in the Object Explorer tree.

Other  
 
Most View
IMac 21.5 Inch And 27 Inch (Late 2013) Review (Part 2)
Scythe Grand Kama Cross Rev.B Microprocessor Cooler Review (Part 3)
Transphone – The Budget Phone And Tablet Combo
Kobo Aura HD – E-Reader For The Most Eager Readers And Books Lovers (Part 2)
Canon PowerShot G7 X
Microsoft Surface RT - Cleverly Designed Tablet
Sony VAIO Duo 11 – All Things To All People
Ascend D1 Quad XL - Huawei Quad-Core Smartphone (Part 4)
Panasonic G Vario 45-150mm f/4-5.6 Aspherical Mega OIS
SQL Server : Implementing One-Way Encryption (part 1) - Populate the Hash Columns, Verify the Implementation, Drop the Unencrypted Column
Top 10
Windows Phone 8 : Microsoft Office Mobile - Excel (part 10) - Entering Formulas,AutoSum
Windows Phone 8 : Microsoft Office Mobile - Excel (part 9) - Sharing a Spreadsheet, Fitting and Hiding Text
Windows Phone 8 : Microsoft Office Mobile - Excel (part 8) - Accessing Charts and Sheets, Saving a Spreadsheet
Windows Phone 8 : Microsoft Office Mobile - Excel (part 7) - Undo and Redo, Charting
Windows Phone 8 : Microsoft Office Mobile - Excel (part 6) - Sorting a Column, Filtering
Windows Phone 8 : Microsoft Office Mobile - Excel (part 5) - Freezing a Pane, Applying Formatting Options
Windows Phone 8 : Microsoft Office Mobile - Excel (part 4) - Selecting Multiple Cells, Viewing Cell Text
Windows Phone 8 : Microsoft Office Mobile - Excel (part 3) - Viewing and Navigating Comments,Searching a Spreadsheet
Windows Phone 8 : Microsoft Office Mobile - Excel (part 2) - Adding a Comment to a Cell, Changing the Comment Author
Windows Phone 8 : Microsoft Office Mobile - Excel (part 1) - Creating a New Spreadsheet, Working with the Excel Interface