There is no simple to say if is better to use temporary table or table variable, because each of them is better for different purpose.
So let's see the differences:
Name of temporary table must start with #. If table start with ## it means global temporary table that is available for all sessions and users.
CREATE TABLE #TemporaryTable ( Id INT, FirstName VARCHAR(50), Surname VARCHAR(50) ) INSERT INTO #TemporaryTable (Id, FirstName, Surname) VALUES (1, 'FirstName1', 'Surname1'), (2, 'FirstName2', 'Surname2') SELECT * FROM #TemporaryTable DROP TABLE #TemporaryTable
DECLARE @TableVariable TABLE ( Id INT, FirstName VARCHAR(50), Surname VARCHAR(50) ) INSERT INTO @TableVariable (Id, FirstName, Surname) VALUES (1, 'FirstName1', 'Surname1'), (2, 'FirstName2', 'Surname2') SELECT * FROM @TableVariable
Can be used commands CREATE, ALTER, DROP, TRUNCATE
Table structure can't be change
Explicit transaction defined by user are supported.
Isn't affected by transaction. So if you insert data into table variable in transaction and than call rollback data stay in table variable.
You can create indexes to increase performance. When you create index on temporary table statistic file for index will be also created. You can create index implicitly or explicitly. You can create cluster and non-clustered indexes.
You can create indexes just implicitly. You can create clustered and non-clustered (from SQL server 2014), but table variable doesn't have statistic distriution for indexes so the query optimizer assumes that table variable has one row and it could be performace issue for large datasets.
Can be accesed within child batches (for example nested procedures)
Can be accessed only in within batch and scope in which are declared.
Can be used for parallel operations.
Can not be used for parallel operations. Parallel execution plan is not generated for table variable.
It is recommended to use Table variables for storing datsets up to 100 rows and Temporary tables for bigger datasets. But it always depends on situation that developer solves.
Another interesting article about this topic: