In SQL Server, a temporary table and a table variable are two different types of temporary storage objects that can be used to store data during the execution of a query or a batch.
Here are the main differences between temporary tables and table variables:
Scope
Temporary tables are created in the tempdb database and are visible to all sessions on the same SQL Server instance. Table variables are local to the batch or stored procedure in which they are defined.
Durability
Temporary tables are durable and are not dropped automatically when they go out of scope. You need to explicitly drop them when they are no longer needed. Table variables are not durable and are automatically dropped when they go out of scope.
Indexing
Temporary tables can have indexes and statistics, which can help improve query performance. Table variables do not support indexes, but they can benefit from statistics created on the columns used in queries against them.
Memory usage
Table variables are stored in memory, and their size is limited to the available memory on the server. Temporary tables can be stored in memory or on disk, depending on their size and the memory settings of the server.
Performance
Table variables are generally faster than temporary tables for small datasets, due to their in-memory storage and lack of indexing overhead. However, for larger datasets, temporary tables can be faster due to their ability to use indexes and statistics.
Here's an example of creating a temporary table:
CREATE TABLE #TempTable (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
INSERT INTO #TempTable (ID, Name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
SELECT * FROM #TempTable;
DROP TABLE #TempTable;
And here's an example of creating a table variable:
DECLARE @TableVariable TABLE (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
INSERT INTO @TableVariable (ID, Name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
SELECT * FROM @TableVariable;
