Problem

You have several processes that all need to run the same query. This query needs to return multiple columns from multiple tables for a specific product category. For example, you need to return product transaction history data for all bikes.

Solution

Create a view that uses just the necessary columns, joined to the proper tables, and filtered for Bikes. Here’s an example:

CREATE VIEW dbo.v_Product_TransactionHistory
AS
SELECT p.Name AS ProductName,
        p.ProductNumber,
        pc.Name AS ProductCategory,
        ps.Name AS ProductSubCategory,
        pm.Name AS ProductModel,
        th.TransactionID,
        th.ReferenceOrderID,
        th.ReferenceOrderLineID,
        th.TransactionDate,
        th.TransactionType,
        th.Quantity,
        th.ActualCost,
        th.Quantity * th.ActualCost AS ExtendedPrice
 FROM Production.TransactionHistory th
        INNER JOIN Production.Product p
         ON th.ProductID = p.ProductID
        INNER JOIN Production.ProductModel pm
         ON pm.ProductModelID = p.ProductModelID
        INNER JOIN Production.ProductSubcategory ps
         ON ps.ProductSubcategoryID = p.ProductSubcategoryID
        INNER JOIN Production.ProductCategory pc
         ON pc.ProductCategoryID = ps.ProductCategoryID
WHERE pc.Name = 'Bikes';
GO

How It Works

A view is created that retrieves multiple columns from multiple tables for the product category of Bikes. You can now query this data with this SELECT statement:

SELECT ProductName,
        ProductNumber,
        ReferenceOrderID,
        ActualCost
FROM dbo.v_Product_TransactionHistory
ORDER BY ProductName;

This returns the following (abridged) result set:

In this case, the view benefits anyone needing to write a query to access this data, because the user doesn’t need to specify the many table joins each time the query is written.

The view definition also used column aliases, using ProductName instead of just Name, making the column name unambiguous and reducing the possible confusion with other columns called Name. Qualifying what data is returned from the view in the WHERE clause also allows you to restrict the data that the query writer can see—in this case only letting the query writer reference products of a specific product category.

A view is also a good example of code reuse. Multiple processes can utilize this view for performing their actions. If at a later time it is decided that Bicycles should be included along with Bikes, all that is necessary is for the WHERE clause to be modified to include Bicycles, and all of the processes will now start returning bicycles as well as bikes.

Problem

You have a process that needs to know the definition of a view.

Solution

Utilize the sys.sql_modules system catalog view or the OBJECT_DEFINITION function. Here’s an example:

SELECT definition
FROM sys.sql_modules AS sm
WHERE object_id = OBJECT_ID('dbo.v_Product_TransactionHistory'),
 
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.v_Product_TransactionHistory'));

How It Works

Both of these queries return the following result set, which is the definition of the specified view:

CREATE VIEW dbo.v_Product_TransactionHistory
AS
SELECT p.Name AS ProductName,
        p.ProductNumber,
        pc.Name AS ProductCategory,
        ps.Name AS ProductSubCategory,
        pm.Name AS ProductModel,
        th.TransactionID,
        th.ReferenceOrderID,
        th.ReferenceOrderLineID,
        th.TransactionDate,
        th.TransactionType,
        th.Quantity,
        th.ActualCost
FROM Production.TransactionHistory th
        INNER JOIN Production.Product p
         ON th.ProductID = p.ProductID
        INNER JOIN Production.ProductModel pm
         ON pm.ProductModelID = p.ProductModelID
        INNER JOIN Production.ProductSubcategory ps
         ON ps.ProductSubcategoryID = p.ProductSubcategoryID
        INNER JOIN Production.ProductCategory pc
         ON pc.ProductCategoryID = ps.ProductCategoryID
WHERE pc.Name = 'Bikes';

Both of these methods allow you to view the procedural code of all objects, including views, triggers, stored procedures, and functions. If the object is defined as encrypted or if the user does not have permission for this object, a NULL will be returned.

Problem

You need to know the names of all of the views in a database.

Solution

Query the sys.views or sys.objects system catalog view. Here’s an example:

