Temp Table 'vs' Table Variable 'vs' CTE.
-
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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 ShawFor 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 -
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 -
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
-
Thanks Eric.
Much of these are against a db built specifically for reporting. Will check on cardinality.
Thanks again.
-
(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?
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply