Creating and Optimizing Views in SQL Server

Creating and Modifying Views

You can create views using the Enterprise Manager, the Query Analyzer, or an Access project. In addition, the Enterprise Manager has the Create View Wizard, which walks you through the process of creating a simple view.

Creating Views Using the Create View Wizard

To create a view using the Enterprise Manager, select Tools, Wizards, Database, Create View Wizard. The introductory dialog box on the wizard informs you that the wizard can perform the following actions:

• Select the database referenced by the view

• Select one or more tables referenced by the view

• Select one or more columns that you want the view to display

• Name the view and define restrictions

The second wizard dialog box then lets you select the database. The one after that lets you select the tables involved in the view, as shown in Figure 9.3, where the Employees, Order Details, Orders, Products, and Customers tables are selected from the Northwind database.

Figure 9.3
Selecting tables using the Create View Wizard.

Figure 9.4 shows the dialog box for selecting the columns to be displayed in the view.

Figure 9.4
Selecting the columns to be displayed in the view.

The next wizard dialog box lets you type in a WHERE clause, thus limiting the rows in the view. The following one lets you name the view. The final wizard dialog box summarizes your options and lets you view the SELECT statement that will be created.

The main problem with using the Create View Wizard is that it does not create the join syntax between tables if you select multiple tables. This limitation results in a Cartesian product query if you do not go in and modify the view by typing in the join syntax yourself. Also, it doesn't help at all in building criteria into a WHERE clause. For these reasons, using the Create View Wizard is pretty much a waste of time if you need anything other than a simple view on a single table.

Creating Views Using the Enterprise Manager

Creating a view using the Enterprise Manager is a much more satisfying experience than running the Create View Wizard. It will also automatically create join syntax for you, which is extremely useful if you are unfamiliar with the join syntax. To create a view in the Enterprise Manager, right-click the Views folder in the database in which you want to create the view and choose New View from the list. Click the Add Table toolbar button to add tables to the view. You can add multiple tables at a time by simply double-clicking them in the table list.

This time the joins are created automatically if you have enforced referential integrity in your SQL Server database. You can delete any joins that you don't want included in your view by selecting the join line and pressing the Delete key, without affecting the underlying referential integrity join that it was modeled on. You can also create ad hoc joins by dragging and dropping from one field to another. Select the columns to be displayed in the view by checking their associated check boxes.

The data tools have a four-pane window, the display of which is controlled by the toolbar buttons or the right-click menu. Here are the panes:

• Show/hide diagram pane

• Show/hide grid pane

• Show/hide SQL pane

• Show/hide results pane

Figure 9.5 shows the four available panes when creating a new view. The contents of the SQL pane are generated automatically by making selections in the diagram pane and the grid pane. The results pane is generated by clicking the Run toolbar button (or right-clicking and choosing Run from the list).

Figure 9.5
The new view in the Enterprise Manager displays four sections.

There are no options for dragging and dropping objects from outside the New View window when you create a view using the Enterprise Manager. Unfortunately, the New View window is opened modally, so you can't do anything else in the Enterprise Manager until you close the window. However, you can copy and paste from this window to other applications, which makes it useful even if you create views using the Query Analyzer, as discussed later in this chapter.

Creating Views Using an Access Project

To create a new view in an Access project, select Views from the object list and click New. The visual data tools are very similar to the ones in the Enterprise Manager, but there are a few differences in the toolbar buttons and the panes available. The display of results in a datasheet works only after the view has been saved. (You can display the results of a view in the Enterprise Manager without having to save it first.)

The main advantage of using Access to create views is that the designer window isn't modal. In fact, you can drag and drop tables and views from the main object list to the diagram pane. Figure 9.6 shows the design of a view in an Access project.

Figure 9.6
Creating a view using an Access project.

Creating Views Using the Query Analyzer

