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?
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.
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
精彩评论