ptg
864
CHAPTER 27 Creating and Managing Views in SQL Server
are searched. The same basic principles apply to indexes on views, but indexed views are
best utilized to increase performance in the following scenarios:
. Aggregations such as SUM or AVG can be precomputed and stored in the index to
minimize the potentially expensive computations during query execution.
. Large table joins can be persisted to eliminate the need to write a join when retriev-
ing the data.
. A combination of aggregations and large table joins can be stored.
The performance improvements from the aforementioned scenarios can be significant and
can justify the use of an index. The Query Optimizer can use the precomputed results
stored in the view’s index and avoid the cost of aggregating or joining the underlying
tables. Keep in mind that the Query Optimizer may still use the indexes found on the
member tables of the view instead of the index on the view. The Query Optimizer uses the
following conditions in determining whether the index on the view can be utilized:
. The tables in the query
FROM clause must be a superset of the tables in the indexed
view’s FROM clause. In other words, the query must contain all the tables in the view.
The query can contain additional tables not contained in the view.
. The join conditions in the query must be a superset of the view’s join conditions.
. The aggregate columns in the query must be derivable from a subset of the aggregate
columns in the view.
. All expressions in the query SELECT list must be derivable from the view SELECT list
or from the tables not included in the view definition.
. All columns in the query search condition predicates that belong to tables in the
view definition must appear in the GROUP BY list, the SELECT list if there is no GROUP
BY, or the same or equivalent predicate in the view definition.
NOTE
Predicting the Query Optimizer’s use of an indexed view can be complicated and
depends on the complexity of the view that is indexed and the complexity of the query

that may utilize the view. A detailed discussion of these scenarios is beyond the scope
of this chapter, but the Microsoft TechNet article “Improving Performance with SQL
Server 2005 Indexed Views” provides that detail. This article includes more than 20
examples that illustrate the use of indexed views and the conditions the Query
Optimizer uses in selecting an indexed view. As you can see from the title, this article
was written for SQL Server 2005, but the content is still relative for SQL Server 2008.
The flip side of performance with indexes (including those on views) is that there is a cost
in maintaining an index. This cost can adversely affect the performance of data modifica-
tions against objects that have these indexes. Generally speaking, indexes should not be
placed on views that have underlying data sets that are frequently updated. Caution must
Download from www.wowebook.com
ptg
865
Indexed Views
27
be exercised when placing indexes on views that support online transaction processing
(OLTP) applications. A balance must be struck between improving the performance of
database modification and improving the performance of database inquiry. Indexed views
improve database inquiry. Databases used for data warehousing and decision support are
usually the best candidates for indexed views.
The impact of data modifications on indexed views is exacerbated by the fact that the
complete result set of a view is stored in the database. When the clustered index is created
on a view, you specify the clustered index key(s) in the CREATE UNIQUE CLUSTERED INDEX
statement, but more than the columns in the key are stored in the database. As in a clus-
tered index on a base table, the B-tree structure of the clustered index contains only the
key columns, but the data rows contain all the columns in the view’s result set.
The increased space utilized by the index view is demonstrated in the following examples.
This first example creates a view and an associated index view similar to the
Adventureworks2008 Production.vProductAndDescription view used in a prior example:
result setCREATE VIEW [Production].[vProductAndDescription_2]

WITH SCHEMABINDING
AS
View (indexed or standard) to display products and
— product descriptions by language.
SELECT
p.[ProductID]
,pmx.[CultureID]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductModel] pm
ON p.[ProductModelID] = pm.[ProductModelID]
INNER JOIN [Production].[ProductModelProductDescriptionCulture] pmx
ON pm.[ProductModelID] = pmx.[ProductModelID]
INNER JOIN [Production].[ProductDescription] pd
ON pmx.[ProductDescriptionID] = pd.[ProductDescriptionID];
go
CREATE UNIQUE CLUSTERED INDEX [IX_vProductAndDescription_2]
ON [Production].[vProductAndDescription_2]
(
[CultureID] ASC,
[ProductID] ASC
)
The difference with this new view is that the result set returns only the two columns in
the clustered index; there are no additional columns in the result set.
When the new view and associated index are created, you can compare the amount of
physical storage occupied by each. The following example shows the sp_spaceused
commands for each view and the associated results:
exec sp_spaceused ‘Production.vProductAndDescription’
Download from www.wowebook.com
ptg
866

