Aditya Gaur

Aditya Gaur

  • NA
  • 1.8k
  • 1m

Temporary Table and Derived Table

May 24 2012 1:36 AM
Hello friends

What is the difference between temporary table and derived table and which one have better performance?


Thanks

Answers (4)

0
Anil Kumar

Anil Kumar

  • 192
  • 9.9k
  • 1.9m
May 24 2012 2:01 PM

A derived table is something like this -

Derived Table: 
SELECT *
FROM 
        ( SELECT innerFieldNames FROM tableName
   WHERE conditionsComeHere  ) AS derivedTableName
WHERE finalCondionComeHere
ORDER BY orderByColumnListIfRequired

Temporary Table:
And temporary tables are like normal tables and physically created in tempdb on which you can do indexing for faster searching etc. Its scope is limited to current session. And you can not have foreign key constraints on temporary tables.

You create temp table as-

CREATE TABLE #tmpTableNameAlwasyBeginsWithHash
(
        AutoID int IDENTITY,
        FieldName varchar(50)
)

So you can use these two as per your requirement. 

Table Variable:
Table variable is a special kind of variable which is like a table. You can't apply indexes on it. Transactions are not maintained. Also it is stored in memory as it is a variable. Generally it is used for less no of records and in dynamic queries. You can create table variable as-

DECLARE @tableVariableName TABLE
(
        FieldName varchar(50)
)

Accepted Answer
0
Anil Kumar

Anil Kumar

  • 192
  • 9.9k
  • 1.9m
May 26 2012 10:28 PM

@Aditya
I hope you would have idea on temporary table and derived table.


@Senthi
We can't speculate what someone should ask on the basis mostly asked questions. There is something called Derived table which is nothing but like derived from some other table using query as I have given an example. So why not someone might want to have an clear picture on temporary table and Derived table?

I did answered a bit more by explaining table variable which was not asked. I put it here to clarify on these 3 terms. 
0
Senthilkumar

Senthilkumar

  • 0
  • 10.8k
  • 4m
May 25 2012 12:15 AM
Is the question is correct?

You supposed to ask the question of "Difference between Temp table and table variable"


0
Satyapriya Nayak

Satyapriya Nayak

  • 0
  • 39.3k
  • 13.3m
May 24 2012 1:57 AM
Hi Aditya,

Please refer the below link

http://my.safaribooksonline.com/book/databases/sql/9780983336365/chapter-19-temporary-tables/derived_tables#X2ludGVybmFsX0ZsYXNoUmVhZGVyP3htbGlkPTk3ODA5ODMzMzYzNjUvNDM3

http://www.c-sharpcorner.com/UploadFile/suba.venkat/temporary-table-vs-temporary-variable-in-sql-server/

http://www.c-sharpcorner.com/UploadFile/37db1d/what-are-temporary-tables-in-sql-server/


Thanks