User Defined Types and Temp Tables Gotcha

This tripped me up a few weeks ago, but once I stopped and thought about for a moment it made total sense. I was trying to copy some data into a temp table and got an error I’d never encountered before.

Column, parameter, or variable #1: Cannot find data type MyStringType.

What’s that all about? Let’s find out.

Why User-Defined Types?

I’ve never been a fan of user-defined types (UDT). They definitely have applications, but there’s also a temptation to use them to “standardize” things like string lengths or decimal field precision across a whole database. On the surface, that seems like a good idea. But these can cause trouble as well. Not right-sizing your fields can lead to data quality and query performance problems.

But for argument’s sake, let’s go ahead and use a UDT and see what happens.

Setup

I’ll start out by creating a UDT for string values in my database, and a ridiculously simple table that uses it. Then, I’ll verify that the table really is using that type.

That’s what we want to see. The type exists in the database and the table I’ve created is using it.

Put it to Work

I’ll insert some data into the table just to verify that it’s working 100%, and then copy that data into a temp table.

Great, that worked. But what’s the data type for that field in the temp table?

Interesting! SQL Server translated that UDT to its base type when it created the temp table via SELECT INTO.

Can We Use the UDT in tempdb?

But what happens if I try to use that type when creating a temp table explicitly?

Msg 2715, Level 16, State 6, Line 3 Column, parameter, or variable #1: Cannot find data type MyStringType.

What happened? Even though I was working in the testing database, to create the temp table with a UDT, that type has to exist in the scope where the table will be created - the tempdb database in this case.

But this isn’t database-scoped. It’s scoped to the schema in which you created the UDT. So what if I try to fully qualify the type name with database.schema.type?

Nope, that throws an error too.

Msg 117, Level 15, State 2, Line 3 The type name 'testing.dbo.MyStringType' contains more than the maximum number of prefixes. The maximum is 1.

I can’t even look beyond the current database to reference my UDT. It makes sense, though. What if I had a UDT defined in one database, but used in another database? If I were to move that second database to another instance, things would break because the type would be gone.

Wrapping Up

User-defined types do have valid uses, and sometimes they’re required to accomplish a particular goal. For example, if you need to pass a table into a stored procedure or function, a UDT is the only means to do that. But for more regular usage, as we’ve seen here, they can be more trouble than they’re worth. If you want to use them in temp tables (which aren’t created via SELECT INTO), you can’t - you still have to know the base type.

So, are they really worth it? I’m going to pass on UDTs in favor of using right-sized standard types unless there’s no other means to fulfill a requirement. There’s just not enough upside to justify the pitfalls in my opinion.