432
Part IV: Programming with T-SQL in SQL Server
The Performance Myth
Many DBAs and developers, especially those who worked with SQL Server prior to SQL Server 2000
will say that stored procedures provide a performance benefi t because the execution plan is cached
and stored for reuse. The phrase “precompiled” might also come up. This is no longer true. As of SQL
Server 2000, all execution plans are cached, regardless of whether they’re the result of inline T-SQL or
a stored procedure call. The name of the cache area was changed from Procedure Cache to Plan Cache
with SQL Server 2000 to properly refl ect what was actually happening. There are some performance
benefi ts to stored procedures, but a cached execution plan is no longer one of them.Many different philosophies exist on the appropriate use of stored procedures, but they
all come down to two basic ideas: dumb stored procedures versus smart stored procedures.
Organizational policy and team coding standards might dictate in advance how stored pro-
cedures are implemented, but it is worth looking at these two approaches.Some organizations require that all T-SQL called by an application must be encapsulated
within a stored procedure. This results in a high number of stored procedures, many con-
sisting of basic Create, Retrieve, Update, and Delete (CRUD) operations. These are “dumb”
stored procedures. On the other hand, some organizations implement a stored procedure
only when a process calls for multiple T-SQL statements, multiple result sets, complex
processing, or parameterization. These are “smart” stored procedures. The goals of both
approaches are similar: maintainability, security, performance, and consistency. Depending
on how an organization and its application is structured and built, either approach may be
appropriate. The key is consistency. Decide upfront how to implement stored procedures
and stick to it.Managing Stored Procedures
The actual management of stored procedures is simple compared to the logic within them.
When you know the basic facts and syntax, managing stored procedures shouldn’t present
any problems.All code samples use the AdventureWorks2012 database, available at
http://msftdbprodsamples.codeplex.com/.Create, Alter, and Drop
Adding, modifying, and removing a stored procedure within the database is a straightfor-
ward process. You can use three basic Data Defi nition Language (DDL) commands: CREATE,
ALTER, and DROP.c17.indd 432c17.indd 432 7/30/2012 5:39:34 PM7/30/2012 5:39:34 PM
http://www.it-ebooks.info