SELECT OBJECT_SCHEMA_NAME(v.object_id) AS SchemaName,
        v.name
FROM sys.views AS v ;
 
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS SchemaName,
        o.name
FROM sys.objects AS o
WHERE type = 'V' ;

How It Works

Both of these queries query a system catalog view to return the metadata for the name and schema for all views in the database. Each query returns the following result set:

Problem

You need to know the names of all the columns in a view.

Solution

Query the sys.columns system catalog view. Here’s an example:

SELECT name,
        column_id
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.v_Product_TransactionHistory'),

How It Works

In this query, the metadata of the names and column positions for the view are returned in the following result set:

Problem

You have modified the structure of one of the tables used in a view, and now the view is returning incorrect results.

Solution

Refresh the definition of the view by utilizing the sp_refreshview or sys.sp_refreshsqlmodule system stored procedure. Here’s an example:

EXECUTE dbo.sp_refreshview N'dbo.v_Product_TransactionHistory';
EXECUTE sys.sp_refreshsqlmodule @name = N'dbo.v_Product_TransactionHistory';

How It Works

When table objects referenced by a view are changed, the view’s metadata can become outdated. For instance, if you change the width of a column in a table, this change may not be reflected in the view until the metadata has been refreshed. You can refresh the view’s metadata with either the dbo.sp_refreshview or sys.sp_refreshsqlmodule system stored procedure.

Problem

You need to make a change to the definition of a view.

Solution

Utilize the ALTER VIEW statement to change the definition of a view.

How It Works

The ALTER VIEW statement allows you to change the definition of a view by specifying a new definition. This is performed by first removing the existing definition from the system catalogs (including any indexes if it is an indexed view) and then adding the new definition. For example, to change the view created in Recipe 14-1 to include Bicycles, the following script would be executed:

ALTER VIEW dbo.v_Product_TransactionHistory
AS
SELECT p.Name AS ProductName,
        p.ProductNumber,
        pc.Name AS ProductCategory,
        ps.Name AS ProductSubCategory,
        pm.Name AS ProductModel,
        th.TransactionID,
        th.ReferenceOrderID,
        th.ReferenceOrderLineID,
        th.TransactionDate,
        th.TransactionType,
        th.Quantity,
        th.ActualCost,
        th.Quantity * th.ActualCost AS ExtendedPrice
FROM Production.TransactionHistory th
        INNER JOIN Production.Product p
         ON th.ProductID = p.ProductID
        INNER JOIN Production.ProductModel pm
         ON pm.ProductModelID = p.ProductModelID
        INNER JOIN Production.ProductSubcategory ps
         ON ps.ProductSubcategoryID = p.ProductSubcategoryID
        INNER JOIN Production.ProductCategory pc
         ON pc.ProductCategoryID = ps.ProductCategoryID
WHERE pc.Name IN ('Bikes', 'Bicycles');
GO

This query returns the following (abridged) result set:

Since there are no entries (yet) in the Production.ProductCategory table with a name of Bicycle, the same number of rows is returned.

Problem

You need to make data modifications to a table, but you have access to the table only through a view.

Solution

Provided that you are modifying columns from one base table, you can issue INSERT, UPDATE, DELETE, and MERGE statements against a view.

How It Works

INSERT, UPDATE, DELETE, and MERGE statements can be issued against a view, with the following provisions:

• Any modifications must reference columns from only one base table.
• The columns being modified in the view must directly reference the underlying data in the table. The columns cannot be derived in any way, such as through the following:
• An aggregate function
• computed column
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select statement of the view together with the WITH CHECK OPTION clause.

Generally, the database engine must be able to unambiguously trace modifications from the view definition to one base table.

In the view created in Recipe 14-1, the query references multiple tables and has a calculated column. To examine the results for ReferenceOrderId = 53463, the following query is issued:

SELECT ProductName,
        ProductNumber,
        ReferenceOrderID,
        Quantity,
        ActualCost,
        ExtendedPrice
FROM dbo.v_Product_TransactionHistory
WHERE ReferenceOrderID = 53463
ORDER BY ProductName;

