Search code examples
sqlsql-serverscopingtemp-tables

Scope of Derived Tables in SQL Server

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?

Solution

  • 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