With clause (CTE) vs Temporary table

Question posted in Computer Software, Computer\IT Services on 11 2009
Rate question difficulty level 0 Votes
With clause or temporary table?
 
 
1 Answer
 
A CTE in this sense is a derived table. 
Derived tables can be a lot more performance than temporary tables.  This is because temporary tables by default
do not have indexing and you have to spare the expense of creating the table,
populating the data, and creating the index.  The optimizer is able to use all
the existing statistics and indexing available for a derived table/etc, which
makes it a more viable solution, in my opinion.
NOTE: the optimizer can use
statistics and optimize a temporary table; however, cannot do so for a table
variable. The best way to test is the see how each method performs, but I would
say the etc/derived table is probably the better method here.

credits to : http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d2ab0c7c-e991-4e85-9c21-827e2d12ef2f

11/03/2009
 
 
Add an answer*
 
Email
Location: United States
db sql
Now hiring!
Software Development Engineer (SDE) Job
Microsoft 
Redmond, WA
Category: Software Engineering: Development Location: United States, WA, Redmond Job ID: 722492 18325 Division: ... Our software is responsible for managing hardware and software from the time it is ordered until it is retired.
---------------------------
Senior IT System Analyst
UnitedHealth Group 
Basking Ridge, NJ
Group, Enterprise Technology Services Group and the IT Business Services Group, UnitedHealth Group IT has over ... with MQ * 1+ year experience with DB2 * 3+ years Development experience * 2+ years QA or Operations...
---------------------------
Software Development Engineer 2
Microsoft 
Redmond, WA
of the Developer Division, which is chartered with making it easy for developers and domain experts to use Microsoft ... in developing debuggers required. Demonstrated passion for developer experience a plus. Prior experience with high...
---------------------------
Senior Software Development Engineer
Microsoft 
Redmond, WA
We are looking for a strong and independent Software Development Engineer to work in a position to help us ... Qualifications include: 7+ years of strong experience in software design and development. Demonstrated ability and...
---------------------------
IT Software Development Engineer (SDE) 2 Job
Microsoft 
Issaquah, WA
Category: Software Engineering: Development Location: United States, WA, Issaquah Job ID: 727018 21522 Division: IT ... Management, Engineering, Software Engineer, Engineer, Test Engineer, Technology, SQL, Database, Developer, .NET,...
---------------------------
Senior IT Project Manager
UnitedHealth Group 
Philadelphia, PA
IT Project Management ... PowerPoint and Visio) - 6 years * Software application development experience (internet, intranet, infrastructure, networks, applications) - 6 years * Healthcare industry experience - 4 years Assets: * PMP...
---------------------------
Sr. IT Software Development Engineer (SDE) Job
Microsoft 
Issaquah, WA
Category: Software Engineering: Development Location: United States, WA, Issaquah Job ID: 723798 19797 Division: IT ... able to experience with writing automation for API or UI development, data management, SQL reporting. Candidates...
---------------------------
Senior IT Quality Manager
UnitedHealth Group 
Hartford, CT
processes are carried out through all phases of the Software Development Lifecycle * Work with business partners, systems analyst, designers and programmers to create/analyze required project documents and ... IT Quality Assurance...
View all jobs