In SQL Server, you can create 4 types of temporary tables:
- Table variables (
DECLARE @MyTable TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
- Local temporary tables (
CREATE TABLE #MyTable) are visible only to the connection that creates it, and are deleted when the connection is closed.
- Global temporary tables (
CREATE TABLE ##MyTable) are visible to everyone, and are deleted when all connections that have referenced them have closed.
- Tempdb permanent tables (
USE tempdb CREATE TABLE MyTable) are visible to everyone, and are deleted when the server is restarted.
There are some important facts to keep in mind when using these different types of tables. You need to think about why you need the table, who will need access to the table, and how long the table needs to exist.
The table variable is helpful if you are needing a place to temporarily store data in a stored procedure. The Tempdb table is useful if you need the data for a long period of time, but you don’t care if the data is lost when the server reboots.
The Local and Global temporary tables are were it gets confusing for some people, so lets explain the difference between these two in a little detail. Local temporary tables are available to the current connection or sessions to the database for the current user and are dropped when the connection or session is closed. They are automatically deleted when connection or sessions closed. The name of local temporary table started with hash (i.e. #) sign. Global temporary tables are available to any connection or session once created, and is dropped when the last connection or session using it is closed. The name of global temporary table started with the double hash (i.e. ##) sign.
You should also remember a few things about local and global temporary tables:
- You can’t use global temporary table inside a SQL Function.
- You can’t create triggers on local and global temporary tables
- You can’t create View on local and global temporary tables.
- Temporary tables cannot be partitioned.
You can read more about creating tables from Microsoft.