This query returns the following result set:

It is decided to update the quantity of this record to 3, so the following query is issued:

UPDATE dbo.v_Product_TransactionHistory
SET Quantity = 3
WHERE ReferenceOrderID = 53463;

Running the previous query now returns the following result set:

What this example demonstrates is that even though the view is created against multiple tables, as long as the update is against just one of the tables, the data exposed by the view can be updated. Now, if it wasn’t realized that the ExtendedPrice column is a calculated column and the UPDATE statement tries to update that column also with this query:

UPDATE dbo.v_Product_TransactionHistory
SET Quantity = 3,
        ExtendedPrice = 4957.1784
WHERE ReferenceOrderID = 53463;

then the following error is generated:

Msg 4406, Level 16, State 1, Line 12
Update or insert of view or function 'dbo.v_Product_TransactionHistory' failed because it contains a derived or constant field.

Problem

You have a SQL Server–based commercial application, and you need to hide the definition of the view.

Solution

Encrypt the view with the WITH ENCRYPTION clause of the view definition.

How It Works

The WITH ENCRYPTION clause in the CREATE VIEW and ALTER VIEW statements allow you to encrypt the Transact-SQL code of the view. Once encrypted, you can no longer view the definition in the sys.sql_modules catalog view or the OBJECT_DEFINITION system function.

Software vendors that use SQL Server as the back-end database management system often encrypt the Transact-SQL code in order to prevent tampering or reverse-engineering from clients or competitors. If you use encryption, be sure to save the original, unencrypted definition so that you can make modifications to it in the future.

The following example creates an encrypted view:

CREATE VIEW dbo.v_Product_TopTenListPrice
WITH ENCRYPTION
AS
SELECT TOP 10
        p.Name,
        p.ProductNumber,
        p.ListPrice
FROM Production.Product p
ORDER BY p.ListPrice DESC;
GO

When the following queries are run to view the definition (as shown in Recipe 14-2):

SELECT definition
FROM sys.sql_modules AS sm
WHERE object_id = OBJECT_ID('dbo.v_Product_TopTenListPrice'),

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.v_Product_TopTenListPrice')) AS definition;

the following results are returned:

definition
---------
NULL
 
definition
---------
NULL

Problem

You need to optimize the performance of a view that is defined against multiple tables, all of which have infrequent data modifications.

Solution

Create an index on the view.

How It Works

Indexed views allow you to materialize the results of the view as a physical object, similar to a regular table and associated indexes. This allows the SQL Server query optimizer to retrieve results from a single physical area instead of having to process the view definition query each time it is called.

To create an indexed view, you are required to use the WITH SCHEMABINDING option, which binds the view to the schema of the underlying tables. This prevents any changes in the base table that would impact the view definition. The WITH SCHEMABINDING option also adds additional requirements to the view’s SELECT definition. Object references in a schema-bound view must include the two-part schema.object naming convention, and all referenced objects have to be located in the same database.

In the following example, a view is created using the SCHEMABINDING option:

CREATE VIEW dbo.v_Product_Sales_By_LineTotal
WITH SCHEMABINDING
AS
SELECT p.ProductID,
        p.Name AS ProductName,
        SUM(LineTotal) AS LineTotalByProduct,
        COUNT_BIG(*) AS LineItems
FROM Sales.SalesOrderDetail s
        INNER JOIN Production.Product p
         ON s.ProductID = p.ProductID
GROUP BY p.ProductID,
        p.Name;
GO

Before creating an index, we’ll demonstrate querying the regular view, returning the query I/O cost statistics using the SET STATISTICS IO command:

SET STATISTICS IO ON;
GO
 
SELECT TOP 5
        ProductName,
        LineTotalByProduct
FROM dbo.v_Product_Sales_By_LineTotal
ORDER BY LineTotalByProduct DESC ;
GO

This query produces the following result set:

