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!
IT & Engineering Professionals w/ Poly
SRA International 
Herndon, VA
Business focuses on providing high-end information technology solutions and support to clients within the ... Architects ? Configuration Management Specialists ? Software Engineers ? Software Developers ? Geospatial...
---------------------------
IT Application Analyst
Saint Barnabas Health Care System 
Oceanport, NJ
projects. Participate in and provide leadership for development and support of automation initiatives. Research ... skills. Experience in w orking with and developing hospital information systems. Patient Accounting and/or registration...
---------------------------
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...
---------------------------
IT Development Manager
Farmers Insurance Group 
Simi Valley, CA
Preferred Skills and Abilities Minimum 8+ years hands-on software development experience required; Minimum 4+ years ... required; Minimum 2+ years distributed (J2EE preferred) development experience required; Mainframe COBOL development...
---------------------------
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.
---------------------------
Software Development Analyst 2 - Health IT
Northrop Grumman 
Providence, RI
Northrop Grumman Information Systems Sector is seeking a Software Development Analyst 2 to join our team of qualified, ... development life cycle (SDLC) in support of application software from design, development, testing, documentation...
View all jobs