What is the difference between temporary table and derived table and which one have better performance?
Thanks
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Anil KumarPosted May 24, 2012, 2:01 PM
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)
)
Anil KumarPosted May 26, 2012, 10:28 PM
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.
SenthilkumarPosted May 25, 2012, 12:15 AM
You supposed to ask the question of "Difference between Temp table and table variable"
Satyapriya NayakPosted May 24, 2012, 1:57 AM
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