Views, and Then Some | Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

We have discussed views in several places in this book, starting back in Chapter 2, and it is fitting to discuss them further here on the heels of the SELECT statement and other useful SQL facilities like UNIONs and JOINs. But let’s recap before we look at some of the advanced attributes of view technology in SQL Server 2005. A view is a way of looking at an underlying table through a filter. Over the years many database books, SQL Server Books Online included, have referred to a view as a virtual table. But this is simplistic, because a view is really a specialized query that computes to create a filter over the table, thereby hiding tables, schema, and thus data not specified in the query, from direct access. A view is like a pair of sunglasses. You are not seeing a copy of the world through the lenses; you are seeing the same world but with the ultraviolet and gamma rays filtered out.

A view looks like a table, feels like a table, and works like a table with a few exceptions coming up for discussion. You build a view with a SELECT query that is processed against the underlying table. The following CREATE VIEW statement creates a simple view of the Items tables, resulting in the Items2 table:

 CREATE VIEW Items 2   AS    Select Item, Category FROM Items

The view Items2 is created in the database and can now be worked just like a table. So the statement

 SELECT * From Items2

returns the following view:

Item	Category
Cabbage	Veggie
Plums	Fruit
Squash	Veggie
Apes	Animals
Peaches	Fruit
Monkeys	Animals

The data for this table is still derived from the Items table. But the view Items2 filters out the Quantity column.

You can also think of a view as a stored query; in fact, this is probably the best explanation, and one that would be appreciated by Access converts, who no doubt still remember how to create “stored queries,” built using SQL and stored in an Access database.

Views have been in use for many years. Their primary function has been to hide tables and provide a measure of security and distance from the underlying tables. You can use views to do the work of complex queries, even to prepare the data for stored procedures, by creating joins, UNION queries, select queries, and so on. Views can also contain aggregations and other complex functionality that returns precise result sets that can then be further queried against.

They continue to do all these things and more. But of late they have begun to play a much more auspicious role in SQL Server, as a partitioning facility. While it was possible to partition views across databases in a single instance and across multiple servers, partitioned views were not updatable prior to SQ L Server 2000, so they had limited use.

Indexed Views

Indexed views allow you to store the actual view data along with the view definition in the database (as opposed to just the SELECT query). Indexed views are useful for providing faster access to view data.

We discussed indexed views, schema binding, and so on earlier, so let’s go directly to creating the index on a view. For a change, let’s create the view in Management Studio:

Drill down to the database, to the Views node into which you need to install the indexed view. Right-click New and select New View. The New View panel loads, as illustrated in Figure 16–2

Figure 16–2: New View dialog box
Create the view using the tools in Management Studio, as shown, or create it using your own T-SQL code. Check the syntax and create the view after all checks out.
To create an index, first create the view and then expand that view in Management Studio. Select the Indexes folder. At the Indexes folder you can right-click and select New Index. The New Index dialog box, illustrated in Figure 16–3, loads. Create the index and click Close. You are finished.

Figure 16–3: The New Index dialog box
You can also create the index in code. The CREATE VIEW code is as follows:

 CREATE VIEW dbo.NOOVIEW WITH SCHEMABINDING AS    SELECT OrderItem, CanShip    FROM dbo.Orders

Notice that the view is created WITH SCHEMABINDING, which will prevent the source table from being altered (at the schema level). To alter the underlying table (such as by adding a column), you would first have to destroy the view and index.

Updatable Partitioned Views

Updatable partitioned views can be used to partition data across multiple databases. They can also be used to partition data across databases on multiple servers, thus providing horizontal scalability of the highest magnitude. Partitioning tables, linking servers in federations, and building distributed partitioned views are really very easy as long as you stick to the rules and follow logic. Here’s a checklist with a couple of tips:

Build your federation in a single domain, or across transitive trusts. Intradomain federations are much more complex to set up from a security standpoint.
Use Windows Authentication to keep the security provisioning simple. If you have a complex security setup, you might have to go down to nontrusted access using a remote login.
Optimize the member tables so that each federation member is working as well as it can. Also balance the resources (it does not make sense to partition the tables and put one part on a small server and the other part on a large server).
Make sure you can properly access all the linked servers from Management Studio before you start building the partitions.
Use unique identifiers and fully qualified table names to build the views.

When views are partitioned across databases in the same instance, they are known as local partitioned views. When they are partitioned across multiple server instances, they are called federations.

The route to federated partitioned views (after linking the servers) is to first divide a table into a number of smaller tables. These so-called member tables, which contain a portion of the rows from the main table, are then installed on multiple servers-each member table in a new host database on a member of the federation. The configuration looks like this for the example I am going to show here:

Old configuration: Table1= 4,000,000 rows on Server1
New configuration: Table1a on Server1=first two million rows from Table 1; Table1b on Server2=second two million rows from Table 1

Let’s say we want to partition a view across two servers using a large order table that is being constantly updated from the Internet. One server is struggling and, as discussed in Chapter 13, has reached its scale-up limit. The business is also at a point where it cannot afford the downtime to further boost the initial cluster-pair. So the answer is to add another server cluster while the primary cluster is still getting hits and then, for a small amount of downtime, partition that data across the two servers, thereby reducing the load on the primary cluster-pair. The order of business is as follows:

Link the Servers Before we can create the partitioned view over the servers, we first have to link them. To link the servers, we have to call the system stored procedure sp_addlinkedserver to add the servers to each other’s respective catalogs. You can also use Management Studio, but there is more clutter in the dialog boxes to distract you. If you are setting up inside a single domain, you only need a few parameters. The details go into the sysservers table. Important: The linking is bidirectional, or two-way Each server must configure linked servers pointing to the remote server. The code looks like this:
```
 EXEC sp_addlinkedserver      @server='FED_1',      @srvproduct='',      @provider='SQLOLEDB',      @datasrc='/books/3/74/1/html/2/MCDC10\MCDCSQL01' GO --F ED_2 is the local node(default instance) EXEC sp_addlinkedserver      @server='FED_2',      @srvproduct='',      @provider='SQLOLEDB',      @datasrc='/books/3/74/1/html/2/MCSQL00', GO
```
Create member tables The first step, after making a full backup, is to split the primary 4,000,000-row table into two smaller tables, one containing a range of rows, say 1 through 2,000,000, and the other containing the range of rows 2,000,001 through 4,000,000. There are number of ways you can do this, such as copying the database to the other server, restoring to the new server, or using SQL Server Integration Services (SSIS). The primary node is called MCSQL00, while the secondary is called MCSQLO1.
Create the Partitioned View Before this step can be achieved, you must first link the servers and place the member tables on each respective member of the new federation. A primary key must be placed in each member table so that the view can be updated.

Create the Partitioned View

A view is now created that combines the two tables and makes them look like one again to the hungry Web browsers out on the shopping-mad Internet. Before the views can be created, however, there are several rules you have to follow, and these can be a bit tricky. See Books Online for the list of rules; you’ll find them by searching for the topic “Creating a Partitioned View.”

The T-SQL code to create the partitioned view is as follows:

 --the view on FED_1 CREATE VIEW OrdersP AS   SELECT * FROM Ordersl_2M     UNION ALL     SELECT * FROM FED_2.Customers.dbo.Orders2_4M and for FED_2: --the view on FED_2 CREATE VIEW OrdersP AS   SELECT * FROM Orders2_4M     UNION ALL     SELECT * FROM FED_1.Customers.dbo.Orders1_2M

Once the view OrdersP has been created, clients can continue to access the view without needing to know which server it resides on. In other words, the query

 UPDATE OrdersP SET CanShip = '10' WHERE OrderItem = '246'

updates the table on the primary node. On the other hand, the query

 UPDATE OrdersP SET CanShip = '53' WHERE OrderItem = '3469872'

updates the table on the secondary node, unbeknownst to the client. The distributed, updatable, partitioned view is now able to balance hits against the data across two server clusters. To check if the update succeeded, we can just query the partitioned view as follows:

 SELECT OrderItem, CanShip FROM OrdersP WHERE OrderItem = '3469872'

and the result set will be:

OrderItem	CanShip
3469872	53

Tip

Remember to provide a primary key on all source tables to the view, so that the view can be updated.

Part of the effort to partition the data is discussed in terms of linking servers, in Chapter 12, with respect to data transformation, which discusses scale-out concepts in more depth. Now to a new friend….

The Common Table Expression

Result sets on the tabulated data stream, even XML streams, are not referenceable within a T-SQL statement, direct or on the back end in a stored procedure or a trigger. As soon as a SELECT pulls the rows and columns, the data travels to the client, where it can be accessed, but you no longer have access to the data at the server. You can, as shown earlier, use temporary and derived tables and cursors to access “in-process” result sets, but these constructs are very heavy and not simple to construct and operate for small queries that return only a small number of rows.

The common table expression (CTE), introduced in SQL Server 2005, is basically a temporary result set that you create and access within the execution context of a SELECT, INSERT, UPDATE, or DELETE statement. You can also create and use it as part of the CREATE VIEW statement and use it in cases you would typically use a view. The CTE can also be created in user-defined code, such as functions, stored procedures, or triggers. The CTE lasts only for the duration of a query and is also destroyed quickly and automatically by SQL Server. The main difference between a CTE and temporary or derived tables is that a CTE can reference itself recursively. You can also reference it multiple times in the same query.

CTEs are very flexible. You can create a CTE for one part of a statement and then use the CTEs as “components” to put together complex queries, all of which can be the means of generating a final complex result set that is returned to the client. The example here shows the use of multiple CTEs to generate a result set that was impossible to achieve in a simple query on a collection of underlying tables that were very badly designed to begin with.

Structure of a CTE

A CTE is created with syntax that is similar to Visual Basic. First you name the CTE using a WITH statement. Then you add an optional column list, and a query defining the result set within the CTE. Once you define the CTE, you can access it as you would any table or view in a SELECT, INSERT, UPDATE, or DELETE statement.

Here is the basic syntax structure for the CTE:

 WITH expression_name [ ( column_name [ ,...n ] ) ]   AS ( CTE_query_definition );

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is

 SELECT *  FROM cte_name