The Query Analyzer in SQL Server 2000 makes it much easier to create views than it was in past versions of the Query Analyzer. However, unlike using the visual data tools found in the Enterprise Manager and an Access project, you still have to do some typing (or some copying/pasting) because there's no tool that will create joins for you. A new feature in SQL Server 2000 Query Analyzer is the Object Browser, which lets you drag-and-drop objects and will automatically create SELECT statements with a right-mouse drag and drop. The Object Browser also contains three templates for creating simple views. Load the Object Browser by choosing View, Object Browser from the menu, clicking the Object Browser toolbar button, or pressing the F2 key. If you drag a table from the Object Browser to the Query Analyzer using the right mouse button instead of the left, you'll get a list of menu items to choose from. Pick SELECT, and a SELECT statement will be generated for you that includes all the columns in the selected table. However, if you have multiple tables and joins in your view, you need to type the join syntax yourself or paste it in from another source.

---

Tip - Because typing in join syntax is prone to errors, you can create joins using the visual data tools in the Enterprise Manager or an Access project and paste them into the Query Analyzer. This can be a great timesaver if you're not a good typist, or you're a little weak on join syntax.

---

You can test your SELECT statement prior to executing the CREATE VIEW by highlighting just the SELECT statement, leaving out the CREATE VIEW...AS header, and then clicking the Execute Query button or pressing the F5 key. This will run the SELECT statement, and you can view the result set in the results pane. Another option is to have your syntax parsed before executing it by choosing Query, Parse from the menu (or clicking Ctrl+F5). Figure 9.7 shows the definition of a view, with the Object Browser loaded, just after executing the CREATE VIEW statement.

Figure 9.7
Creating a view using the Query Analyzer.

Creating a View from a Template

There are two ways to create a view from a template:

• Load the Object Browser (or press F8), select the Templates tab, and drag a template from the Create View node onto the query pane of the Query Analyzer.

• Choose Edit, Insert Template (or press Ctrl+Shift+Ins) and select the template from the Create View folder.

The three available templates are

• Create View Basic Template. Choose this to create a simple view.

• Create View WITH CHECK OPTION. Choose this to create a view that contains the WITH CHECK OPTION clause. This option prevents saving data to the underlying tables that does not match the view definition.

• Create View WITH SCHEMA BINDING. Choose this to create a view that contains the WITH SCHEMA BINDING clause. This option does not allow changes to the underlying tables unless the view is dropped first. However, views without this clause might not work properly if the schema of an underlying table is changed after the view is created.

The Query Analyzer will help you fill in the template parameters if you Choose Edit, Replace Template Parameters (or press Ctrl+Shift+M), by displaying a dialog box for you to fill in the template parameters. Click the Replace All button to have all the template parameters filled in automatically. The following output displays the view created by using the basic template before the parameters are replaced:

-- =============================================
-- Create view basic template
-- =============================================
IF EXISTS (SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.VIEWS 
  WHERE TABLE_NAME = N'<view_name, sysname, view_test>')
 DROP VIEW <view_name, sysname, view_test>
GO
CREATE VIEW <view_name, sysname, view_test>
AS 
 <select_statement, , SELECT * FROM authors>
GO

And here's the view after the parameters have been replaced:

-- =============================================
-- Create view basic template
-- =============================================
IF EXISTS (SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.VIEWS 
  WHERE TABLE_NAME = N'vwProductsSelect')
 DROP VIEW vwProductsSelect
GO

CREATE VIEW vwProductsSelect
AS
 SELECT * FROM Products
GO

Note that all the text inside the chevrons in the template has been replaced. You can also add your own custom templates by following the instructions for creating new templates outlined in Chapter 8.

Using ALTER VIEW to Modify a View

If you try to use the CREATE VIEW syntax on a view that has already been saved, you'll get an error. SQL Server allows you to alter the definition of a view using the ALTER VIEW syntax, which is almost identical to the CREATE VIEW syntax (with the substitution of the word ALTER for the word CREATE). Listing 9.3 modifies an existing view by adding a WHERE clause to show only products that are not discontinued.

Listing 9.3 Modifying a View That Already Exists

ALTER VIEW vwProductByCategory
AS
 SELECT CategoryName, ProductName, UnitPrice
 FROM Products
 INNER JOIN Categories
 ON Products.CategoryID = Categories.CategoryID

 WHERE Discontinued = 0

