All you need to know about temporary tables
When to use #t or ##t or when to declare a table variable @t
we will go in depth and compare the differences and performances and some myths about temp tables
Temporary Tables are two types
Local temporary tables :
Global temporary tables :
Local Temporary Table : These tables are connection specific and session dependent (i.e. once the user or application finishes the connection or session these tables are deleted
- Theses are are denoted as #table name (# is prefixed to the table name) (table name is limited to 116 characters)
- Syntax: Create Table #temp(id int); drop table #temp
Global Temporary Table: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server
- Theses are are denoted as ##table name (## is prefixed to the table name) (table name is limited to 116 characters)
Note: Global temporary tables are visible to all user sessions.You should always check for existence of the global temporary table before creating it
Try to avoid using global temporary tables in stored procedures. If multiple users are trying to execute the procedure it will throw an error
saying ‘There is already an object named ‘## table name’ in the database.’
- Syntax: Create Table ##temp(id int); drop table ##temp
- Storage location: TempDB
- When stored procedure or batch finishes these tables are dropped
- Temporary tables can not be partitioned
- Temporary tables supports transactions
- Add or Drop constraints except Foreign keys
- Can perform any DDL statements(Alter,Drop)
- Can perform any DML statements(Select, Update, Insert, Delete)
- Can use IDENTITY column ( can insert explicit values into identity columns)
- Can create Clustered indexes and Non Clustered indexes and use statistics
- Can create any number tables(Local Temporary tables #) with same name from different sessions but make sure constraint names are different
- Columns can not use UDDT’s that are not created in TempDB you can only use native data types
- we can still use UDDT for temp tables but they should be created in TempDB as we know TempDB is recreated when sql server restarts
- you can use start up stored procedures to create UDDT’s or you can create those in model database
- Please refer this article why stored procedures are recompiled every time when it is executed when temporary tables are used in stored procedure
Re compilations Due to Certain Temporary Table Operations
Use of temporary tables in a stored procedure may cause the stored procedure to be recompiled every time the procedure is executed.
To avoid this, change the stored procedure so that it meets the following requirements:
- All statements that contain the name of a temporary table refer to a temporary table created in the same stored procedure, and not in a calling or called stored procedure, or in a string executed using the EXECUTE statement or sp_executesql stored procedure.
- All statements that contain the name of a temporary table appear syntactically after the temporary table in the stored procedure or trigger.
- There are no DECLARE CURSOR statements whose SELECT statements reference a temporary table.
- All statements that contain the name of any temporary table precede any DROP TABLE statement that references a temporary table.
- DROP TABLE statements are not needed for temporary tables created in a stored procedure. The tables are automatically dropped when the procedure has completed.
- No statements creating a temporary table (such as CREATE TABLE or SELECT… INTO) appear in a control-of-flow statement such as IF… ELSE or WHILE.
Table Variables: These temporary tables have a scope of only current batch or when execution of statements or procedure finishes the table will be cleared automatically
DECLARE @T TABLE (ID INT PRIMARY KEY CLUSTERED )
- Storage location: TempDB (most of us think that table variables are stored in memory)
- table variables can not be partitioned
- tables variable can have only clustered index with no statistics
- they don not support transactions (Rollbacks will not effect them)
- can use IDENTITY COLUMNS (but no explicit inserts)
- can use all DML statements (select,insert,update,delete)
- can not use DDL statements(alter,drop)
- can not truncate the table variable
- Although table variable is variable. It can not be assigned to another variable
- you can not create a named constraint
- and no FOREIGN KEY constraints
- create a temp table
- Wait for 10 secs
- create table variable
- see all the objects in TempDB
Similarities between temporary tables and table variable:
– Both are created in tempdb
– You can create constraint like primary key, default and check on both but the table variable has certain limitation for the default and check constrain where you can not use UDF
– Clustered indexes can be created on table variables and temporary tables
– Both are logged in the transaction log but the tempDB recovery model is SIMPLE, log will be truncated once the trasaction get complete.
Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.
– You can not create non-cluster index and statistics on table variable but you can create it on temporary table.
– You can not use DDL statement on table variable but you can use it on temporary table.
– Table variable doesn’t support transaction wheras temporary table supports
1) SQL cannot build statistics against a table variable, like it can against temporary tables. This means that the execution plan for table variables will always treat the table as if it has 1 row. Obviously, this means that it might not use the best execution plan. Note that the estimated execution plan for both table variables and temporary tables will show 1 row; however the actual execution plan for temporary tables will change once SQL has recompiled the stored procedure . Also note that if a temporary table doesn’t exist, you will get an “Invalid object name ‘<tablename>’” error when creating the estimated execution plan.
2) As mentioned previously, you cannot perform any DDL statements against a table variable. For instance, you might have a need to populate a table, and then add an index or column. In this case, you will need to use a temporary table.
3) With table variables, you cannot select into a table variable using the SELECT <columnlist> INTO <table> syntax. As demonstrated in the code above, you can do this with a temporary table.
4) With SQL 2008, you can pass a table variable to a procedure if you have defined a user-defined table type and this is the type for the parameter. You cannot do this with temporary tables, or with table variables in SQL 2000 or SQL 2005 .
5) Scope: table variables are only visible while the current batch of statements is running, and they are not visible to any nested procedures. Local temporary tables are visible to the current session once created, including nested procedures; however they will not be visible to parent procedures. Global temporary tables are visible to all sessions until dropped and all other sessions have stopped using them.
6) Collation: table variables use the collation of the current database. Temporary tables use the collation of the tempdb database. If they are not compatible, then you will need to specify the collation to use in either the queries or the table definition.
7) If you want to use a table variable in dynamic SQL, you must define the table variable in the dynamic SQL code. A temporary table that was created before calling the dynamic SQL can be used inside the dynamic SQL.
So, what should I use?
Microsoft recommends using table variables (in Reference 4). If the number of rows that you are going to be inserting into the table is very small, then use a table variable. Most of the “internet gurus” will tell you to have less than 100 records as a guideline, as this is where the lack of statistics start to mess up a query – but they will also tell you to test your specific needs against both methods. Some people will only use table variables within user-defined table functions (which require them). If you can use an index from either a PRIMARY KEY or UNIQUE constraint on a table variable, then you could get excellent performance from table variables that contain tens of thousands of records. This is primarily true if you don’t have to join the table variable to another table. When you start joining the table variable to other tables, then the cardinality error frequently results in a bad execution plan, which gets worse the more complex the query is, and the larger the row count is.
To illustrate this, see the attachment to this article. It creates both a temporary table and a table variable, and populates each of these tables with the contents of the AdventureWorks.Sales.SalesOrderDetail table. In order to get a large data size (> 1 million rows), I perform 10 inserts on each table, with each insert being in a different order. I then perform an identical query on each table, joining up against the AdventureWorks.Sales.SalesOrderHeader table and filter by the ModifiedDate field. If you examine the output statistics, you will see a remarkable difference in the number of logical reads on the joined table between the select with the temporary table versus the select with the table variable. The same data, running the same query, is producing very different performance results. Time wise (on my system) it consistently takes the table variable over 50 seconds, while the temporary table takes under 8 seconds.
If your process requires you to perform DDL statements on the table after creation, then you will need to use a temporary table.
Temp Tables and Table Variables have overlapping purposes, so there’s no real concrete answer to which you should use. For any given situation, you’ll find the optimum only by examining the advantages, disadvantages, and characteristics of each and by doing some simple performance testing. The side-by-side comparison in the following “Summary” section should make the first part of that a bit easier to accomplish.
|Feature||Table Variables||Temporary Tables|
|Scope||Current batch||Current session, nested stored procedures. Global: all sessions.|
|Usage||UDFs, Stored Procedures, Triggers, Batches.||Stored Procedures, Triggers, Batches.|
|Creation||DECLARE statement only.||CREATE TABLE statement.SELECT INTO statement.|
|Table name||Maximum 128 characters.||Maximum 116 characters.|
|Column data types||Can use user-defined data types.Can use XML collections.||User-defined data types and XML collections must be in tempdb to use.|
|Collation||String columns inherit collation from current database.||String columns inherit collation from tempdb database.|
|Indexes||Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.||Indexes can be added after the table has been created.|
|Constraints||PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.||PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.|
|Post-creation DDL (indexes, columns)||Statements are not allowed.||Statements are allowed.|
|Data insertion||INSERT statement (SQL 2000: cannot use INSERT/EXEC).||INSERT statement, including INSERT/EXEC.SELECT INTO statement.|
|Insert explicit values into identity columns (SET IDENTITY_INSERT).||The SET IDENTITY_INSERT statement is not supported.||The SET IDENTITY_INSERT statement is supported.|
|Truncate table||Not allowed.||Allowed.|
|Destruction||Automatically at the end of the batch.||Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)|
|Transactions||Last only for length of update against the table variable. Uses less than temporary tables.||Last for the length of the transaction. Uses more than table variables.|
|Stored procedure recompilations||Not applicable.||Creating temp table and data inserts cause procedure recompilations.|
|Rollbacks||Not affected (Data not rolled back).||Affected (Data is rolled back).|
|Statistics||Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.||Optimizer can create statistics on columns. Uses actual row count for generation execution plan.|
|Pass to stored procedures||SQL 2008 only, with predefined user-defined table type.||Not allowed to pass, but they are still in scope to nested procedures.|
|Explicitly named objects (indexes, constraints).||Not allowed.||Allowed, but be aware of multi-user issues.|
|Dynamic SQL||Must declare table variable inside the dynamic SQL.||Can use temporary tables created prior to calling the dynamic sql.|
/***** AUTHOR : AKHIL TEST DATE : 11/5/2011 ********************/ BEGIN TRAN DECLARE @MAXCNT INT = 100 DECLARE @MINCNT INT = 1 DECLARE @STTIME DATETIME = GETDATE() CREATE TABLE #T(ID INT , VAL VARCHAR(100)) WHILE @MINCNT < = @MAXCNT BEGIN INSERT INTO #T (ID) SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME) FROM SYS.ALL_OBJECTS UPDATE #T SET VAL = 'X' SET @MINCNT = @MINCNT +1 DELETE #T END COMMIT TRAN SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS 'DURATION FOR LOCAL TEMPORARY TABLE' BEGIN TRAN SET @MAXCNT = 100 SET @MINCNT = 1 SET @STTIME = GETDATE() CREATE TABLE ##T(ID INT, VAL VARCHAR(100)) WHILE @MINCNT < = @MAXCNT BEGIN INSERT INTO ##T (ID) SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME) FROM SYS.ALL_OBJECTS UPDATE ##T SET VAL = 'X' SET @MINCNT = @MINCNT +1 DELETE ##T END COMMIT TRAN SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS 'DURATION FOR GLOBAL TEMPORARY TABLE' BEGIN TRAN SET @MAXCNT = 100 SET @MINCNT = 1 SET @STTIME = GETDATE() DECLARE @T TABLE (ID INT , VAL VARCHAR(100)) WHILE @MINCNT < = @MAXCNT BEGIN INSERT INTO @T (ID) SELECT TOP 5000 ROW_NUMBER() OVER (ORDER BY NAME) FROM SYS.ALL_OBJECTS UPDATE @T SET VAL = 'X' SET @MINCNT = @MINCNT +1 DELETE @T END COMMIT TRAN SELECT DATEDIFF (MS,@STTIME,GETDATE()) AS 'DURATION FOR TABLE VARIABLES' Thank you Any questions please comment note : some of the contents in this post are referenced from multiple blogs (sqlserverperformance, sqlservercentral,sqldbpool)