When writing SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have three table options: local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.
Both local and global temporary tables are physical tables within the tempdb database, indexes can be created .Because temp tables are physical tables, you can also create a primary key on them via the CREATE TABLE command or via the ALTER TABLE command. You can use the ALTER TABLE command to add any defaults, new columns, or constraints that you need to within your code.
Create table #temp([Id] Int,[Name] Nvarchar(100)
You'll notice I prefixed the table with a #.This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server.
Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This will then create a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.
Declare @temp table([Id] Int,[Name] nvarchar(100))You'll notice I write declare because @temp is a variable of type "Table".
The another difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:
CREATE Table #temp([Id] Int,[Name] nvarchar(100)) DECLARE @temp table([Id] Int,[Name] nvarchar(100)) INSERT into #temp select 1,'India'INSERT into @temp select 1,'India'BEGIN transactionUPDATE #temp set [Name]='Hindustan'UPDATE @temp set [Name]='Hindustan'ROLLBACK transactionSELECT * from #tempSELECT * from @temp
CREATE Table #temp([Id] Int,[Name] nvarchar(100))
DECLARE @temp table([Id] Int,[Name] nvarchar(100))
INSERT into #temp select 1,'India'
INSERT into @temp select 1,'India'
UPDATE #temp set [Name]='Hindustan'
UPDATE @temp set [Name]='Hindustan'
SELECT * from #temp
SELECT * from @temp
Here the out put
After declaring our temporary table #temp and our table-variable @temp, we assign each one with the same 1,"India" . Then, we begin a transaction that updates their contents. At this point, both will now contain the same 1,"Hindustan" . But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the "old value" string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.
Which to use