Programming, SQL, SQL Server 2000, SQL Server 2005, SQL Server 2008, Transact-SQL, tSQL

SQL Server: Global Temporary Tables

Global Temporary tables are defined on creation by adding the double pound sign (##) as a prefix. Unlike temporary tables they are available to all SQL Server sessions until the table is destroyed/the creating session goes out of scope.

Global Temporary tables are similar to regular temporary tables in that they exist in tempdb and cause less locking and logging than permanent tables. To boost query performance when working with small data sets (under 1000 rows) it is advisable to use table variables rather than temporary tables since  table variables run within server memory and are not created in tempdb. See my article covering this for further details.

Temporary tables in general are useful when needing to work with data on a temporary basis. This is especially useful when analyzing data before writing a production level query. Although using temporary tables for short term storage may seem convenient, it is usually best practice to convert these into a single query using joins or sub-tables for performance reasons.

I usually do not use Global Temporary tables, but they do come in handy in some special instances. For example if I am running tests on a complex or long running stored procedure and want to access data set at a particular point in the procedure from a query in another tab or instance of SQL Server Management Server (SSMS), then a Global Temporary table can be useful.

Here is an example of creating a Global Temporary table:

CREATE TABLE ##TmpTbl(
    id INT,
    name VARCHAR(32)
    )
INSERT INTO  ##TmpTbl (id,name) VALUES (1,'test')
SELECT id,name FROM ##TmpTbl
DROP TABLE ##TmpTbl

So to sum up: Global Temporary tables behave very much like Local Temporary tables except that their scope is greater, spanning all SQL Server sessions. In general Global Temporary tables are not widely used, but they can be useful if you need to create and populate a work table.

References:

Advertisements

1 thought on “SQL Server: Global Temporary Tables”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s