Scope of Derived Tables in SQL Server

Question

I've been looking into SQL recently and exploring a bit. in regards to Temp Tables I have discovered 3 different temp table types:

1) CREATE TABLE #TempTable

2) DECLARE TABLE @TempTable

3) SELECT * FROM (SELECT * FROM Customers) AS TempTable

Now I understand the scope behind the #TempTable and the @TempTable types, but what about the derived table as in example 3? Where does this derived table get stored? and if it is declared in 1 transaction, can a 2nd transaction access it, or is the scoping of Derived Tables that same as example 1 and 2?

Answer 1

This depends on the actual RDBMS you are using - I will assume Sybase or SQL Server (but might well be true for all)

SELECT * FROM (SELECT * FROM Customers) AS TempTable

Temptable is only available for the current SQL statement ie just the select.

SELECT * FROM Customers into tempdb..TempTable

would create a new table in the tempdb whichg would have to be explicitly dropped

Answer 2

1) CREATE TABLE #TempTable --local temp table

Local temporary tables are visible only in the current session, and can be shared between nested stored procedure calls: http://www.sommarskog.se/share_data.html#temptables

2) DECLARE TABLE @TempTable --local table variable

The scope of a local variable is the batch, stored procedure, or statement block in which it is declared. They can be passed as parameters between procedures. They are not subject to transactions and will retain all rows following a rollback.

3) SELECT * FROM (SELECT * FROM Customers) AS TempTable --derived table

is visible to the current query only

4) CREATE TABLE ##TempTable --global temp table

This differs from a #temp table in that it is visible to all processes. When the creating process ends, the table is removed (but will wait until any current activity from other processes is done).

5) CTE - common table expression

example CTE:

;WITH YourBigCTE AS
(
    big query here

)
SELECT * FROM YourTable1  WHERE ID IN (SELECT ID FROM YourBigCTE)
UNION 
SELECT * FROM YourTable2  WHERE ID IN (SELECT ID FROM YourBigCTE)

can be used multiple times within the same CTE command, even recursively, and will last for the duration of the CTE command.