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?
 
 
2 Answers
 
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
 
 
CTE as well as table variable have one major drawback, they do not maintain statistics....CTE are great thing for readability and understanding the query

08/29/2010
 
 
Add an answer*
 
Your name
Email
 
Location: United States
db sql

add a question

arrow_blue


Now hiring!
PSF IT Project Manager
Children's Hospital of Orange County 
Orange, CA
include development and management of capital budget for information technology, securing resources to accomplish ... experience * 5 years experience managing the Information Technology function in the healthcare industry * Strong...
---------------------------
Business Process Analyst 3-Health IT
Northrop Grumman 
Woodlawn, MD
Northrop Grumman Information Systems sector is seeking a Business Analyst for IT Software Development to join our team ... management and analysis activities along all phases of the software development life cycle, including conducting user...
---------------------------
IT Mgr Software Engineering
Apollo Group 
Phoenix, AZ
supervision and the software development guidelines and technology standards defined by Apollo Corporate IT, this ... Experience supervising and/or leading other professional level IT staff is required...
---------------------------
Senior Software Developer - Info Technology Job
Mercer 
Deerfield, IL
Senior Software Developer - Info Technology-DEE000C5 Description Mercer is the global leader for trusted Human Resources ... information, please visit us at: www.mmc.com/diversity. Job Information Technology/Software Development Primary Location...
---------------------------
Account Manager - IT Staff Augmentation/IT Consulting Services
TNT Staffing 
Emerson, NJ
for highly motivated, dynamic Information Technology (IT) Sales Professionals to join our growing IT Staffing ... and a passion for relationship building and business development. The right candidate will have a successful...
---------------------------
Software Development Manager 2 - Health IT
Northrop Grumman 
Providence, RI
Northrop Grumman is seeking an experienced Software Development Manager to join our team of qualified, diverse ... years of relevant experience - Experience with IT Project Development Lifecycle - Strong Medicaid/Medicare business...
---------------------------
IT Mgr Software Development
Apollo Group 
Phoenix, AZ
supervision and the software development guidelines and technology standards defined by Apollo Corporate IT, this ... design, development, coding, testing and debugging of new software or significant enhancements to existing software.
---------------------------
Business Intelligence/Data Management Manager - Health IT - CSD
Northrop Grumman 
Woodlawn, MD
support team. The successful candidate must have prior software development experience, with specific insight into ... and data quality reporting within a structured software development framework. Possession of a PMP certification is...
View all jobs