ProductName	LineTotalByProduct
-----------------------	------------------
Mountain-200 Black, 38	4400592.800400
Mountain-200 Black, 42	4009494.761841
Mountain-200 Silver, 38	3693678.025272
Mountain-200 Silver, 42	3438478.860423
Mountain-200 Silver, 46	3434256.941928

This query also returns the following I/O information reporting the various activities against the tables involved in the query that was run (if you are following along with the recipe, keep in mind that unless your system is identical in every way to mine, then you will probably have different statistic values returned from the following statistics):

Table 'Product'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Now we can add the clustered and nonclustered indexes to this view:

CREATE UNIQUE CLUSTERED INDEX UCI_v_Product_Sales_By_LineTotal
ON dbo.v_Product_Sales_By_LineTotal (ProductID);
GO
CREATE NONCLUSTERED INDEX NI_v_Product_Sales_By_LineTotal
ON dbo.v_Product_Sales_By_LineTotal (ProductName);
GO

When the previous query is now run, the same results are returned. However, the statistics have changed:

Table 'v_Product_Sales_By_LineTotal'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Let’s step through the process. First, a view is created that utilizes the WITH SCHEMABINDING clause.

CREATE VIEW dbo.v_Product_Sales_By_LineTotal
WITH SCHEMABINDING
AS

The rest of the view is a regular SELECT statement that sums the LineTotal column and counts the number of records for the ProductID and Name grouping.

SELECT p.ProductID,
        p.Name AS ProductName,
        SUM(LineTotal) AS LineTotalByProduct,
        COUNT_BIG(*) AS LineItems
FROM Sales.SalesOrderDetail s
        INNER JOIN Production.Product p
         ON s.ProductID = p.ProductID
GROUP BY p.ProductID,
        p.Name;

Notice that the query referenced the COUNT_BIG aggregate function. COUNT_BIG is required in order for SQL Server to maintain the number of rows in each group within the indexed view. Once the view is successfully created with SCHEMABINDING, a unique clustered index can then be created on it.

CREATE UNIQUE CLUSTERED INDEX UCI_v_Product_Sales_By_LineTotal
ON dbo.v_Product_Sales_By_LineTotal (ProductID);
GO

To index a view, you must first create a unique clustered index on it. This process materializes the view, making it have a physical existence instead of its normal virtual existence. Once this index has been built, the view data is stored in much the same way as a clustered index for a table is stored. After a clustered index is created, you can also create additional nonclustered indexes, as you would for a regular table. In the example, a nonclustered index is created on the ProductName column of the indexed view.

CREATE NONCLUSTERED INDEX NI_v_Product_Sales_By_LineTotal
ON dbo.v_Product_Sales_By_LineTotal (ProductName);
GO

Once a view is indexed, view indexes can then be used by SQL Server Enterprise Edition whenever the view or underlying tables are referenced in a query. The SET STATISTICS IO command was used to demonstrate how SQL Server performs the data page retrieval both before and after the view was indexed.

Indexed views can provide performance benefits for relatively static data. Frequently updated base tables, on the other hand, are not an ideal choice for being referenced in an indexed view, because the updates will also cause frequent updates to the view’s indexes, potentially reducing the benefit of any query performance gained. This is a trade-off between data modification speed and query speed.

Also, although indexed views can be created using any edition of SQL Server, they will be automatically considered during the query execution if you are using Enterprise Edition. To make sure SQL Server uses it in other editions, you need to use the view hint NOEXPAND, which is reviewed in the next recipe.

Problem

You have a table that has an extremely large row count and is causing performance issues. Only the current month’s data is actively changing. You want to reduce the size of this table in order to improve the performance of DML operations; yet you still want to keep all of the rows in the table for your queries and to keep the same object name in your queries.

Solution

Split the table into multiple tables, and create a partitioned view with the same name as the original table name.

How It Works

Partitioned views allow you to create a single logical representation (view) of two or more horizontally partitioned tables that are located on the same SQL Server instance.

To set up a partitioned view, a large table is split into smaller tables based on a range of values defined in a CHECK constraint. This CHECK constraint ensures that each smaller table holds unique data that cannot be stored in the other tables. The partitioned view is then created using a UNION ALL to join each smaller table into a single result set.

