User-Defined Data Types

During a routine search through Microsoft’s Knowledge Base articles, I came across the article "HOW TO: Change the Owner of a User-Defined Data Type That Is in Use in SQL Server 2000" (Q327123, http://support.microsoft.com). I had forgotten that SQL Server data types can have owners, and I’d never even considered that someone might want to change a data-type owner. The article describes a time-consuming process for changing a data-type owner; the process uses very little automation or T-SQL programming. I thought there must be an easier way to perform this change, and I set out to simplify the process.

Before you learn how to change the owner of a user-defined data type, you need to understand what a user-defined data type is, how and why you can create one, and how you can access information about user-defined data types on your system. I cover those points this month. In the next T-SQL Admin column, I’ll show you some T-SQL solutions for changing the data-type owner that are quicker and simpler than the Microsoft method.

Why Define a Data Type?

Not many people know that data types have owners, and even fewer know that you can create your own data types in SQL Server. In fact, if you’ve used only Query Analyzer, rather than Enterprise Manager, to create and manage objects, you might not have ever thought about data type owners. User-defined data types are identical to existing system data types, and many developers have survived quite well for years without ever creating a user-defined data type. So why would you consider using them?

The only real advantage to using user-defined data types is that they add the ability to self-document your data structures. For example, in several different tables you might have columns defined as varchar(30), and those columns might have nothing to do with each other. But if you define a data type called lastname to be equivalent to varchar(30), when you have multiple columns with the data type lastname, you know that those columns contain similar data.

You can also define a rule or a default as a separate object and bind it to a user-defined data type so that all columns using that data type automatically inherit that rule or default. In SQL Server 6.0, Microsoft introduced check constraints and column defaults as part of table definitions. These features have generally replaced binding of rules and default objects, but creating your own automatic inheritance for rules or defaults is sometimes preferable.

Getting Data-Type Information

If you decide to use user-defined data types, you need to know how to work with them. Every user in a database can create data types, and every user can use any user-defined data type that has been created. Enterprise Manager includes an icon that you can expand to show all the user-defined data types; but our focus here is what is possible using T-SQL. If you trace the commands that Enterprise Manager runs behind the scenes to generate its list of data types, you’ll see that it calls a procedure called sp_MShelptype. However, this stored procedure isn’t documented and its possible parameters aren’t obvious, so you’d have to do some research to use sp_MShelptype. If you’re interested in sharpening your T-SQL skills, I encourage you to learn for yourself how to use sp_MShelptype outside Enterprise Manager by reading the T-SQL definition and experimenting with using different parameters to call this procedure.

Note that no supplied sp_help procedure will show you just the names of the user-defined types in a database, but the names are included with other information when you run sp_help to see all the objects in a database. You can see information about user-defined data types by querying the INFORMATION_SCHEMA.DOMAINS information schema view. Or you can supply the name of a specific data type as the parameter for the sp_help stored procedure, and you’ll get information about the underlying data type. For example, to see the user-defined types in the Pubs database, you can run the statement

USE pubsEXEC sp_help tid

When I run the sp_help command above, I get results similar to the output that Figure 1 shows. (I left out the result column called Collation so that the results would fit in the space provided.) You can see that the data type that tid is based on is varchar with a length of 6. Precision and scale really don’t mean anything special for character types such as varchar; they mainly provide more details about numeric values. You can also see that the procedure still allows for binding a rule or a default to the data type, and if such a binding existed, sp_help’s output would show the name of the bound rule or default.

Sp_help lets you access information about a particular user-defined data type, but there is no procedure similar to sp_help that will give you a list of only the user-defined data types in your database. Before you start changing data-type owner names, you might want to write your own sp_help-type procedure to create such a list. (I can get the required result by wrapping the statement SELECT from INFORMATION_SCHEMA.DOMAINS in a procedure, but then you wouldn’t learn anything new about the system procedures or the system tables.) You can use your T-SQL scripting skills to create a new stored procedure that returns this list.

I can get a list that includes all the user-defined data types when I run the sp_help command with no parameters, but sp_help also returns a lot of extra information that I don’t want. I know I can get a list of just the user-defined data types if I create a procedure to return that specific information. If I execute sp_helptext sp_help from the master database to examine sp_help’s code, I can see that one SELECT statement does exactly what I want. This statement simply selects from the systypes table in the current database the rows for which the xusertype value is greater than 256—which indicates a user-defined data type. I can copy the sp_help SELECT statement into a new procedure that I call sp_listtypes, which Listing 1 shows. To make the procedure complete, I also need to copy a few variable declarations and assignments from the beginning of the sp_help procedure. (I could have also just replaced those variables with hard-coded values.)

As Listing 1 shows, the code I copied to my sp_listtypes stored procedure gets information from the systypes table. If you examine systypes, which exists in every database, you can see a row for each of the predefined system types (e.g., int, varchar) and a row for each of the user-defined types in that database. The xusertype column has a unique ID number for each data type (a unique index exists on that column), but the column called xtype isn’t unique. A user-defined data type has an xtype value equal to the xtype value for a system-supplied data type. And for system data types, the xtype and xusertype values are always the same. To use T-SQL to find out what system type is the basis for a user-defined type, you could join the systypes table to itself with a self-join, or you could use the undocumented function type_name(). I learned about the type_name() function by reading the text of the sp_help procedure that lists all the user-defined data types, and I included the function in the code I converted into my procedure.

Systypes contains columns for the length of the data column and for precision and scale, which are used for numeric values. Systypes also has columns to hold the object ID of any rule or default bound to the data type in the current row. If you examine all the columns in systypes, you might notice that some of them appear to be duplicates. In addition to xtype and xusertype, systypes has a type column and a usertype column. Systypes also has both prec and xprec for precision and scale and xscale for scale. The x-columns didn’t exist in the earliest versions of SQL Server, and all the procedures referencing the systypes table originally used column names such as type, usertype, prec, and scale. In later SQL Server releases, Microsoft changed the way that systypes keeps track of information, and all the x-columns were added. The company retained the other columns only for backward compatibility within the systypes table.

I decided to make one more change in my sp_listtypes stored procedure. I noticed in the systypes table a column called uid that the sp_help procedure ignores. The uid column contains the user ID of the owner or creator of the data type. The value in the uid column for most databases is 1, meaning that the dbo owns the data type, but if other users create data types, you’ll see other values. Because I’m investigating type owners, I translate this user ID into a username by using the supplied function user_name( ) so that my procedure will include the owner of the data type. Listing 1 shows that the username(uid) value will appear in the Owner column of the result set.

So do you need to be aware of any problems if a user who isn’t the dbo owns a data type? Generally, the answer is no. The systypes table has a unique index on the name column to avoid duplicate data-type names. Ambiguity is eliminated when you use a user-defined data type in a table definition, and even if the owner is different from the user who created the table, you never have to specify the owner. Any user who creates a table can use any data type in the database without ever having to qualify a data type name with an owner name. As far as I can tell from my research, the only time that the owner column is referenced is when Enterprise Manager reports the data-type owner.

Now that you can list all the user-defined data types and see who owns them, you can learn how to change the owner of a type. In the next T-SQL Admin column, I’ll show you several programmatic methods that you can use to make this change. All the T-SQL methods are easier than the technique Microsoft describes in its online article.