CHAPTER 27 Creating and Managing Views in SQL Server
/* results
name rows reserved data index_size unused
——————————— —— ———— ——— ————— ———
vProductAndDescription 1764 592 KB 560 KB 16 KB 16 KB
*/
exec sp_spaceused ‘Production.vProductAndDescription_2’
/* results
name rows reserved data index_size unused
——————————— —— ———— ——— ————— ———
vProductAndDescription_2 1764 64 KB 48 KB 16 KB 0 KB
*/
Take note of the reserved space and data results for each view. The view that was created
with only two result columns takes much less space than the view that has an index with
five result columns. You need to consider the overhead of storing these additional result
columns along with the index when creating the view and related index. Changes made
to any of the columns in the base tables that are part of the view results must also be
maintained for the index view as well.
Nonclustered indexes can be created on a view, and they can also provide added query
performance benefits when used properly. Typically, columns that are not part of the clus-
tered index on a view are added to the nonclustered index. Like nonclustered indexes on
tables, the nonclustered indexes on the view provide additional options for the Query
Optimizer when it is choosing the best query path. Common search arguments and
foreign key columns that may be joined in the view are common targets for nonclustered
indexes.
To Expand or Not to Expand
The expansion of a view to its base tables is a key consideration when evaluating the use
of indexes on views. The SQL Server Query Optimizer can expand a view to its base tables
or decide to utilize indexes that are found on the view itself. The selection of an index on
a view is directly related to the edition of SQL Server 2008 you are running and the expan-

sion options selected for a related query.
As mentioned earlier, the Enterprise and Developer Editions are the only editions that
allow the Query Optimizer to use an indexed view to solve queries that structurally match
the view, even if they don’t refer to the view by name. For other editions of SQL Server
2008, the view must be referenced in the query, and the NOEXPAND hint must be used as
well for the Query Optimizer to consider the index on the view. The following example
demonstrates the use of the NOEXPAND hint:
SELECT *
FROM Production.vProductAndDescription (NOEXPAND)
WHERE cultureid = ‘he’
Download from www.wowebook.com
ptg
867
Summary
27
When this example is run against the Adventureworks2008 database, the execution plan
indicates that a clustered index seek will be performed, using the index on the view. If the
NOEXPAND hint is removed from the query, the execution plan will ignore the index on the
view and return the results from the base table(s). The only exception to this is when the
Enterprise or Developer Edition is used. These editions can always consider indexed views
but may or may not choose to use them.
SQL Server also has options to force the Query Optimizer to use the expanded base tables
and ignore indexed views. The (EXPAND VIEWS) query hint ensures that SQL Server will
process a query by accessing data directly from the base tables. This option might seem
counterproductive, but it can be useful in situations in which contention exists on an
indexed view. It is also handy for testing indexed views and determining overall perfor-
mance with and without the use of indexed views.
The following example, which utilizes the same view as the previous example, demon-
strates the use of the (EXPAND VIEWS) query hint:
SELECT *

FROM Production.vProductAndDescription
WHERE cultureid = ‘he’
OPTION (EXPAND VIEWS)
The query plan in this example shows the use of the base tables, and the index on the
view is ignored. For more information on query optimization and indexes, see Chapter 34.
Summary
Views provide a broad spectrum of functionality, ranging from simple organization to
improved overall query performance. They can simplify life for developers and users by
filtering the complexity of a database. They can help organize data access and provide a
security mechanism that helps keep a database safe. Finally, they can provide performance
improvements via the use of partitioned views and indexed views that help keep your
database fast.
Some of the same benefits, including performance and security benefits, can also be
achieved through the use of stored procedures. Chapter 28, “Creating and Managing
Stored Procedures,” delves into these useful and powerful database objects.
Download from www.wowebook.com
ptg
This page intentionally left blank
Download from www.wowebook.com
ptg
CHAPTER 28
Creating and Managing
Stored Procedures
IN THIS CHAPTER
. What’s New in Creating and
Managing Stored Procedures
. Advantages of Stored
Procedures
. Creating Stored Procedures
. Executing Stored Procedures

