Temporary table and Derived table

hi,

Temporary tables are created in tempdb.  The name "temporary" is slightly misused, for even though these tables are instantiated in tempdb, they are created on physical disk and are even logged into the transaction log.  They act like regular tables in that you can query their data via SELECT queries and modify their data via UPDATE, INSERT, and DELETE statements.
Depending upon the scope they are destroyed.

CREATE TABLE dbo.#TempTest
(
   Id int NOT NULL,
   Name varchar(20)
)

A derived table is one that is created on-the-fly using the SELECT statement, and referenced just like a regular table or view. Derived tables exist in memory and can only be referenced by the outer SELECT in which they are created.

SELECT * FROM
(SELECT * FROM TestTable) AS a

hope this help.

Hi,

Temp and derived tables are used to store the result temporarily and it resides in the temp database. The temp table will be decided at the compilation time and derived table will be decided at the run time.

The temp table will be created by the user like manual and it will not be stored underlying database. Usually it will be created in the stored procedure for store the results and calculations temporarily.

The temp table can be set as local and global. The local is available only to the per session and global can be accessed by all the sql session.

For example
        create table #tempEmployee
        (
           EmployeeID INT IDENTITY(1,1)
          ,Name VARCHAR(100)
          ,Age INT
        )


INSERT INTO #tempEmployee(Name, Age)
SELECT Name, Age FROM Employees

The DML operation can be performed on the temp table like normal table.

The derived table will be created at the run time and it will inherit the field type and size based on the original columns of the querying table.

SELECT EmployeeID, Name, Age INTO #tempEmployeeTable FROM Employees.