Showing posts from 2017

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…

Named Constraints on Temp Tables

Momma sed don't name constraints on temp tables. But why?

How SQL Makes Temp Tables Globally Unique

When you first build a temp table, SQL stores the definition of that temp table away  and every subsequent time you create it, it's actually a clone of that one, original table definition. To allow each individual connection to create a clone of the table, it has to do something about the name so that it won't collide with itself. Let's see what that looks like