. Deferred Name Resolution
. Viewing Stored Procedures
. Modifying Stored Procedures
. Using Input Parameters
. Using Output Parameters
. Returning Procedure Status
. Debugging Stored Procedures
Using SQL Server
Management Studio
. Using System Stored
Procedures
. Startup Procedures
A stored procedure is one or more SQL commands stored in
a database as an executable object. Stored procedures can be
called interactively, from within client application code,
from within other stored procedures, and from within trig-
gers. Parameters can be passed to and returned from stored
procedures to increase their usefulness and flexibility. A
stored procedure can also return a number of result sets and
a status code.
What’s New in Creating and
Managing Stored Procedures
Unlike SQL Server 2005 with its addition of .NET CLR
stored procedures, SQL Server 2008 doesn’t introduce any
significant changes to the creation and functionality of
stored procedures. However, one of the most welcome
enhancements is the return of the Transact-SQL (T-SQL)
debugger to SQL Server Management Studio (SSMS). System
administrators can now debug stored procedures without
having to install Visual Studio (VS). An introduction to

debugging stored procedures is provided later in this
chapter in the section “Debugging Stored Procedures Using
SQL Server Management Studio.”
One small enhancement to the functionality of stored
procedures in SQL Server 2008 is the capability to use table-
valued parameters. Table-valued parameters allow you to
pass table variables as input parameters to stored procedures
so that the contents may be accessed from within the
stored procedure. In previous versions of SQL Server, it was
not possible to access the contents of table variables outside
Download from www.wowebook.com
ptg
870
CHAPTER 28 Creating and Managing Stored Procedures
the scope in which they were declared. The “Using Table-Valued Parameters” section in
this chapter provides a description and examples on how to make use of this new feature.
One other enhancement in SQL Server 2008 is that there is no longer a maximum size for
your stored procedure source code.
Advantages of Stored Procedures
Using stored procedures provides many advantages over executing large and complex
SQL batches from client applications. Following are some of them:
. Modular programming—Subroutines and functions are often used in ordinary
3GL and 4GL languages (such as C, C++, and Microsoft Visual Basic) to break code
into smaller, more manageable pieces. The same advantages are achieved when using
stored procedures, with the difference that the stored procedure is stored in SQL
Server and can be called by any client application.
. Restricted, function-based access to tables—A user can have permission to
execute a stored procedure without having permissions to operate directly on the
underlying tables.
. Reduced network traffic—Stored procedures can consist of many individual SQL

statements but can be executed with a single statement. This allows you to reduce
the number and size of calls from the client to the server.
. Faster execution—Stored procedures’ query plans are kept in memory after the first
execution. The code doesn’t have to be reparsed and reoptimized on subsequent
executions.
. Enforced consistency—If users modify data only through stored procedures, prob-
lems that often result from ad hoc modifications (such as omitting a crucial
WHERE
clause) are eliminated.
. Reduced operator and programmer errors—Because less information is being
passed, complex tasks can be executed more easily, with less likelihood of SQL errors.
. Automating complex or sensitive transactions—If all modifications of certain
tables take place in stored procedures, you can guarantee the data integrity on those
tables.
Some of the disadvantages of using stored procedures (depending on the environment) are
as follows:
. Increase in server processing requirements—Using stored procedures can
increase the amount of processing that takes place on the server. In a large user envi-
ronment with considerable activity in the server, it may be more desirable to offload
some of the processing to the client workstation.
. Less cross-DBMS portability—Although the ANSI-99 SQL standard provides a
standard for stored procedures in database management systems (DBMSs), the for-
mat and structure are different from those of SQL Server stored procedures. These
Download from www.wowebook.com
ptg
871
Creating Stored Procedures
procedures would all have to be rewritten to be compatible with another DBMS
environment.
Should you use stored procedures? The answer is (as it often is), it depends.