The ALTER VIEW syntax was introduced in SQL Server 7.0. In prior versions of SQL Server, you had to drop a view and then re-create it. The advantage of ALTER VIEW is that only the definition of the view is changed. If the view was dropped and re-created, a new object ID would be generated, and all internal references to the view would be invalidated. All permissions on the view would be lost, and any stored procedures using the view would need to be recompiled.

---

Note - If you use an Access project to modify an existing view, only the SELECT statement defining the view is shown. Access takes care of using the ALTER VIEW syntax behind the scenes.

---

Views, Subqueries, and Outer Joins

Another use of views is to encapsulate complex joins and subqueries. For example, here are a couple of ways to find out if there are any orders without corresponding order detail line items.

The following view uses NOT EXISTS to determine if there are any orders that do not have any line items:

CREATE VIEW vwNoOrderDetails
AS
 SELECT OrderID 
 FROM Orders
 WHERE NOT EXISTS
 (SELECT OD.OrderID 
  FROM [Order Details] OD 
  WHERE Orders.OrderID = OD.OrderID)

A user simply has to write the following query to find empty orders:

SELECT * FROM vwNoOrderDetails

Another way to write the same query would be to use a LEFT JOIN instead of a subquery:

CREATE VIEW vwNoOrderDetailsJoin
AS
 SELECT Orders.OrderID
 FROM Orders 
 LEFT JOIN [Order Details] OD 
 ON Orders.OrderID = OD.OrderID
 WHERE OD.OrderID IS NULL

The second technique, using a LEFT JOIN, will be more efficient in most cases. However, performance will also depend on the amount of data in your tables and the indexes you have defined.

Sorting and Top Values in Views

If you look back to Figure 9.5, you'll see that the syntax used in the SELECT statement reads like this:

SELECT TOP 100 PERCENT ...

This apparently superfluous statement is needed because an ORDER BY clause appears at the end of the view definition. Here's a brief history of how SQL Server 2000 came to support sorting in views.

The TOP syntax was introduced in SQL Server 7.0 to provide the equivalent of an Access "top values" query, although there are some subtle differences—TOP is discussed in more detail in Chapter 8. To get top values, you need a sort order, so an ORDER BY clause is required in order for TOP to work properly.

The TOP syntax with its associated ORDER BY clause are Transact-SQL extensions and are not part of the ANSI SQL-92 standard. As far as the ANSI SQL-92 standard is concerned, the definition of a view is unordered. If you want an ordered view, you're supposed to use the ORDER BY clause in a SQL statement querying the view, the same way you would with a table. Here's an example:

SELECT * FROM MyView ORDER BY SomeColumn

However, enterprising developers working with SQL Server 7.0 soon discovered by reading the error message returned from SQL Server that they could use SELECT TOP 100 PERCENT to work around the ANSI SQL-92 restriction of not sorting in view definitions. If you try to use the ORDER BY clause in a view without the TOP syntax, you'll get an error message that tells you to use TOP. In SQL Server 2000, when you create a view in Enterprise Manager, TOP 100 PERCENT is inserted by default, and sorting options are provided. This new sorting capability in views might offend some SQL purists, but developers, who have long found it frustrating not to be able to sort in views, welcome it wholeheartedly. The complete view syntax is shown in Listing 9.4.

Listing 9.4 Creating a Sorted View

CREATE VIEW dbo.vwCustomerOrderTotals
AS
 SELECT TOP 100 PERCENT Customers.CompanyName,
 Orders.OrderID, Orders.OrderDate,
 Employees.LastName AS SoldBy,
 SUM([Order Details].UnitPrice *
 [Order Details].Quantity) AS Total
 FROM Customers
 INNER JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
 INNER JOIN Employees
  ON Orders.EmployeeID = Employees.EmployeeID
 INNER JOIN [Order Details]
  ON Orders.OrderID = [Order Details].OrderID
 GROUP BY Customers.CompanyName, Orders.OrderID,
 Orders.OrderDate, Employees.LastName

 ORDER BY Customers.CompanyName, Orders.OrderDate DESC

Views with Functions

