Temp Table 'vs' Table Variable 'vs' CTE.

Question

Temp Table 'vs' Table Variable 'vs' CTE.

Viewing 6 posts - 46 through 50 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 1

ChrisM@Work (8/23/2016)
The second article is written in growlingly poor English. I read it this morning. Despite some relief - in the comments - from the frustrating language and the glaring omissions I had no patience left for the first article. Until now.
How about this for "Factual Misunderstanding of the Week":
"Difference between CTE and Temp Table and Table Variable
Temp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.
Common Table expressions (CTE)
Common Table expressions (CTE) was introduced with SQL Server 2005. It is a temporary result set..."

Good gosh. I glossed right over that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Alan.B (8/23/2016)
growlingly poor English
:hehe::hehe::hehe: +1
I never heard the word growlingly before so I looked up the Webster definition.
Growl´ing`ly
adv.1.In a growling manner.

'Zackly. By the time you've finished reading it - if you get that far - you'll be growling 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Grant hit the nail on the head. I have a ONE ROW, ONE COLUMN demo (on AdventureWorks no less!!) that shows a poor query plan choice from a table variable where a temp table gets the optimal plan. All because of statistics on that one value.

I tell all of my clients that they should use temporary tables almost 100% of the time. The only exceptions that I know of are a) REALLY high volume DML activity leading to recompilation-related performance issues and b) you need to roll back a transaction and still have access to what you put in the temporary object. The first is almost ALWAYS the result of poor, iterative, code in my experience. The second is a rare bird.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eric M Russell SSC Guru Points: 125524 More actions · Answer 4

Your decision between table variable or temp table should depend on the data access pattern of your query.

Generally speaking, table variables are best applied in OLTP stored procedures where you want reusable execution plans and avoid recompiles. By default SQL Server estimates the row count of table variables to be 0 or somtimes 1. However, if you're coding heavy duty reporting type queries where cardinality estimation and leveraging alternate execution plans is more important than compile time, then temp tables are better.

The specific behaviour of table variables versus temp tables is something we need to revisit with each release, and I wouldn't bank too much on it as gospel. NOTE: There is a relatively new trace flag 2453 for SQL Server versions 2012+ that enables cardinality estimation for table variables in some circumstances.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sivapragasam2000 SSC Enthusiast Points: 150 More actions · Answer 5

Thanks Eric.

Much of these are against a db built specifically for reporting. Will check on cardinality.

Thanks again.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

(removed - already covered previously in the thread).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?