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…

Regular Expressions in SSMS

Several times a year I get asked something like "Can SQL do regular expressions"? The answer is essentially no (I know, I know, CLRs). But while SQL statements can't make sure of regular expressions to search through table data, you CAN use them in the course of creating and editing SQL scripts.

SQL Server Management Studio as well as Visual Studio, which the later versions of SQL Server use as their IDE support regular expressions in find-and-replace operations. If you're wholly unfamiliar with regular expressions, there are tons of resources out there to help you learn. Some good info can be found at www.regular-expressions.info. I personally bought a book called Introducing Regular Expressions which I found quite useful. It's not my intent to teach regular expression here, but to show you how to find a regex character you need and give you some find-and-replace regular expressions I use almost daily.

First things first, SQL Server uses some funky notation for some of its special characters. I found it particularly difficult to find this page on Google as well, but this is, as close to I can find, the definitive source describing the regex syntax used in SSMS and VS

Search Text With Regular Expressions

Commonly used expressions

Again, I don't want to give a comprehensive overview of regular expressions in SQL, but show you some of the tricks I use a lot which can save a lot of time in editing procedures. 
  • :a (alphanumeric)
    • I probably use :a more than anything else. It matches any alphanumeric character which is great of SQL objects that use camel case. Add a + or * to the end of it to quickly find a list of column names.
    • Example:
      • :a+
      • Matches any string of 1 to n alphanumeric characters
  • :d (digit)
    • To be honest I rarely use this one, but it's another simple useful one to identify any digit between 0 and 9. Again, you can modify it with a + or * to find strings of numbers.
    • Example
      • :d*
      • Matches any string of 0 to n digits
  • . (Any character)
    • As the description suggests, a period "." will match any single character; be it white space, a number a letter or so forth. I don't think it will capture line breaks.
  • [] (character set)
    • Another one I use all the time. Those of you who read my comment about easily identifying object names may have pointed out that object names can contain underscores, variables are declared with an @ sign, and temp tables are declared with a # sign. Build a character set inside brackets to allow what you need.
    • Note that you CAN use special characters in your character sets such as :a and :d
    • Example
      • [:d@]
      • Matches the @ sign or any digit
  • {} (Tagged Expression)
    • The syntax for this is, from what I hear, a little different from some other regex dialects. I don't know how many you can have nested in a single regex, but I've never had to use more than 2 or 3 at a time.
    • How these work in find-and-replace is each tagged expression maps to a number which can be accessed in the "replace" bar by a slash "\" and the number of the tagged expression.
    • To figure out what the number of the expression is, start counting from the left-most open curly bracket in ward (starting at 1). 
    • Hello {{\@}:a+}
      • \2 matches the @ character
      • \1 matches the @ character AND a string of one or more alpha numeric characters
    • \0 always represents the entire regular expression
  • \ (Escape)
    • Always a good one to know. slash "\" escapes a special character
    • Example:
      • \@


Here are some example regular expression similar (if not verbatim) to what I use quite often.I don't show it in my screenshots, but generally, you'll want to select "use selection" from the "look in" dropdown or you will apply it to the whole script. Also, check the box under "find options" that says "Use:" and then select "Regular Expressions" from the dropdown.
  • Select variables as columns
  • Injecting a string into a set of column names
  • Aliasing columns
  •  Make a serialized string easier to read

While this isn't a comprehensive list of what you can do with regular expressions and find-and-replace, hopefully it gives you some good ideas of ways to speed up your work. Combined with the Square Marquee  I talked about in a previous post, you can do some pretty creative things to speed up editing and creation of stored procedures.

If you have regular expression you use regularly (get it?) or any questions, please feel free to leave me a comment below.


Popular posts from this blog

JOINs Using Playing Cards

Named Constraints on Temp Tables

Master Data Services on Windows 10