Aside from the inability to sort in views, the other major limitation has traditionally been that views cannot contain parameters. This, too, is now no longer a problem. SQL Server 2000 allows you to work with user-defined, table-valued functions as though they were views and to pass parameters to the functions as though they were stored procedures. Before switching over to user-defined functions, however, consider the advantages of using standard aggregate and system functions in views.

Using Aggregate Functions

Listing 9.5 shows a view that's used to calculate the total value sold by each employee. The SUM() function is used in the SELECT clause, and the GROUP BY clause groups by employee.

Listing 9.5 Using an Aggregate Function in a View

CREATE VIEW vwOrderTotalsByEmployee
AS
 SELECT Employees.LastName, SUM([Order Details].UnitPrice *
 [Order Details].Quantity) AS Total
 FROM Employees INNER JOIN Orders
 ON Employees.EmployeeID = Orders.EmployeeID
 INNER JOIN [Order Details]
 ON Orders.OrderID = [Order Details].OrderID

 GROUP BY Employees.LastName

Using System Functions

Listing 9.6 shows using a view employing the SUSER_SNAME() system function to query the Orders table. The SUSER_SNAME() system function returns the name of the current user. This would restrict the current user to viewing only orders in which that user was the recorded salesperson.

Listing 9.6 Using a System Function in a View

CREATE View vwOrdersByUser
AS
SELECT * FROM Orders

WHERE SalesPerson = SUSER_SNAME()

In order for this view to work, you need to create a new column in the Orders table named "SalesPerson" with a data type of nvarchar. You also need to ensure that all rows have the correct values for each user. For example, each INSERT statement would need to place the username in the table so that views based on the table would return the correct results:

INSERT INTO Orders (CustomerID, SalesPerson)
VALUES ('BONAP', SUSER_SNAME())

An even better option would be to create a default value of SUSER_SNAME() for the SalesPerson column. That way you wouldn't have to explicitly insert the username each time. Setting up the table and view this way gives you a dynamic view, where users would see only their own orders.

The SUSER_SNAME() system function is similar to the CurrentUser() function in Access. You may be wondering why it isn't just called USER_NAME. Well, there's already a function by that name, but it returns the database username, not necessarily the login name of the user. For example, if you are logged on to SQL Server as user Bart, who is mapped to the sysadmin role, USER_NAME will return "dbo," but SUSER_SNAME() will return "Bart." SUSER_SNAME() will also return the Windows NT/Windows 2000 login name if you're using integrated security.

Using Views with User-Defined Functions

The three types of user-defined functions are

• Scalar functions, which return a single value. Here's an example of a simple scalar function that computes the average price of all products:

   CREATE FUNCTION fnAvgProductPriceScalar() 
   RETURNS money 
   AS 
   BEGIN
   Declare @Average money
   SELECT @Average = (SELECT AVG(UnitPrice)FROM Products)
   RETURN @Average
   END

• Inline table-valued functions, which contain a single SELECT statement that returns a set of rows, referred to as a table. Here's an example of an inline table-valued function that returns a table consisting of the ProductID, ProductName, and UnitPrice from the Products table:

   CREATE FUNCTION fnProductSelectInline()
   RETURNS TABLE 
   AS
   RETURN
   (SELECT ProductID, ProductName, UnitPrice
   FROM Products)

• Multi-statement table-valued functions, which can contain more complex statements contained within a BEGIN...END block. Like inline table-valued functions, multi-statement table-valued functions also return a table as the result set. Here's an example of a multi-statement table-valued function that has an optional input parameter. (All the function types support input parameters.) If the parameter is supplied, then only products matching a CategoryID are returned. If the parameter value is not supplied, all rows are returned:

   CREATE FUNCTION fnProductSelectTableMulti
   (@CategoryID int = NULL)
   RETURNS @retProducts TABLE 
   (ProductID int primary key,
   ProductName nvarchar(50) NOT NULL, 
   UnitPrice money)
   AS 
   BEGIN 
   -- Declare local table variable
   DECLARE @temp TABLE (
   ProductID int primary key, 
   ProductName nvarchar(50) NOT NULL,
   UnitPrice money)
  
   IF @CategoryID IS NULL -- insert all records into variable
   BEGIN
    INSERT @temp
    SELECT Products.ProductID, 
    Products.ProductName,
    Products.UnitPrice
    FROM Products
   END
   ELSE -- insert records matching parameter into variable
   BEGIN
    INSERT @temp
    SELECT Products.ProductID, 
    Products.ProductName,
    Products.UnitPrice
    FROM Products
    WHERE CategoryID = @CategoryID
   END
  
   --Insert local table variable into Return table 
   INSERT @retProducts
   SELECT ProductID, ProductName, UnitPrice
   FROM @temp
   RETURN
  END

