Scope of Temporary Tables in SQL Server

Scope of temporary tables in SQL Server

From CREATE TABLE:

Local temporary tables are visible only in the current session

and (more importantly):

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users [sic - almost certainly this should say sessions not users]. The Database Engine does this by internally appending a numeric suffix to each local temporary table name.

Which exactly rebuts the point of whoever said that they would be shared.

---

Also, there's no need to DROP TABLE at the end of your procedure (from same link again):

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished

What is scope of temp table when called by nested stored procedure?

1. Yes, the temp table is in the scope of the connection, so the nested stored procedure (sp2) will have access to #temp table create in sp1.

2. Yes, in SQL 2008 we have ability to pass a table valued parameter (TVP) as input to a function or stored procedure. You can read more here.

Level scope in local temporary tables sql server

Picture a stored procedure called A than, in turn, calls two stored procedures, B and then C¹.

Each of these stored procedures creates a temporary table with the same name, prefixed (of course) by # (so, A creates #A) as their first action, before running any further code.

You execute the following code in query analyser:

CREATE TABLE #F (ID INT NOT NULL)
EXEC A
GO
EXEC A

Code in A can work with tables #F and #A.

Code in B can work with tables #F, #A and #B

Code in C can work with tables #F, #A and #C

Despite B and C being at the same "level" (they were both called from A), C cannot access the temp table that B created (it was destroyed when B exited).

---

¹

CREATE PROCEDURE A
AS
    CREATE TABLE #A (ID INT NOT NULL)
    EXEC B
    EXEC C

When and Why to use global temporary table over local temporary table in SQL Server

Personally, I don't see a need for global temporary tables. My inclination is to store such data in tables along with other tables. But, clearly, they do fill a need.

The answer to your first question is that global temporary tables are instance specific -- if by instance you mean a SQL Server instance. They are available to all users and all connections on the server. Global temporary tables start with the prefix ##. All references to such a table, say ##table, are to the same table (within a server instance).

The answer to the second is that SQL Server supports both global and local temporary tables. Local temporary tables are more common. All temporary tables have the nice feature that they disappear when they are automatically deleted when the server restarts and under other circumstances.

Access SQL Server temporary tables created in different scope

I think it's best to use one single script.

You can change how many characters will print in Tools > Options > Query Results > SQL Server > Results to Text - change "Maximum number of characters..." from 256 to the max (8192).

If it's bigger than 8192, then yes, printing is difficult. But you could try a different option in this case. Instead of PRINT @sql; instead use the following (with Results to Grid):

SELECT sql FROM (SELECT @sql) AS x(sql) FOR XML PATH;

Now you can click on the result, and it opens in a new query window. Well, it's an XML file window, and you can't execute it or see color-coding, and you have to ignore that it changes e.g. > to > to make it valid as XML data, but from here it's easy to eyeball if you're just trying to eyeball it. You can copy and paste it to a real query editor window and do a search and replace for the entitized characters if you like. FWIW I asked for them to make such XML windows real query windows, but this was denied:

http://connect.microsoft.com/SQLServer/feedback/details/425990/ssms-allow-same-semantics-for-xml-docs-as-query-windows

Local and global temporary tables in SQL Server

I find this explanation quite clear (it's pure copy from Technet):

There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

---

Related Topics