November 4, 2011 Leave a comment
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables. In this blog I will try to explain the differences, and tell you how to use it.
Local Temp Tables
The local temp table is the most commonly used temp table. This temp tables is created with the following statement:
CREATE TABLE #TempTable (ID INT IDENTITY(1,1) NOT NULL, Description VARCHAR(10) NULL)
The table that the script above created is a temporary table that is stored on disk. To be exact, it’s stored in the TempDB. The table can only be reached from within the same scope. It will be cleaned automatically, but it’s more wise to clean it up yourself by using a DROP TABLE statement.
A session specific identifier is added to the name, to make sure that temp tables from other sessions don’t use the same name. If you query the sys.tables, you will see a table name similar to this:
The benefits of using a temp table are (amongst others): reduction of required locks (because the user is the only one who has access), less logging necessary, easy debugging (in some cases), etc. One downside is that the objectname is limited to 116 characters.
Global Temp Tables
Global temporary tables work just like local temporary tables (stored in TempDB, less locking necessary). However, they are visible to all sessions, until the session that created the object goes out of scope and all referring sessions to the object go out of scope. Also, the name of the object will remain the same as you declared it. So if you query sys.tables in the database, you will notice that (instead of the script above) the name is still TempTable.
A global temp table can be created with the following statement:
CREATE TABLE ##TempTable (ID INT IDENTITY(1,1) NOT NULL, Description VARCHAR(10) NULL)
Most of the time you will not encounter these Global temp tables “in the wild”, because permanent tables are mostly preferred.
A temporary table can also be created in memory. This has several advantages: less locking then permanent objects, performs slightly better then temporary- or global tables, not as much logging necessary, etc. Table variables are cleared automatically when the procedure, function or query goes out of scope.
There are a few disadvantages and/or demands for using table variables:
•They are allowed in SQL Server 2000 and above, and databases with Compatibility level 80 or higher
•They cannot be used for “SELECT * INTO”-queries
•They cannot be changed after declaration. They need to be recreated
•They cannot be truncated
•They cannot contain: User Defined Function (UDF), User Defined Type (UDT), Computed Column or Default Constraint
•They cannot be dropped. You need to let them go out of scope
•They cannot be created dynamically (“SELECT * INTO @TempTable”-statement), or used in a dynamic SQL statement
•They are not included in statistics by SQL Sever, and you cannot create it manually
•They don’t use parallelism when you use an “INSERT INTO”-statement
•They will always have a cardinality of 1, because the table doesn’t exist at compile time. Cardinality refers to the uniqueness of a column. The lower the cardinality, the more duplicated items in a column
•They must be referenced by an alias if you join the object in a query. If you don’t the compiler will tell you the object doesn’t exist
As always, there is no right or wrong answer. In most cases you will use the Table Variable or Local Temporary Table. The right answer in your case will be a judgment call between performance and usability. There are a few rules of thumb you can use to determine which type of object suites your needs:
•If you have less then 100 rows, generally you want to use a Table Variable
•If you need an index on your object, use a Temporary Table (Local or Global)