SQL query optimization — Temp Tables vs Table Variables

SQL query optimizationNow a days I am working close to stored procedures and SQL statements and mostly on optimization.

I came across concepts and depths of JOINs, Temporary tables, TABLE variables, Indices both clustered and non clustered and dynamic SQL.

Temporary Tables

Temporary tables are created in tempdb. The name “temporary” is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user. Multiple users could create a temp table named #TableX and any queries run simultaneously would not affect one another – they would remain autonomous transactions and the tables would remain autonomous objects. You may notice that my sample temporary table name started with a “#” sign. This is the identifier for SQL Server that it is dealing with a temporary table.

The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):

CREATE TABLE dbo.#Cars
(
Car_id int NOT NULL,
ColorCode varchar(10),
ModelName varchar(20),
Code int,
DateEntered datetime
)

Temporary tables act like physical tables in many ways. You can create indexes and statistics on temporary tables. You can also apply Data Definition Language (DDL) statements against temporary tables to add constraints, defaults, and referential integrity such as primary and foreign keys. You can also add and drop columns from temporary tables.

Table Variables


The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:

DECLARE @Cars table (
Car_id int NOT NULL,
ColorCode 
varchar(10),
ModelName 
varchar(20),
Code int,
DateEntered datetime
)

As you can see the syntax bridges local variable declaration (DECLARE @variable_name variable_data_type) and table creation (column_name, data_type, nullability). As with any other local variable in T-SQL, the table variable must be prefixed with an “@” sign. Unlike temporary or regular table objects, table variables have certain clear limitations.

  • Table variables can not have Non-Clustered Indexes
  • You can not create constraints in table variables
  • You can not create default values on table variable columns
  • Statistics can not be created against table variables

Similarities with temporary tables include:

  • Instantiated in tempdb
  • Clustered indexes can be created on table variables and temporary tables
  • Both are logged in the transaction log
  • 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.
Here are some of the very fine sources on tests of the both:
Conclusion:
1. Use temporary tables whenever you have huge data and lots of modifications to be done. Use it also when indexes can improve the performance.
2. Use table variable when you just want to fill the table and return it.

One thought on “SQL query optimization — Temp Tables vs Table Variables

Leave a Reply

Your email address will not be published. Required fields are marked *