Temporary elements are storage structures that exist during the database session or within the scope of a stored procedure.
But sometimes, when processing complex logic, you need a place to “park” data momentarily. It could be a list of IDs to process, or an intermediate result from a heavy calculation.
For this, SQL Server offers us two main mechanisms: Temporary Tables and Table Variables.
Which one to choose?
| Feature | Temporary Table (#Table) | Table Variable (@Table) |
|---|---|---|
| Syntax | CREATE TABLE #Name | DECLARE @Name TABLE |
| Storage | Disk (TempDB) | Memory / TempDB |
| Statistics | YES (Optimizable) | NO (Fixed estimation = 1) |
| Indexes | Full (Clustered, Non-Clustered) | Only PK/Unique (inline) |
| Scope | Session + Child Procedures | Only the current code block |
| Transactions | Rolled back with ROLLBACK | Persist after ROLLBACK |
| Ideal Use | Large volumes (>100 rows) | Small volumes, Arrays, IDs |
Temporary Tables
They are real tables physically created in the system database tempdb. The only difference from a normal table is that their name starts with a hash # and they self-destruct when the session closes.
- Scope: They are visible in the current session and also in child procedures. If you create a
#Tableand call a Stored Procedure, that procedure can read your table. - Performance: They have full Statistics and Indexes. The engine can optimize queries on them just like normal tables.
- Transactions: They participate in transactions. If you do a
ROLLBACK, the data inserted into the#Tableis undone.
Creation and use of temporary tables
To create a local temporary table, we use the standard CREATE TABLE syntax, but with the # prefix. Here’s an example:
CREATE TABLE #MyTempTable (
ID INT PRIMARY KEY,
Nombre NVARCHAR(50),
FechaCreacion DATETIME
);
Once created, you can insert data into the temporary table as you would with a normal table:
INSERT INTO #MyTempTable (ID, Nombre, FechaCreacion)
VALUES (1, 'Ejemplo', GETDATE());
And then you can query the data:
SELECT * FROM #MyTempTable;
Dropping temporary tables
Local temporary tables are automatically dropped when the session that created them closes. However, it’s good practice to drop them manually when they are no longer needed:
DROP TABLE #MyTempTable;
There is a variant with two hashes ##GlobalTable. These are visible to all users on the server.
Avoid using them, or use them with extreme caution, as it’s easy to cause naming conflicts.
Table Variables
They are structures that live primarily in memory (although they can spill to tempdb if they grow too much). They behave like any other local variable (@Number, @Text).
- Scope: They are visible only in the current batch. If you call another procedure, that procedure cannot see your variable. They die as soon as you reach a
GOor the end of the script. - Performance: They have NO Statistics. The engine always assumes they have 1 row.
- If you have 10 rows: They are very fast (less locking).
- If you have 1,000,000 rows: Performance will be terrible because the engine will choose a bad execution plan.
- Transactions: They do NOT participate in
ROLLBACK. If you open a transaction, insert into the variable, and do a rollback, the data remains in the variable.
Declaration and use of table variables
To declare a table variable, we use the DECLARE keyword followed by the variable name and the table definition. Here’s an example:
DECLARE @MyTableVariable TABLE (
ID INT PRIMARY KEY,
Nombre NVARCHAR(50),
FechaCreacion DATETIME
);
Then, you can insert data into the table variable:
INSERT INTO @MyTableVariable (ID, Nombre, FechaCreacion)
VALUES (1, 'Ejemplo', GETDATE());
And query the data:
SELECT * FROM @MyTableVariable;
Dropping Table Variables
Table variables are automatically dropped when the code block in which they are declared finishes execution. There is no need to drop them manually.