Each type of user-defined function can be used with views (or in place of views) in different ways.

Using Scalar Functions in Views

Scalar functions can be used in the SELECT clause of a query. The following view uses the fnAvgProductPriceScalar in the SELECT list.

CREATE VIEW vwUseScalarSELECT
AS
 SELECT ProductName,
 dbo.fnAvgProductPriceScalar() as AvgPrice,
 UnitPrice
 FROM Products

Here are the first few rows of the result set created by selecting all the rows from the view:

ProductName        AvgPrice    UnitPrice    
---------------------------------------- --------------------- ----------------
Geitost         28.8923    2.5000
Guaraná Fantástica      28.8923    4.5000
Konbu         28.8923    6.0000

The scalar function can also be used in the WHERE clause. The following view selects only products whose prices are greater than the average computed by fnAvgProductPriceScalar:

CREATE VIEW vwUseScalarWHERE
AS
 SELECT ProductName, UnitPrice 
 FROM Products
 WHERE UnitPrice > dbo.fnAvgProductPriceScalar()

Here are the first few rows of the result set from selecting all the rows from this view, which returns only products with prices that are above average:

ProductName        UnitPrice
---------------------------------------- --------
Uncle Bob's Organic Dried Pears   30.0000
Ikura         31.0000
Gumbär Gummibärchen      31.2300

You can also use a function in both the SELECT and the WHERE clause. The following view uses the function in the SELECT clause to display the amount over average for any product whose UnitPrice is greater than average:

CREATE VIEW vwUseScalarBoth
AS
 SELECT ProductName, UnitPrice,
 (UnitPrice -dbo.fnAvgProductPriceScalar()) as AmtOverAverage
 FROM Products
 WHERE UnitPrice > dbo.fnAvgProductPriceScalar()

Here are the first few rows of the result set created by selecting all the rows from that view:

ProductName        UnitPrice    AmtOverAverage 
---------------------------------------- --------------------- ----------------
Uncle Bob's Organic Dried Pears   30.0000    1.1077
Ikura         31.0000    2.1077
Gumbär Gummibärchen      31.2300    2.3377

Using Inline Functions in Views

Since inline functions return a table, they can be used in the FROM clause of a SELECT statement, as shown in the following view, which acts as a wrapper around the fnProductSelectInline() function:

CREATE VIEW vwInlineSelect
AS
 SELECT * FROM dbo.fnProductSelectInline()

Users could select columns from the view instead of using the more complicated function syntax:

SELECT ProductID, ProductName, UnitPrice
FROM vwInlineSelect
ORDER BY UnitPrice DESC

Another way to use an inline function in a view is as a subquery. The following view scans the Order Details table for ProductIDs that don't exist in the Products table by using the fnProductSelectInline() in a subquery.

CREATE VIEW vwInlineSelectFROM
AS
 SELECT ProductID, UnitPrice
 FROM [Order Details]
 WHERE NOT EXISTS
 (SELECT ProductID
 FROM dbo.fnProductSelectInline())

You can also use a view as the source for an inline function. For example, the following view joins the Customers, Orders, and Order Details tables to provide the total sales for each customer:

CREATE VIEW vwCustomerOrderTotals
AS 
 SELECT dbo.Customers.CustomerID, dbo.Customers.CompanyName,
 SUM(dbo.[Order Details].UnitPrice * 
 dbo.[Order Details].Quantity) AS Total
 FROM dbo.Customers 
 INNER JOIN
  dbo.Orders 
  ON 
  dbo.Customers.CustomerID = dbo.Orders.CustomerID 
 INNER JOIN
  dbo.[Order Details] 
  ON 
  dbo.Orders.OrderID = dbo.[Order Details].OrderID
 GROUP BY dbo.Customers.CustomerID, dbo.Customers.CompanyName

