The Mysterious Empty Table

It seems like no matter how long you work with a system beyond a trivial size, you’ll find something new every so often. A little while ago, I found a table without about a dozen columns, no data, and was referenced in only one place across an entire database hosting nearly 1000 tables and over 8000 stored procedures. Why does this thing even exist?

To protect the innocent(ish?), obviously I’m changing the names of everything here. I was looking into some performance issues and found a very short stored procedure being called from another stored procedure which opened with this query:

1
2
3
4
5
6
7

select
    MyShortString
    ,MyLongString
    ,ZipCode
    ,StateAbbr
into #TempTable
from SystemUtility;

Huh. What immediately put my Spidey-sense on alert was the fact that the column names matched the user-defined scalar data types used pervasively in this database. That’s…unusual. Looking closer, SystemUtility was empty! So it looks like this may be one of the three (or more) ways to create a temp table. But what’s going on with those column names?

The full DDL for the the SystemUtility table is:

1
2
3
4
5
6
7
8

create table SystemUtility (
 [MyShortString] MyShortString
,[MyLongString] MyLongString
,[MyNovel] MyNovel
,[ZipCode] ZipCode
,[ZipPlus4] ZipPlus4
,[StateAbbr] StateAbbr
);

That’s certainly odd. But as I mentioned above, this database makes extensive use of scalar UDTs. Including these gems:

1
2
3
4
5
6

create type MyShortString from varchar(10);
create type MyLongString from varchar(255);
create type MyNovel from nvarchar(max);
create type ZipCode from char(5);
create type ZipPlus4 from char(10);
create type StateAbbr from char(2);

Sure enough, if I pop over to tempdb, get the true name of #TempTable and run sp_help, I see the underlying system types.

What was the author doing with this table? They were taking a shortcut. You can’t use UDTs in temporary objects (especially temp tables), but they really wanted to make sure that the types in this temp table were consistent with the database’s UDTs. So, they created an empty table that could be used as the source for a select into query to create their temp table.

As noted in that link, the problem is that you can’t just change the definition of the UDT and let the change propagate throughout. This “shortcut” doesn’t save you anything in the future (in fact, it creates more work), and I suspect that the authors made this discovery after the initial implementation, thus abandoning it - and not returning to back it out.

The alternative to this technique is to just define your table using the corresponding system types. If it’s not evident from that previous T-SQL Tuesday post (and now this one), I have some pretty strong feelings about scalar UDTs and this is another illustration of why.

After spending about 15 minutes chasing this down, I finally understood what this table was for and possibly why it was done. To only do it once with no explanation in a database with pervasive scalar UDTs, well, that’s why you always leave a note.