The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, NumberOfOrders, and MaxDate) and is defined as the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson. When the statement is executed, the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson’s manager. The data for both the salesperson and the manager are returned in a single row.

 USE MODELIZE WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)   AS  (     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)      FROM Sales. SalesOrderHeader     GROUP BY SalesPersonID ); SELECT E.EmployeeID, OS. NumberOfOrders, OS. MaxDate, E.ManagerID,  OM. NumberOfOrders, OM. MaxDate FROM HumanResources. Employee AS E       JOIN Sales_CTE AS OS ON E.EmployeeID = OS. SalesPersonID       LEFT OUTER JOIN Sales_CTE AS OM       ON E.ManagerID = OM.SalesPersonID ORDER BY E.EmployeeID GO

The following code shows multiple CTEs used in a single complex query for use in a sales related report on a database so horrendously designed that intelligent query is extremely difficult:

 Use MODELIZE go declare @sDate datetime declare @eDate datetime set @sDate = '06/01/06' set @eDate = '06/13/06'; with CTE(SalesID) as (     select con1.salesid     from mcontracts_1 as con1     where con1.crdate between CONVERT(DATETIME, @sDate, 102) AND CONVERT (DATETIME,     eDate + '23:59:59', 102)     group by con1.salesid ), CTE1(SalesID, [PIF W/T]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1 left outer join mtransd_1 as tranl on con1.connum =     tran1.actref where con1.crdate between CONVERT (DATETIME, @sDate, 102) AND     CONVERT (DATETIME, @eDate + '23:59:59', 102) and     tran1.pifflag = 1 and tran1.pmtdpflag = 1 and con1.travflag = 1 and     tran1.trvflag = 1     group by con1.salesid ) , CTE2(SalesID, [$499 W/T]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1     where con1.crdate between CONVERT(DATETIME, @sDate, 102) AND CONVERT (DATETIME,     eDate + '23:59:59', 102) and     con1.pifflag = 0 and con1.travflag = 1 and con1.odownp >= '499'     group by con1.salesid ) , CTE3(SalesID, [$299 W/T]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1     where con1.crdate between CONVERT (DATETIME, @sDate, 102) AND CONVERT (DATETIME,     @eDate+'23:59:59', 102) and con1.pifflag = 0 and con1.travflag = 1 and     con1.odownp >= '299' and con1.odownp < '499'     group by con1.salesid ) , CTE4(SalesID, [PIF]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1 left outer join mtransd_1 as tranl on con1.connum =     tran1.actref     where con1.crdate between CONVERT (DATETIME, @sDate, 102) AND CONVERT (DATETIME,     eDate + '23:59:59', 102) and     tran1.pifflag = 1 and tran1.pmtdpflag = 1 and con1.travflag = 0     group by con1.salesid ) , CTE5(SalesID, [$499]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1     where con1.crdate between CONVERT (DATETIME, @sDate, 102) AND CONVERT (DATETIME,     eDate + '23:59:59', 102) and     con1.pifflag = 0 and con1.travflag = 0 and con1.odownp >= '499'     group by con1.salesid ) , CTE6(SalesID, [$299]) as (     select con1.salesid, count(*)     from mcontracts_1 as con1     where con1.crdate between CONVERT (DATETIME, @sDate, 102) AND CONVERT (DATETIME,     @eDate + '23:59:59', 102) and con1.pifflag = 0 and con1.travflag = 0 and     con1.odownp >= '299' and on1.odownp < '499'     group by con1.salesid ) , --NOW Query it SELECT CTE.SalesID, sm.smfirstnam + '  ' + sm.smlastnam, isnull (CTE1. [PIF W/T], 0)     AS [PIF W/T] , isnull (CTE2. [$499 W/T] , 0)     AS [$499 W/T] , isnull (CTE3. [$299 W/T] ,0)     AS [$299 W/T] , isnull (CTE4. [PIF] ,0)     AS [PIF] , isnull (CTE5. [$499] ,0)     AS [$499], isnull (CTE6. [$299] ,0)     AS [$299], isnull (CTE1. [PIF W/T] ,0)       + isnull (CTE2. [$499 W/T], 0)       + isnull (CTE3. [$299 W/T], 0)       + isnull (CTE4. [PIF] ,0)       + isnull (CTE5. [$499] ,0)       + isnull (CTE6. [$299] ,0) AS Total from CTE full outer join CTE1 on CTE.SalesID = CTE1. SalesID full     outer join CTE2 on CTE.SalesID = CTE2. SalesID full outer join CTE3 on     CTE.SalesID = CTE3. SalesID full outer join CTE4 on CTE.SalesID = CTE4. SalesID     full outer join CTE5 on CTE.SalesID = CTE5.SalesID full outer join CTE6 on     CTE.SalesID = CTE6. SalesID left outer join msmandb as sm on CTE.SalesID =     sm.salesid ORDER BY CTE.SalesID, sm.smfirstnam + ' ' + sm.smlastnam

While the above code demonstrates what is possible with the CTE, it would be better that the database this code was used against be redesigned from the ground up.