Temporary tables are created in tempdb. The name “temporary” is slightly misleading, for even though the tables are instantiated in tempdb, they are backed by physical disk and are even logged into the transaction log. They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements. If created inside a stored procedure they are destroyed upon completion of the stored procedure. Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.
The syntax for creating a temporary table is identical to creating a physical table in Microsoft SQL Server with the exception of the aforementioned pound sign (#):
CREATE TABLE #TempTable ( UserID INT NOT NULL, UserName VARCHAR(10), DateofBirth DATETIME )
The syntax for creating table variables is quite similar to creating either regular or temporary tables
DECLARE @TableVariable TABLE ( UserID INT NOT NULL, UserName VARCHAR(10), DateofBirth DATETIME )
here are three major theoretical differences between temporary tables And table variables
The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism
- After declaring our temporary table #T and our table-variable @T, we assign each one with the same “old value” string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same “new value” string. 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.
- The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.
- Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement