temporary table vs table veriable

Question

Hello :)

I want to know which is a better option temporary table or table variable ?

Also can we create a table variable or temporary table exactly as one permanent table? e.g. by using select statement

Thanks

Warm regards
Nishu

Answer 1

Depends upon your needs. There are advantages to both and this is a common question so there are several good articles on the matter.

SqlShack
Red Gate
MSSqlTips

At the end of the day table variables are easier and faster to use. They don't require cleanup. However they are stored in memory and don't have any of the optimizations that temp tables can have. Therefore they are most useful for small sets of temp data that you need. In most cases you can build a query or CTE to do what you want but I've found that table variables are useful when you need to build up a result set from separate tables and/or that would normally require a cursor. Assuming the dataset is small it is fine.

Temp tables are necessary for larger datasets and require cleanup because they are stored separately. There are permissions needed as well. They are better suited for larger datasets, when you need to take advantage of table optimizations and if you need to store data for more than a session. However at some point you should probably use a regular table. A temp table is useful for things like data migrations in my experience.

Ultimately you should evaluate what you need the table for and determine whether the fastest/simplest approach will work or not. Of course profiling any usages of either is always recommended.

Answer 2

Advantage with table variables that any typos will be caught at compile time.

When it comes to performance, temp tables are better in most cases, although there are exceptions. I tend to use table variables if I know that it will be a matter of a couple of rows, but if there can be any number I go for a temp table.

You could develop with table variables, and once you have concluded your testing, do a find replace to use temp tables instead.

Answer 3

Hi @NishchalG ,

As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)

Some points to consider when choosing between them:

1.Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.

2.Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.

3.Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!

4.Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.

5.You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.

6.You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).

7.Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.

8.Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.

9.Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.

For more details,please refer to:
What's the difference between a temp table and table variable in SQL Server?
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
TempDB:: Table variable vs local temporary table

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.