There are three temporary table object types (although two are quite similar): a table variable, prefixed with an @; a local temporary table, prefixed with a #; and a global temporary table prefixed with a ##.
Local temporary tables are visible within the session that created then, across batches, i.e. a GO batch terminator does not remove them, and they are also available in inner levels of the call stack, so if one stored proc creates a local table variable and then calls a second stored proc, the local temporary table is visible in the called stored proc.
Global temporary tables are visible across all sessions so long as the session that created it is still open and there are no active references to it.
You use a CREATE TABLE statement to create both types of temporary table.
Table variables are declared in the same way as any other variable. They are just declared to be of type TABLE, e.g. DECLARE @SimonTemp TABLE. They are visible only in the batch that created them and they get destroyed as soon as the batch terminates. They are not available within the inner call stack. The following code will fail…
DECLARE @SimonTemp TABLE (Id INT); GO SELECT * FROM @SimonTemp;
When you create a temporary table it creates it in the dbo schema of tempdb. You can create temporary tables with the same name in different sessions. This is because SQL Server actually appends a unique ID to the end of the name when it is created in tempdb. Any objects referencing the table can just use the name without the unique identifier. For example, if I create two connection and run the following in both…
CREATE TABLE #Simon (Id INT);
I can then view the objects in the tempdb and can see the following…
You can create constraints on temporary tables but beware if you give these constraints a name in code that could be run across multiple sessions. Any constraints created on the temporary table do not have a unique suffix added and are created exactly as stated. If another session tries to create the same named constraint on another temporary table it will fail as SQL Server will enforce it’s rule where object names must be unique within a schema. The best thing to do it not name the constraints and let SQL Server generate a unique internal name for them.
You can create indexes and run DDL commands once a temporary table is created, for example, to add a new column.
Table variables don’t allow you create any named constraints, indexes or perform any DDL. However you can create unnamed PRIMARY and UNIQUE constraints and these will both create unique clustered and non clustered indexes respectively.
Table variables are created in tempdb the same as temporary tables and are assigned a hex value name, e.g. #BD042834.
Like any other variable, table variables are not affected by rolling back a transaction where temporary tables are.
There are lots of blogs and comments on whether you should use table variables or temporary tables in your queries. But with most things in SQL Server it depends on the situation. The only real difference in term of performance is that tempoaray tables contain statistics whereas table variables do not.
In general if you have a small amount of data in your table or the query plan is very simple then it’s probably better to use table variables as there is no extra overhead in creating the statistics. If you have a fair bit of data or your query is selective enough for the optimiser to benefit from using an index then temporary tables are probably better.