Scope of Derived Tables in SQL Server
- by FailBoy
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?