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…

SQL ISNUMERIC() function

Imagine you have a column of data which is supposed to contain numbers, but you don't know which ones are numbers. Imaging you also live in a world where SQL Server 2012 and above don't exist. Now imagine you stumble across the function ISNUMERIC one day and think to yourself "huzzah! My worries are over!". Now imagine you put that check in to your code, push it to production, and low and behold, it doesn't do what you expect.

This is documented elsewhere as well but ISNUMERIC might not do what you expect. It includes characters which aren't strictly speaking numeric values. For instance, it contains several currency signs. It allows some white space characters, and it allows commas, pluses, minuses and more. Consider the following.

select 
    Idx = num,
    Chr = char(num),
    IsNum = isnumeric(char(num)),
    IsNum_e0 = isnumeric(char(num) + 'e0')
from (select top 255 num = row_number() over (order by (select null))
      from sys.all_objects) a
order by isnumeric(char(num)) desc, num 
 
You can see all the characters (from the first 255 ascii characters) which it will consider to be "numeric". It's also worth noting that this is not a comprehensive list of characters though, as I'll illustrate in a way which improves the functionality of isnumeric in many cases. the trick is to add 'e0' to the end of the string you're converting. So 123 becomes 123e0. Since this is scientific notation, it's considered a numeric value; and rightfully so. but "$\e0" is decidedly NOT numeric. In the statement above, I included a "e0" suffixed column as well as you can see it cuts out everything that's not a number.

So remember ISNUMERIC is not quite what you think, but if you're expecting a run-of-the-mill integer, float, or decimal value, appending "e0" to the end can get you a long way.

Comments

Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10