Showing posts from 2014

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…

Computed OBJECT_NAME column

I was writing a script to pull a bunch of meta data to dynamically generate some other scripts, and I built a temp table to house various tables and different dynamic sql strings I was planning to use. For whatever reason, I opted to make the PK the object_id and build a computed column to output the name. When I did this, I got an odd result though. Assuming you have AdventureWorks2012 installed (it doesn't need to be that version, you just need some table objects), run the following code: use adventureworks2012 go declare @tables table ( TableId int primary key clustered, TableName as object_name(TableID) ) insert into @Tables (TableId) select top 10 object_id from sys.tables select TableId, ComputedTableName = TableName, OnTheFlyTableName = object_name(TableId) from @Tables You should see something that looks like this: The problem is that when you declare the computed column on @tables, it assumes tempdb as the context for the object_name() function call. So…

Covering Indices

I had a question posed to me where a person was working on a procedure which was running great until he added one new column to the table and query. To start at the end and work my way forwards, the issue was that the new column cause the query to no longer be "covered". What the heck does that mean? In short, it means that if you look up something in a given index, every piece of information SQL Server needs to return that data can be found in the index. To understand how that's accomplished you'll need a little primer on index structure. This is not an in-depth review of indices, but the concepts will be good enough. Take the following table, with some test data put in: if object_id('tempdb.dbo.#Users') is not null drop table #Users create table #Users ( UserID int identity(1,1) primary key clustered, FirstName varchar(100), LastName varchar(100), Birthday date ) insert into #Users (FirstName, LastName, Birthday) values ('Gabe'…


This is a cool little feature I found the other day doing some research on a SQL Server Central article. Someone asked if they could essentailly check for syntax errors in dynamically generated maintenance scripts. The first thought I had was to simply wrap each statement in a try/catch and an explicit transaction, but he also said that these were index maintenance statements and it wasn't feasible to be doing all the "heavy lifting" of rebuilding and reorganizing indices just to validate the SQL.

Enter the obscure NOEXEC setting. As the name suggests, when set ON, it will parse/validate sql but not execute it. While this is admittedly a pretty fringe case you'd need this for, it's none the less a very elegant solution to problems like the aforementioned.

set noexec on alter index IXN__myIndexondbo.myTablerebuild set noexec off

View Default Constraints

A co-worker today couldn't figure out how a column, ostensibly being set to null held a value of 0 instead of null. I looked at the populating code and couldn't find anywhere a null was being replaced with a value of zero, so the most likely candidate was a default constraint on the column. Turned out he wasn't sure how to find default constraints on columns.

The easiest way is to use the sp_help function on the table name, or highlight the table name and hit alt-f1.

exec sp_help myTable

At the bottom, you'll find all the constraints on the table, including default constraints.

If however your table is not part of the dbo schema (as the Aventureworks sample database is fond of doing) you won't be able to use those methods on the table as it assumes the object exists in the 'dbo' schema. Instead, you can access the data via the Dynamic Management Views (DMVs) like this:
select TableName = object_name(c.object_id), DefaultConstraintName =, …

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 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 …

Square marquee

For those of you who may not have known, there’s a cool trick you can do in Visual Studio or SSMS; the alt-drag marquee. Many of these functions can be handled with fine and replace with or without regular expressions, but sometimes those are more trouble than it’s worth.

The basics of alt-drag are this. Say I have a block of text, such as a comment block of a procedure which I want to replace all at once. Hold alt and drag to the bottom right, and you get a marquee area which respects x/y coordinates rather than just lines and indentation.

Now the cool part. If you’ve ever used copy-paste with these marquees, you’ll find they work… oddly. However like any good Jedi, you can turn your enemies weakness into your strength.

Say for example, I forgot to type select statements for a derived table like this:

You could use find replace to replace “all” with “all select’, you could use a series of copy/paste commands and down arrow keys, or this:

Create a zero-width vertical mar…