SQL Temp Tables

Here's a quick note about SQL temp tables. In SQL Server, there are three kinds of temporary tables you can use. The first kind (my favorite) are known as table variables. They only exist in memory and are not written to disk (unless your OS is low on RAM and starts swapping). Table variable names start with an "@" just like other T-SQL variables and the syntax for one is like so:

[code]DECLARE @myTableVar TABLE 
(id INT PRIMARY KEY,
name varchar(50),
datetimeCreated datetime)[/code]

Table variables only last for the duration of that SQL statement, and are very lightweight. They are handy to use in stored procs to hold intermediate results since they can be inserted into, selected from, and deleted from just like regular tables. One draw back with table variables is that you cannot declare indexes on them. Even though they will hold a lot of records, their performance drops off after a few thousands records. Since table variables are only used by one spid, concurrency is not a concern. They use no locks, and can't be rolled back since they don't write to the transaction log. This makes them very speedy, but they come with a host of quirks such as not being able to insert into them from the results of a stored proc call, and only being able to reference them via an alias in a select statement. The second type is colloquially referred to as a temp table and uses a CREATE TABLE command instead of a DECLARE command like a regular table:

[code]CREATE TABLE #myTableVar
(id INT PRIMARY KEY,
name varchar(50),
datetimeCreated datetime)[/code]

Temp tables are written to disk in tempdb and act a lot more like regular tables as far as what you can do with them. They allow you to create indexes on them, but they only last for the duration of that database connection. NOTE: It is good form to drop them when you are through or check for their existence prior to creating them. If CF is maintaining database connections, a temp table could be still hanging around from a previous statement when that session gets reused. Temp tables write to the transaction log, but since they are still specific to one spid, you don't have to worry about another process locking them. The last type is a global temp tabe:

[code]CREATE TABLE ##myTableVar
(id INT PRIMARY KEY,
name varchar(50),
datetimeCreated datetime)[/code]

They are everything a regular temp table table is, except after created, they can be referenced by any other process in the database. They stay around as long as the original spid that created it is active or until the last running task stops using it. Frankly, I've never had a need for a global temp table. If I want to share data between multiple processes, I just use a normal table. I usually use temp tables in stored procs where I am performing multiple operations on a result set or want to get an initial result set and then update information into it before returning it for performance. There is nothing keeping you from using temp tables in a cfquery block though. Another good use for temp tables is to shorten transaction times. If you have a long procedure that places a lot of locks on your database, prepare the records you wish to update/insert/delete into temp tables, then once the heavy lifting is over, open your transaction, make your updates, and commit in one fell swoop. Watch out for concurrency issues though since the data you are updating might have been changed since you read it. (which was kind of the purpose of the locks in the first place)