Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 274

Table 9-10: Comparison of Table Variables and Temp Tables

Temp Table Type Advantages

Table Variables Use less logging and locking resources. Not affected by transaction

rollbacks (less work to do). Not subject to recompilation because you can't
accidently use a DDL statement to define table variables. Better for smaller
intermediary results.

#Temp Tables Can create non-clustered indexes on them for performance. Can use

statistics. Can use in sp_ExecuteSql statements. Better for large
intermediary results.

Generally, the rule is to use #temp tables for larger results because of the capability to apply indexing
to the results for performance purposes. In terms of usability, a best practice is to define your
temp table structure at the top of your query regardless of whether you use the table variables or
#temp tables. One reason is that if you are not defining your structure, you are probably creating the
#temp table using a SELECT INTO statement. The SELECT INTO method of creating #temp tables causes
some system-table level locking that you should avoid. (This happens because any time a database object
is created, like a table, it has to temporarily lock some system resources.) The other reason is that it
makes troubleshooting the query much easier to be able to convert the use of a table variable to a #temp
table. The decision of which temp table to use now or later in the life cycle of the query is then trivial
to change.

--EXAMPLE OF HOW TO CODE TO BE ABLE TO USE EITHER TABLE VARIABLES OR TEMP TABLES
DECLARE @prodId TABLE (ProductId INT) -- >> CREATE TABLE #prodId(productId Int)

INSERT INTO @ProdId -- >> INSERT INTO #ProdId

SELECT ProductId -- >> SELECT ProductId

FROM Production.Product -- >> FROM Production.Product

User-Defined Functions in SELECT Statements
Scalar user-defined functions (UDF) are great for encapsulating scalar calculations, but must be examined
carefully for how they are used in production queries. If you are not using UDFs in select statements,
but have a procedure or UDF that is called to perform computationally complex calculations or that
utilizes hierarchical or traversal-type logic, you will find that these pieces of code can be more efficiently
performed by rewriting them using SQL CLR. This topic and performance issues related to using SQL
CLR are covered in better detail in Professional SQL Server 2005 CLR Programming with Stored Procedures,
Functions, Triggers, Aggregates, and Types from Wrox.

The two biggest performance bottlenecks you may encounter are using the results of a UDF as a
filter and returning the results of a UDF in a column within a large result set. Performance issues are
particularly exacerbated when any significant querying work is being done within the UDF. For an
example look at the UDF ufnGetProductStandardCost in AW that joins the Production.Product and
Production.ProductCostHistory tables and filters the ProductCostHistory table using a date range. What
can happen in the life cycle of UDFs is that someone creates them with the expectation that they will be
used in SELECT statements for singleton or highly selective WHERE predicates. However, the appeal of