The following parameterized function selects data from the view that matches a particular CustomerID:

CREATE FUNCTION fnCustomerOrderTotalsView
 (@CustomerID char(5))
RETURNS TABLE 
AS
 RETURN
 SELECT CompanyName, Total 
 FROM vwCustomerOrderTotals
 WHERE CustomerID = @CustomerID

Note that the RETURN statement has a completely different meaning in functions than it does in stored procedures. In a stored procedure, RETURN causes the procedure to terminate, but in a function it just introduces the definition of the return set. Here's how you would query the function and supply a value for the parameter:

SELECT * FROM fnCustomerOrderTotalsView('ALFKI')

Although user-defined functions are useful in views, in some circumstances you may want to use them to replace views entirely. Their capability to support input parameters makes them more versatile than views. Plus, multi-statement table-valued functions give you all of the processing power of stored procedures.

Replacing Views with Table-Valued Functions

Multi-statement table-valued functions combine the best of both views and stored procedures. You can select from one like a view, and have parameters as with a stored procedure. The fnProductSelectTableMulti() function listed earlier in this section is flexible enough to be called with a parameter value or without one. Here's the syntax for calling it with specifying the default value for the @CategoryID input parameter:

SELECT * FROM dbo.fnProductSelectTableMulti(default)

Note that the keyword default is used inside the parentheses. This behavior is different from parameters with default values in stored procedures in which omitting the parameter implies the default value. If you omit both the parameter and the default keyword, you'll get the "An insufficient number of arguments were supplied for the procedure or function..." error.

The function will return all of the products in the Products table. If you call it with a parameter, then only the products matching the @CategoryID value will be returned. Here's the syntax for calling it with a parameter value:

SELECT * FROM dbo.fnProductSelectTableMulti(5)

Here's the first few rows of the result set, showing products from the one specified category:

ProductID ProductName          UnitPrice 
----------- -------------------------------------------------- ----------
22   Gustaf's Knäckebröd        21.0000
23   Tunnbröd           9.0000
42   Singaporean Hokkien Fried Mee      14.0000

Note that you can also use an ORDER BY clause when selecting rows from a function. This query will sort the result set by UnitPrice in ascending order:

SELECT * FROM dbo.fnProductSelectTableMulti(5)ORDER BY UnitPrice

Multi-statement table-valued functions are a very useful hybrid, combining features of both views and stored procedures. Just like stored procedures, however, multi-statement table-value functions cannot be used in queries that update, insert, or delete data. To be updateable, a function must be based on a single SELECT statement. Inline functions (ones with a single SELECT statement) can both be updateable and take parameters—a feature not available in views or in stored procedures. Updating inline functions is covered later in this chapter.

Choosing Between Views and Functions

Once you've learned about SQL Server 2000's new user-defined functions, it's natural to wonder whether you ever really need to use the old-fashioned views. Here are some guidelines to follow when choosing between views and table-returning functions:

• As covered in this chapter, functions offer greater flexibility than views. Parameters are supported, and multi-statement functions can support stored-procedure-like Transact–SQL logic and flow control. If you need these features, then functions are a clear choice over views.

• Microsoft has stated that its intention was for there to be no performance penalty for using functions rather than comparable views. If you find a performance difference, it's a bug. But only time and experience will bear out whether Microsoft succeeded in meeting this goal. Initial tests indicate that it has.

• Views do offer the performance benefit of supporting indexing, a new feature in SQL Server 2000 that is discussed later in this chapter. You cannot create indexes for functions. You could, however, wrap an indexed view in an inline function.

• Views can be created with graphical tools, such as the New View dialog in the Enterprise Manager, or the Visual Data Tools in Access ADPs or in Visual Basic. There are no comparable tools for creating functions. Of course, you could create a view and copy and paste the T-SQL into a function.

