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.4 shows the dialog box for 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).
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.
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.
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 differencesTOP 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 parametersa 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 TransactSQL 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.
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 similarthe 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.
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.