The performance benefit is realized when a query is executed against the partitioned view. If the view is partitioned by a date range, for example, and a query is used to return rows that are stored only in a single table of the partition, SQL Server is smart enough to search only that one partition instead of all tables in the partitioned view.

To demonstrate partitioned views, let’s work with the fictional company MegaCorp. They want to track all of the hits to their web site. Anticipating a large amount of traffic, a WebHits table is created for each month in the TSQLRecipe_A database.

IF DB_ID('TSQLRecipe_A') IS NULL
  CREATE DATABASE TSQLRecipe_A;
GO
USE TSQLRecipe_A;
GO
CREATE TABLE dbo.WebHits_201201
  (
        HitDt DATETIME
         NOT NULL
         CONSTRAINT PK__WebHits_201201 PRIMARY KEY
         CONSTRAINT CK__WebHits_201201__HitDt
         CHECK (HitDt >= '2012-01-01'
         AND HitDt < '2012-02-01'),
        WebSite VARCHAR(20) NOT NULL
  );
GO
CREATE TABLE dbo.WebHits_201202
  (
        HitDt DATETIME
         NOT NULL
         CONSTRAINT PK__WebHits_201202 PRIMARY KEY
         CONSTRAINT CK__WebHits_201202__HitDt
         CHECK (HitDt >= '2012-02-01'
         AND HitDt < '2012-03-01'),
        WebSite VARCHAR(20) NOT NULL
  );
GO
CREATE TABLE dbo.WebHits_201203
  (
        HitDt DATETIME
         NOT NULL
         CONSTRAINT PK__WebHits_201203 PRIMARY KEY
         CONSTRAINT CK__WebHits_201203__HitDt
         CHECK (HitDt >= '2012-03-01'
         AND HitDt < '2012-04-01'),
        WebSite VARCHAR(20) NOT NULL
  );
GO

Now that the tables are set up, it is time to create the partitioned view. There are three areas that have specific requirements that need to be met in order to create a partitioned view:

1. The SELECT list
   • All columns in the affected tables need to be selected in the column list of the view.
   • The columns in the same ordinal position need to be of the same type, including the collation.
   • At least one of these columns must appear in the select list in the same ordinal position. This column (in each table) must be defined to have a check constraint such that any specified value for that column can satisfy at most only one of the constraints from the involved tables. This column is known as the partitioning column, and it may have a different name in each of the tables. The constraints need to be enabled and trusted.

     The same column cannot be used multiple times in the select list.

2. The partitioning column
   • The partitioning column is part of the PRIMARY KEY constraint for the table.
   • It cannot be a computed, identity, default, or timestamp column.
   • There can be only one check constraint on the partitioning column.
3. The underlying tables
   • The same table cannot appear more than once in the set of tables in the view.
   • The underlying tables cannot have indexes on computed columns.
   • The underlying tables need to have their PRIMARY KEY constraints on the same number of columns.
   • All underlying tables need to have the same ANSI padding setting.

Notice the check constraints on the HitDt columns. These check constraints create the partitioning column necessary for the view.

For the partitioned view to be able to update data in the underlying tables, the following conditions must be met:

• INSERT statements must supply values for all the columns in the view, even if the underlying tables have a default constraint or they allow null values. If the column does have a default definition, the INSERT statement cannot use the DEFAULT keyword for this column.
• The value being inserted into the partitioning column should satisfy at least one of the underlying constraints.
• UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause.
• Columns in the view that are identity columns in any underlying table cannot be modified by either the INSERT or UPDATE statement.
• If any underlying table contains a TIMESTAMP column, the data cannot be modified by using an UPDATE or INSERT statement.
• None of the underlying tables can contain a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULTor ON DELETE CASCADE/SET NULL/SET DEFAULT constraint.
• INSERT, UPDATE, and DELETE actions are not allowed if there is a self-join with the same view or any of the underlying tables in the statement.
• Bulk importing data from the bcp utility or the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements is not supported.

Considering all of the previous requirements, the following view is created:

