Temporary Table and Derived Table

Question

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

Anil Kumar · Answer

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) )

Anil Kumar · Answer

@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.

Senthilkumar · Answer

Is the question is correct? You supposed to ask the question of "Difference between Temp table and table variable"

Satyapriya Nayak · Answer

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

Temporary Table and Derived Table

Answers (4)