TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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
Reply
Answers (
4
)
0
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
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
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
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
Co- related Query
Last inserted identity