CREATE VIEW dbo.WebHits
AS
SELECT HitDt,
        WebSite
FROM dbo.WebHits_201201
UNION ALL
SELECT HitDt,
        WebSite
FROM dbo.WebHits_201202
UNION ALL
SELECT HitDt,
        WebSite
FROM dbo.WebHits_201203;
GO

Next, some records are inserted into the view. If everything works correctly, they will be inserted into their underlying tables.

INSERT INTO dbo.WebHits
        (HitDt,
         WebSite)
VALUES ('2012-01-15T13:22:18.456',
         'MegaCorp'),
INSERT INTO dbo.WebHits
        (HitDt,
         WebSite)
VALUES ('2012-02-15T13:22:18.456',
         'MegaCorp'),
INSERT INTO dbo.WebHits
        (HitDt,
         WebSite)
VALUES ('2012-03-15T13:22:18.456',
         'MegaCorp'),
GO

To check whether the records are in the proper tables, run the following queries:

SELECT *
FROM dbo.WebHits_201201;

This query returns the following result set:

HitDt	WebSite
-----------------------	--------------------
2012-01-15 13:22:18.457	MegaCorp
SELECT *
FROM dbo.WebHits_201202;

This query returns the following result set:

HitDt	WebSite
-----------------------	--------------------
2012-02-15 13:22:18.457	MegaCorp
SELECT *
FROM dbo.WebHits_201203;

This query returns the following result set:

HitDt WebSite
-----------------------	--------------------
2012-03-15 13:22:18.457	MegaCorp

Now that you can see the data is going into the proper tables, let’s look at how SQL Server retrieves data.

SET STATISTICS IO ON;
GO
SELECT *
FROM dbo.WebHits
WHERE HitDt >= '2012-02-01'
        AND HitDt < '2012-03-01';

This query returns the following result set:

HitDt WebSite
-----------------------	--------------------
2012-02-15 13:22:18.457	MegaCorp
 
Table 'WebHits_201202'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If SELECT statements that are referencing the view specify a search condition, the query optimizer uses the check constraints to determine which underlying tables contain the data, and the execution plan is built referencing only those tables. In the previous query, even though the query is being run against the view, the check constrains on the underlying tables tell SQL Server that for the date range being selected, only the WebHits_201202 table will need to be accessed to retrieve data. When the execution plan is built and executed, this is exactly what happens.

There are several benefits to utilizing partitioned views. These include the following:

• Allowing easier archiving of data, without extra transaction log activity. (You don’t need to move records from one table to another to archive or even to just delete the records. Moving records would require transaction log entries for the table both being deleted from and being inserted into, potentially growing the transaction log to an undesired size.)
• Assuming that data is modified only on the current month’s underlying table, only the indexes on that table will need maintenance activities. Since the size of the indexes will be much smaller, the time required for the index maintenance will be shorter.
• Queries can run against a smaller number of records.

Problem

You need to spread the workload of a table across multiple servers.

Solution

Create a table on each instance of SQL Server, and create a distributed partitioned view on each server to access the data from all of the servers.

How It Works

Distributed partitioned views allow you to create a single logical representation (view) of two or more horizontally partitioned tables that are located on multiple SQL Server instances. Distributed partitioned views have a few more conditions to them than partitioned views; however, the only difference between them is whether all of the underlying tables are on the same SQL Server instance. The additional conditions for distributed partitioned views are as follows:

• distributed transaction will be initiated in order to guarantee atomicity across all instances affected by the update.
• SET XACT_ABORT ON should be run in order for INSERT, UPDATE, and DELETE statements to work.
• Any smallmoney and smalldatetime columns in remote tables will be mapped as money and datetime. Therefore, the corresponding columns in the same ordinal position in the select list in the local tables must be money or datetime data types.
• Linked servers utilized in the partitioned view cannot be a loopback linked server (the linked server points to the same instance of SQL Server).

In a distributed partitioned view, each server has a view that references its local table(s), and the remote tables are referenced in a four-part naming schema (Server.Database.Schema.Table) utilizing a linked server.