Overall, you'll probably find that as you become more familiar with user-defined functions, you'll be using them more than views. Even stored procedures will start losing ground to functions, because of the way that you can SELECT from the table-type result set of a function but not from the static results of running a stored procedure.

Horizontally Partitioned Views

If you have very large tables, SQL Server can take a while to run a query. For example, if all your sales data is in one very large table, but you typically only need to work with a subset of rows, then partitioning the table makes a lot of sense. If you have 100,000,000 rows in the table, SQL Server might have to look through the entire table, or at least the entire index, to fetch the few hundred rows you're interested in. The solution to this is to create a horizontal partition by splitting the table into separate, smaller tables. You design the member tables so that each table stores a horizontal slice of the original table based on a range of key values. Figure 9.8 diagrams what this would look like if you split a sales table holding data for a single year into quarters. However, it's not enough to just split the table into separate tables; you also need to create constraints to make sure the data in each of the new tables falls between certain values. In this case, each table has data only for one quarter of the calendar year. This is enforced by creating CHECK constraints on each table, limiting the range of dates entered to those occurring in a particular quarter. Without these CHECK constraints, the partitioned view won't work.

Once you've partitioned the table and it's now many separate tables, you need an efficient way to query all the separate tables. This is where a partitioned view comes in. Listing 9.8 shows the view created using the UNION ALL statement to combine the tables. Users can issue a single query against the view without having to worry about the fact that there are actually four separate tables involved. The view appears to the user as a single table.

Figure 9.8
Creating a horizontal partition involves breaking a large table into smaller tables.

Listing 9.8 Creating a Partitioned View

CREATE View vwSales
AS
 SELECT OrderID, OrderDate, OrderTotal FROM Sales1Q
 UNION ALL
 SELECT OrderID, OrderDate, OrderTotal FROM Sales2Q
 UNION ALL
 SELECT OrderID, OrderDate, OrderTotal FROM Sales3Q
 UNION ALL

 SELECT OrderID, OrderDate, OrderTotal FROM Sales4Q

The beauty of using a partitioned view is that SQL Server won't waste time looking through all the tables to fulfill a query that works with a particular customer. It examines all four tables in the view and determines which table is likely to have the data based on the constraint, and it ignores the other tables. It doesn't attempt to select data from any of the other tables where the constraint would rule out the possibility of the data being present. The following SELECT statement will have SQL Server look in the Sales1 table to satisfy the query since the WHERE clause limits the search to that range of data:

SELECT * FROM vwSales
WHERE OrderDate BETWEEN '1/1/2000' AND '1/22/2000'

Unlike normal UNION queries, a partitioned view is updateable if it meets the following conditions:

• The view is a set of SELECT statements, whose individual result sets are combined into one using the UNION ALL statement.

• Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function.

---

Tip - Always use UNION ALL in any union query if you want all the data to be included, even when you know there are no duplicate records. UNION ALL is faster because SQL Server does not have to worry about eliminating duplicates from the result set. You may recall that in Access, union queries are not updateable, but SQL Server overcomes this limitation for the union queries used in partitioned views, allowing them to be used in queries that modify, insert, or delete data.

---

Distributed Partitioned Views

Partitioned views can be based on data from multiple heterogeneous sources, such as remote servers, not just tables in the same database. To combine data from different remote servers, create distributed queries that retrieve data from each data source and then create a view based on those distributed queries.

When you partition data across multiple tables or multiple servers, queries accessing only a fraction of the data can run faster because there's less data to scan. If the tables are located on different servers or on a computer with multiple processors, each table involved in the query can also be scanned in parallel, thereby improving query performance. Additionally, maintenance tasks, such as rebuilding indexes or backing up a table, can execute more quickly.

By using a partitioned view, the data still appears as a single table and can be queried as such without having to reference the correct underlying table manually.

Here are the steps for setting up distributed partitioned views:

• Add each member server as a linked server. The view needs direct access to data located on all of the other servers.

• Use the sp_serveroption system stored procedure on each server to set the lazy schema validation option. This prevents the query processor from requesting metadata for the linked tables until it actually needs the data, thus boosting performance.

