Temporary Tables and Table Variables in SQL Server
SQL Server allows creation different types of tables in SQL Server for different purposes which include regular user-defined tables, system tables, Partitioned tables, Temporary tables, Wide tables. Each of these mentioned tables are useful for different reason and to satisfy different approaches. In this article we will try to understand about Temporary Tables in SQL Server, also we will talk about Table Variables which serves similar purpose as Temporary Tables but used for different scenarios.
Temporary Tables – There are two types of temporary tables one is a local temporary table and another is a global temporary table. Local temporary tables are visible only to their creators in the same connection to an instance of SQL Server, when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. On the other hand Global temporary tables are visible to any user and any connection after those were created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server. Local temporary table names are prefixed with a single number sign (#table_name), and global temporary table names are prefixed with a double number sign (##table_name). Temporary tables gets created under TempDB and uses space from TempDB system database. Temporary tables are lost after restart of SQL Server instance.
Creating a local temporary table
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
Inserting a value into local temporary table
INSERT INTO #MyTempTable VALUES (1);
Table Variables – Table variables are similar and an alternative of temporary tables. Table variable can be created using the special data type called TABLE. TABLE data type stores a result set for processing at a later time. TABLE data type is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.
Creating a Table Variable
DECLARE @TestTV TABLE ( Id int NOT NULL)
Inserting a value into table variable
INSERT INTO @TestTV (ID) values (1)
Differences between Temporary Tables and Table Variables – Although temporary tables and table variables sound similar and has some similar characteristics, but they also are different and is used for different purposes. Some of the differences are as mentioned below.
– Table variables have a well defined scope as they get cleared automatically at the end of the current batch of statements, where as temporary table will be visible to current session and nested stored procedures. Global Temporary table will be visible to the all the sessions.
– One can pass the table variable as a parameter to the SQL Server Stored Procedure, but temporary table cannot be passed as a parameter to the stored procedure.
– One can use the table variable inside the UDF (User Defined Function) where as temporary table cannot be used inside the UDF.
– DDL/DML operations against temporary tables are logged where are it is not logged for table variables.
– For smaller number of rows table variable yields better performance, but for larger number of rows temporary tables are preferred as we can create indexes for temporary tables which improves the performance.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.