Two days ago, I wrote an article SQL SERVER – How to Create Table Variable and Temporary Table? It was a very basic article for beginners. However, to my surprise, I have got tremendous response to that blog post and also got quite a lot of feedback about the it. Out of all these one feedback which stood out was that I did not include discussion about Global Temporary Table. I agree that when I was writing this blog post I did not think of this, but it is never too late to write about any topic. Let us see in this blog post how to create a global temporary table?
Global Temporary Table
They are exactly the same structure as a temporary table. They are created in tempDB. There is one big difference between Global Temporary Table and a regular temporary table. The Global Temporary table can be created in any session and they can be accessed in any other session as well. Additionally, you can create them outside the scope of the stored procedure and access them inside the stored procedure. You can create and drop them in any session.
In another simple words, they serve as a temporary table which is available across multiple sessions. You can either drop them by explicit DROP command or when SQL Server services are restarted.
Let us see how we can create a global temporary table.
CREATE TABLE ##TempTable (ID INT IDENTITY(1,1))
You can query the table just like a regular table by writing select statement from any new season as well.
Now, open a new query window in SSMS and type following command.
SELECT * FROM ##TempTable
You can see that you see that you can access this table from any session and it will return you empty results.
The table will be automatically dropped when you close the connection. If you want to explicitly drop the table you can execute the following command.
DROP TABLE ##TempTable
Just remember, any table which is created with ## in the beginning is a temporary table and it is created in the temp database.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Pinal, have you come across practical examples where using global temporary tables was a good method for solving a problem (verses alternatives)? I just can’t see where I’d ever use one so am interested if someone has found a good use.
Nice one but I would like to mention that using global temp table is little bit risky. Once you create a temp table , it will be available to all sessions connected. So, if you have multiple users running application and if they connect to same DB, that table would be available to all of them and may create data corruption by overwriting in the same table.