• Create a distributed partitioned view on each server. Here's what it would look like if the linked server names were LinkedServer2, 3, and 4:

   CREATE VIEW vwDistributedPartitionedView
   AS
   SELECT * FROM ThisDatabase.Owner.Sales1Q
   UNION ALL
   SELECT * FROM LinkedServer2.Database2.Owner.Sales2Q
   UNION ALL
   SELECT * FROM LinkedServer3.Database3.Owner.Sales3Q
  UNION ALL
   SELECT * FROM LinkedServer4.Database4.Owner.Sales4Q

Each of the linked servers needs its own copy of the distributed partitioned view that references the other servers, so that all of the servers "talk" to each other.

To a user selecting data from the view, it appears as though the data is being fetched from a single table in a single database on a single server. Distributed partitioned views allow you to scale out your database in a very transparent way. This is similar to the way you might have worked with linked tables in your Access database, where you could link to tables from multiple MDB files. However, the implementation in SQL Server is designed for high-end OLTP (Online Transaction Processing) and Web site databases with individual SQL statements retrieving minimal data from enormous tables. Distributed partitioned views can also be used to implement a federation of database servers. Each server is administered independently, but they cooperate to share the processing load. This allows you to scale out a set of servers to support the processing requirements of extremely large and/or high-transaction-rate databases. See the Books Online topic "Designing Federated Database Servers" for more information. If you are building a decision-support application that needs to fetch summary information from large amounts of data, then consider Analysis Services (formerly known as OLAP Services) instead.

Using Derived Tables and Nested Views

A derived table is nothing more than another SELECT statement in the FROM clause of a SELECT query. In Access, this would be a query that's based on another nested query because Access SQL doesn't allow you to embed SELECT statements in the FROM clause of a query. Derived tables and views are very similar—the main difference is that a derived table is not a saved object; it's dynamic and only exists at runtime.

The example shown in Listing 9.9 uses a derived table to put together a product list, with the product name, price, and average price for each product's category. The derived table is the SELECT statement inside the parentheses that calculates the average price per category and then is joined to the product list.

Listing 9.9 Using a Derived Table to Calculate an Average Price Per Category for a Product Price List

SELECT Products.ProductName, Products.UnitPrice, DerivedTable.CatAvg,
 Categories.CategoryName
FROM Products
INNER JOIN
 (SELECT Products.CategoryID, AVG(Products.UnitPrice) AS CatAvg
 FROM Products
 GROUP BY Products.CategoryID)
 AS DerivedTable ON DerivedTable.CategoryID = Products.CategoryID
INNER JOIN
 Categories ON Products.CategoryID = Categories.CategoryID

ORDER BY ProductName

The first few rows of the output are shown in Figure 9.9.

Figure 9.9
The output from using a derived table.

To write the same query using views, you'd need two of them. The first view would create the average, as shown in Listing 9.10.

Listing 9.10 Creating the Inner View to Calculate the Average Price Per Category

CREATE VIEW vwAvgPricePerCategory
AS
 SELECT Products.CategoryID, AVG(Products.UnitPrice) AS CatAvg
 FROM Products

 GROUP BY Products.CategoryID

The next step is to create the outer view and join it to the inner view, as shown in Listing 9.11.

Listing 9.11 Creating a Nested View

CREATE VIEW vwPriceListWithAvg
AS
 SELECT Products.ProductName, Products.UnitPrice, vwAvgPricePerCategory.CatAvg,
 Categories.CategoryName
 FROM Products
 INNER JOIN
 vwAvgPricePerCategory
 ON vwAvgPricePerCategory.CategoryID = Products.CategoryID
 INNER JOIN

 Categories ON Products.CategoryID = Categories.CategoryID

To query the view, use the following syntax:

SELECT * FROM vwPriceListWithAvg
ORDER BY ProductName

This produces the same result set shown in Figure 9.9. Derived tables and nested views can be used to do pretty much the same thing. The advantages of derived tables is that you have fewer database objects to maintain, and you can dynamically build up complex, nested Transact-SQL queries and execute them without having to create and save a view for each nested SELECT. Another way to approach solving this problem would be to populate temp tables and select from them, but in many cases derived tables will be faster or more convenient.