Temp tables and Table variables are common and you probably have seen the usage of both in stored procedures, so if you are here is because you want to know what exactly is the difference between them.
Both have a very similar functionality, which is to store data temporarily, but lets see a usage example of both first.
CREATE TABLE #HOUSE_LANNISTER ( ID INT IDENTITY(1,1) PRIMARY KEY, FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50) ) INSERT INTO #HOUSE_LANNISTER VALUES('Tywin','Lannister') INSERT INTO #HOUSE_LANNISTER VALUES('Tyrion','Lannister')
@temp table variable
DECLARE @HOUSE_LANNISTER TABLE ( ID INT IDENTITY(1,1) PRIMARY KEY, FIRSTNAME VARCHAR(50), LASTNAME VARCHAR(50) ) INSERT INTO @HOUSE_LANNISTER VALUES('Tywin','Lannister') INSERT INTO @HOUSE_LANNISTER VALUES('Tyrion','Lannister')
Both examples create temporary tables and you can insert data into those, but here are the main differences:
@temp table variables, only exist in the EXECUTION time, this means only when the query is executed, as an example you can try to run the second query in parts (first the CREATE, and then the INSERT, and you will get the error “Must declare the table variable…” because it only exists in memory.
#temp tables are stored in tempdb databases, and they only exist until the SESSION is closed or expired, for example the table #temp will only exist until you close your Query window, so you can run the CREATE and INSERT queries separately with no errors.
What else is different?
Table variables cannot have Non Clustered Indexes or constraints.
What should I use?
Temp tables are generally preferred over Table variables, just because they behave more like a regular table, I would suggest the use of table variables for other simple usages like to store values from a function. Just remember Table variables depend on RAM so you shouldn’t store a big table with thousands of records if you don’t have enough memory.
Do you have any other suggestions or approaches?
Please leave them in the comments!