Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 215

Transact-SQL
This type of stored procedure has been around a long time in SQL Server. It's simply a single T-SQL
statement or multiple T-SQL statements. They can take parameters and optionally return parameter
output values. They can also return resultsets. In fact, a single stored procedure can return multiple
resultsets. They may not return anything at all. Also, they can be used to pump output into a table which
can then be used for later analysis.

CLR
CLR-based stored procedures are new to SQL Server 2005. They are based on the Microsoft .NET common
language runtime (CLR). They are implemented as public static methods on a class written in a supported
.NET programming language, such as C# or Visual Basic .NET. The method type can either be void or
return an integer.

One thing to consider concerning stored procedures is which type you should use. Choosing the
correct type can make a huge difference in performance. If your stored procedure will manipulate a lot
of data, then use a T-SQL procedure. If your stored procedure contains a great deal of logic, or complex
calculations with little data access, then use a CLR procedure.

I'm not going to discuss any techniques concerning bottlenecks caused by CLR-based stored procedures.
The Wrox publication SQL Server 2005 CLR Programming tackles that topic in great detail.

ImplementingStoredProcedures
For T-SQL-based stored procedures there are some techniques to describe. One of the first things to
discuss is why you should use stored procedures at all. After all, it's quite possible (and I remember the
days before stored procedures, which left no alternative) to write an application that generates all the
necessary SQL statements in-line. Consider the steps involved when SQL Server has to manage a group
of T-SQL statements.

1.

The statements are checked for proper syntax, and the statements are translated into a query
tree. This is similar to compiling a program. The program is checked for syntax errors and
then translated into executable objects.

2.

From the query tree an execution plan is determined. This step involves optimizing the
queries, checking security, checking constraints, and incorporating any triggers in
the process.

3.

Finally, the statements are executed.

Now consider putting those same statements in a stored procedure. By doing this, the first step above is
only done once, when the procedure is created. Next, when the procedure is executed an execution plan
will be built just as in Step 2. However, subsequent calls to the procedure may be able to skip this step
because an existing execution plan will be in cache. (Cache management is covered in Chapter 9.)

So a stored procedure will save time by performing the syntax checking only once. Also, execution plans
will likely be available for subsequent calls for a given procedure, thus saving time as well. This just
leaves the actual execution of the procedure as the only step that must be repeated.