Temp Tables vs Table Varibles: The Great Debate

There seems to be a lot of confusion around the differences between  temp tables and table variables (in the classic sense; I'm not talking about in memory table types or anything here). Some people say you should only use table variables. Some people say you should only use temp tables. Most people caution a more nuanced approach. Here I want to cover what I think are the biggest issues in an attempt to shed some light on this surprisingly tricky topic.

Statistics This is usually the most important factor when considering which one to use. It boils down to two facts, and everything else flows from these: Temp Tables maintain statistics, Table Variables do not.

Table Variables Because table variables maintain no statistics, the Query Optimizer always assumes the contain exactly one row. As a result, any joins against a table variable, unless explicitly told to behave otherwise will (probably) alwaysbe a nested loop join.

As long as the number of rows in the table variable are small…

Rolling an Idea Around in Your Hand

I hear from many people (my wife included) that they “just don’t have a mind for programming”. Now maybe that’s just a polite excuse to say they have no interest in programming, which, too, is fine. And while, like with music, there are some people who really have (or do not have) structures in their brains making it difficult if not impossible to do, I think the majority of the skills are generic enough to be learned with practice and more importantly, a drive to do so.

One of the skills you pick up along the way is what, by analogy, I’d like to think of as rolling an object around in your hand. There are few things in our daily lives that we’ve never seen before, let alone something you can hold in your hand. But there are some. Imagine an unsolved Mirror Cube. Or maybe you’ve decided you want to work on your car, and you start removing parts you never knew existed. Or maybe you get a Christmas present still in the packaging and you’re trying to figure out what it might be?

Many times, you’ll just start fiddling with it. Feeling its surface. Rolling it around in your hand. Feeling its weight. Inspecting it from different angles; color, shape, function. If it has parts, how they fit together. Maybe the parts move. Maybe they move and they shouldn’t. Maybe, and most cripplingly, you’re afraid to do the above because you think you might break it, or not be able to put it back the way it was. Maybe you’re right.

The point is, you’re solving a problem. You don’t realize it yet, but you are. Any time you’re presented with something you don’t fully understand you start doing an investigation. The only difference in the software world is you’re lucky enough to be able to hit the undo button, removing much of the risk in the last bit of the previous paragraph.

To a large extent, that’s what software development is. Or more accurately, that’s what software maintenance and troubleshooting is… which is basically what software development is. Maybe that’s what people talk about when they say they don’t “have a mind for programming”. It’s not that they can’t, but they’re not the kind of person who feels the need or desire to question everything, get their hands dirty, and learn about as abstract a concept as an arbitrary piece of code. Personally, I find it a lot of fun. And you sort of do this “rolling around” in levels, going from the simplest to the hardest.

At the simplest, you’re just trying to acknowledge what it is you’re looking at. If it’s a Rubik’s Cube, that’s what you’re trying to come up with. If it’s an encoding issue on a web page, that’s all you’re trying to identify. Then you start fiddling with it. With our Rubik’s Cube example, start twisting things. Start small; make sure you can find your way back. If it’s a piece of text that’s wrong, maybe find where that text comes from, or how it’s being displayed.

All along the way, you’re looking for clues to figure out what you ultimately are trying to get to. If it’s a Cube, maybe you’re trying to solve it. If it’s a bug, maybe you’re trying to find the root cause and possibly fix it.

For your cube, maybe you start trying to solve a side. You draw upon your knowledge of geometry, group theory (or at least the intuitions you have about it), your memory of fiddling with sides and your goal to try to move all the pieces to one side. For your software bug, maybe you open the code that generates that text, and you start substituting other pieces of text seeing if they yield the same bug.

This process can go as deep as it needs to. For the Rubik’s Cube, entire branches of mathematics are in no small part devoted to solving puzzles like the Rubik’s Cube. For software, there are web tools, open source tools, tools by Microsoft or Apache to solve these tools. There are test frameworks and troubleshooting methodologies. All of which will come with practice and experience. Many of which you won’t need for all problems, but are tools none the less at your disposal.

The point is, when you see an intractable problem in front of you, don’t be scared. Pick it up and start playing with it.


Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10