If you are working in a two-tier environment, using stored procedures is often advanta-
geous. The trend is shifting to three- (or more) tier environments. In such environments,
business logic is often handled in some middle tier (possibly ActiveX objects managed by
Microsoft Transaction Server). If you operate in that type of environment, you might want
to restrict the stored procedures to performing basic data-related tasks, such as retrievals,
insertions, updates, and deletions.
NOTE
You can use s tored procedures to make a database sor t of a “black box” as far as the
developers and the application code are concerned. If all database access is managed
through stored procedures, the applications are shielded from possible changes to the
underlying database structures.
For example, one organization found the need to split one table across multiple data-
bases. By simply modifying the existing stored procedures to handle the multiple tables
and by using distributed partitioned views, the company was able to make this change
without requiring any changes to the front-end application code.
Creating Stored Procedures
To create a stored procedure, you need to give the procedure a unique name within the
schema and then write the sequence of SQL statements to be executed within the proce-
dure. Following is the basic syntax for creating stored procedures:
CREATE { PROC | PROCEDURE } [schema_name.]procedure_name [ ; number ]
[ { @parameter [ schema_name.]data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ , n ]
[ WITH { [ ENCRYPTION ]
, [ RECOMPILE ]
, [ EXECUTE_AS_Clause ]
[ , n] ]
[ FOR REPLICATION ]
AS
[BEGIN]

SQL_Statements
[ RETURN scalar_expression ]
[END]
It is good programming practice to always end a procedure with the RETURN statement and
to specify a return status other than 0 when an error condition occurs. Listing 28.1 shows
28
Download from www.wowebook.com
ptg
872
CHAPTER 28 Creating and Managing Stored Procedures
a simple stored procedure that returns book titles and the names of the authors who wrote
them.
LISTING 28.1 A Sample Stored Procedure
use bigpubs2008
go
IF EXISTS ( SELECT * FROM sys.procedures
WHERE schema_id = schema_id(‘dbo’)
AND name = N’title_authors’)
DROP PROCEDURE dbo.title_authors
GO
CREATE PROCEDURE title_authors
AS
BEGIN
SELECT a.au_lname, a.au_fname, t.title
FROM titles t INNER JOIN
titleauthor ta ON t.title_id = ta.title_id RIGHT OUTER JOIN
authors a ON ta.au_id = a.au_id
RETURN 0
END
NOTE

Unless stated otherwise, all examples in this chapter run in the context of the
bigpubs2008 database.
Creating Procedures in SSMS
To create a stored procedure in SSMS, open the object tree for the database in which you
want to create the procedure, open the Programmability folder, right-click the Stored
Procedures folder, and from the context menu, choose New Stored Procedure. SSMS opens
a new query window, populated with code that is based on a default template for stored
procedures. Listing 28.2 shows an example of the default template code for a stored proce-
dure that would be opened into a new query window.
LISTING 28.2 An Example of a New Stored Procedure Creation Script Generated by SSMS
================================================
Template generated from Template Explorer using:
Create Procedure (New Menu).SQL

Use the Specify Values for Template Parameters
command (Ctrl-Shift-M) to fill in the parameter
values below.
Download from www.wowebook.com
ptg
873
Creating Stored Procedures

This block of comments will not be included in
the definition of the procedure.
================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
=============================================

Author: <Author,,Name>
Create date: <Create Date,,>
Description: <Description,,>
=============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> =
<Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> =
<Default_Value_For_Param2, , 0>
AS
BEGIN
- SET NOCOUNT ON added to prevent extra result sets from
- interfering with SELECT statements.
SET NOCOUNT ON;
Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
You can modify the template code as necessary to set the procedure name and to specify
the parameters, return value, and procedure body. When you are finished, you can
execute the contents of the query window to create the procedure. After you have created
the procedure successfully, it is recommended that you save the source code to a file by
choosing the Save or Save As option from the File menu. This way, you can re-create the
stored procedure from the file if it is accidentally dropped from the database.
TIP
When you create a new stored procedure in SSMS, the procedure does not show up in
the Stored Procedures folder in the Object Browser unless you right-click the Stored
Procedures folder and choose the Refresh option.
28

Download from www.wowebook.com