Another look at table variables

Question

Another look at table variables

Kathi Kellenberger

SSChasing Mays

Points: 601
More actions
February 26, 2022 at 12:00 am

#3989013

Comments posted to this topic are about the item Another look at table variables

Viewing 5 posts - 1 through 4 (of 4 total)

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

GregLow SSC Veteran Points: 254 More actions · Answer 1

I remember many developers calling them "memory tables".

The worst aspect of why people used table variables was that the Microsoft Best Practices Analyzer suggested they do so. At the time, every place that it found a temporary table, it suggested replacing them with table variables. It was tragic advice.

sean redmond SSCertifiable Points: 5816 More actions · Answer 2

I believe that the problem is twofold: Microsoft's erratic & short release schedule as well as Microsoft's eagerness to release half-baked features.

Because Microsoft's need to release a new version of SQL Server every 2-3 years, features that were not fully complete were released nonrtheless. These features would not become complete for another couple of releases. My favourites are In-Memory OLTP and Columnstore indexes. Marketing took to the stage and sang their praises. We then tried them out, found lots of gotchas & problems and wrote off the technology.

If Microsoft had a fixed schedule of say, every 5 years, they could release a version of SQL Server with fully functioning & complete features. They would then be in a position to offer a product so good that upgrading to the new version on its release would be the obvious move.

Now, alas, we hear nothing from Microsoft about SQL Server while being bombarded daily with news about Azure. It seems that Microsoft has gone in the opposite direction.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

I've lost the old link that I had about this and, if I still had it, it probably wouldn't work anymore anyway. A lot of people don't know that Temp Tables start off in memory and "spill to disk" only if they reach a certain size. I have no idea what that size is but suspect it's related to the size of TempDB and the amount of memory available to SQL Server.

I also agree with Greg... there was a whole lot of bad information that were based on MS "recommendations".

And I believe Sean hit the nail quite squarely on the head. I was looking at the new feedback site where people suggested the addition of an "ordinal position" of the split elements being returned (like they should have done from the git but there's that half-baked thing again). They've implemented the change is Azure and decided to close the related "tickets" on the feedback site saying that they'll get to the on-prem stuff. My feeling is that it'll be a long time coming before we see it on-prem. 🙁 I won't get into the rest of the half-baked stuff released in the last decade nor the stuff that needs a fix (REORGANIZE, REBUILD, etc, etc) and has needed a fix for more than 2 decades.

Also, I don't know if anyone has seen the following...

https://cloudblogs.microsoft.com/sqlserver/2022/02/25/the-path-forward-for-sql-server-analytics/

I really feel bad for any early adopters of that future missing functionality. In "The Path Forward" section of that article, they explain how they're adding new capabilities in 2022... yeah.... based on current and previous history, that scares the crap out of me. I can just envision a whole lot of people heading for the new shiny objects and then maybe "poof" those will disappear in a similar fashion. Heh... long live CROSSTABs (PIVOT was another half-baked feature that's never been brought up to snuff. The PIVOT in ACCESS is better!). 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 4

GregLow wrote:

I remember many developers calling them "memory tables".
The worst aspect of why people used table variables was that the Microsoft Best Practices Analyzer suggested they do so. At the time, every place that it found a temporary table, it suggested replacing them with table variables. It was tragic advice.

Just the fact that table variables are scoped to the current statement and temp tables are scoped to the current session should have been a pretty big clue they are not interchangeable.