Interview Questions and Answers
Difference Between Temporary Table and Table Variable and CTE
Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.
Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.
1. SYNTAX | |||
Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table.
|
Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it.
RESULT:
| ||
2. MODIFYING STRUCTURE | |||
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP.
Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped.
| Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly. | ||
3. STORAGE LOCATION | |||
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality. | |||
4. TRANSACTIONS | |||
Temporary Tables honor the explicit transactions defined by the user. | Table variables doesn’t participate in the explicit transactions defined by the user. | ||
5. USER DEFINED FUNCTION | |||
Temporary Tables are not allowed in User Defined Functions. | Table Variables can be used in User Defined Functions. | ||
6. INDEXES | |||
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint. | Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration. | ||
7. SCOPE | |||
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it. | Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes. |
The above listed differences are discussed in-detail with extensive list of examples in the below articles:
- Comparative Analysis of Temporary Table and Table Variable based on the aspects like Syntax, Storage Location and Transactions.
- Comparative Analysis of Temporary Table and Table Variable based on the aspects like User Defined Functions, Indexes and Scope.
Ref: https://sqlhints.com/tag/temporary-table-vs-table-variable/
This is pretty broad, but I'll give you as general an answer as I can.
CTEs...
- Are unindexable (but can use existing indexes on referenced objects)
- Cannot have constraints
- Are essentially disposable
VIEW
s - Persist only until the next query is run
- Can be recursive
- Do not have dedicated stats (rely on stats on the underlying objects)
#Temp Tables...
- Are real materialized tables that exist in tempdb
- Can be indexed
- Can have constraints
- Persist for the life of the current CONNECTION
- Can be referenced by other queries or subprocedures
- Have dedicated stats generated by the engine
REf: https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table
Ref: https://www.mytecbits.com/microsoft/sql-server/temp-table-vs-table-variable-vs-cte
Temp Table Vs Table Variable Vs CTE
Sl.# | Temp Table | Table Variable | CTE |
1 | Scope wise the local temp table is available only in the current session.
The global temp tables are available for all the sessions or the SQL Server connections.
| The scope of the table variable is just within the batch or a view or a stored procedure. | The scope of the CTE is limited to the statement which follows it. |
2 | Temp tables are stored in TempDB. | Table variables are also stored in TempDB. | The result set from CTE is not stored anywhere as that are like disposable views. |
3 | The name of the temp table can have only up to 116 characters. | The name of the table variable can have up to 128 characters. | Not Applicable |
4 | Considering the performance, it is recommenced to use temp table for storing huge data, say more than 100 rows. | Table variable is recommended for storing below 100 rows. | No such performance consideration. CTE normally used as a replacement for complex sub queries. |
5 | The structure of temp table can be altered after creating it. | The structure of table variable cannot be altered. | The definition of CTE cannot be changed during run time. |
6 | Can explicitly drop temp tables using DROP statement. | Cannot drop table variable explicitly. | Cannot be dropped. |
7 | Cannot be used in User Defined Function (UDF). | Can be used in UDF. | Can be used in UDF. |
8 | Temp tables take part in transactions. | Table variables wont take part in transactions. | Not Applicable |
9 | Index can be created on temp tables. | Index is not possible on table variables. | CTE cannot be indexed. |
10 | Can apply read lock on temp tables. | Locking is not possible in table variables. | Locking is not possible in CTE as well. |
11 | Constraints can be created on temp tables except FOREIGN KEY. | PRIMARY KEY, UNIQUE KEY and NULL are the only constraints allowed in table variable. | CTE cannot have constraints. |
Difference b/w Temp table and Temp variable:
Temp Table | TempVariable |
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users. | Scope of the Temp variables are limited up to current batch and current stored procedure. |
Temp tables can be create using Create Table and Select Into commands | Temp variables only through Declare command can’t be created using select into command. |
Temp tables can be drop through Drop Command. | We can not drop a Temp variables but we can use truncate command for temp variables. |
Name of Temp table can have maximum 116 characters. | Name of a temp variables can have MAXIMUM 128 characters. |
Temp table support foreign key concept. | Temp variable doesn’t support foreign key. |
Temp tables support transaction management. | Temp variables doesn’t support transaction management. Rollback not work for temp variables. |
Temp tables can easily handle large amount of data. | Temp variables are suitable with small amount of data. |
Ref: https://www.mytecbits.com/microsoft/sql-server/temp-table-vs-table-variable-vs-cte
Post a Comment
0 Comments