Table Variables

Just how awesome are table parameters in SQL Server 2008?

I would have to say, that one of the coolest new features of SQL Server 2008 is the ability to pass a table as a single parameter to a stored procedure.

While we have been able to do this in the past, by using XML to pass more than one value in, then break it apart. But this is just such a simpler, easier, more elegant solution.

Continued »

Temp Tables, Table Variables, and CTEs

There are some major differences between temp tables, table variables and common table expressions (CTEs).  Some of the big differences are:

Temp Tables vs. Table Variables

1. SQL Server does not place locks on table variables when the table variables are used.
2. Temp tables allow for multiple indexes to be created
3. Table variables allow a single index the Primary Key to be created when the table variable is declared only.
4. Temp tables can be created locally (#TableName) or globally (##TableName)
5. Table variables are destroyed as the batch is completed.
6. Temp tables can be used throughout multiple batches.
7. Temp tables can be used to hold the output of a stored procedure (temp tables will get this functionality in SQL Server 2008).

Table variables and Temp Tables vs. CTEs

1. CTEs are used after the command which creates them.
2. CTEs can be recursive within a single command (be careful because they can cause an infinite loop).
3. Table variables and Temp Tables can be used throughout the batch.
4. The command before the CTE must end with a semi-colon (;).
5. As Temp tables and table variables are tables you can insert, update and delete the data within the table.
6. CTEs can not have any indexes created on them, source tables much have indexes created on them.

If you can think of anything that I’ve missed, feel free to post them in the comments.

 Denny