Bad Habits to Kick : Using alias types
See the full index.
To clarify what I am talking about here, alias types (more formally, "user-defined data type aliases") used to be called "user-defined data types." Then, when CLR came around in SQL Server 2005, they gave us the ability to create our own CLR user-defined types (UDTs) – a different beast altogether. I won't get into the differences, but to avoid confusion, they came up with a new name (an alias, if you will) for the old-style user-defined data types, which better reflects what they actually do, I guess.
In general, the thinking behind
user-defined data types user-defined data type aliases is that you can use this central definition to declare the properties of a column type across your entire database. For example, if you wanted to always store e-mail addresses in a
varchar(320) column, you could do this:
CREATE TYPE [email] FROM varchar(320) NOT NULL;
Then you could create a table and procedure like this:
CREATE TABLE dbo.Contacts ( ContactID int PRIMARY KEY, EmailAddress [email] UNIQUE ); GO CREATE PROCEDURE dbo.Contact_Create @ContactID int, @EmailAddress [email] AS BEGIN SET NOCOUNT ON; INSERT dbo.Contacts ( ContactID, EmailAddress ) SELECT @ContactID, @EmailAddress; END GO
This would ensure that your developers always use a
varchar(320) column for e-mail addresses, because they are instructed to use the [email] data type instead of picking their own at random. This provides consistency throughout your tables, and prevents you from losing data when someone accidentally declares a variable or parameter as
varchar(255). Of course someone can still do that, if they "forget" that they are supposed to use the
[email] data type… but SQL Server can't help you solve that problem.
One issue with this approach is that the definition of e-mail address (or at least how you understood it at the time you created the type) can change over time. I know that many people use
varchar(255) for e-mail addresses, even though those are far too short (at least according to the standard) and should be Unicode. In this example, if you picked a big enough size (e.g.
nvarchar(320)), it is unlikely to affect you, as the standard hasn't really changed.
Do you want a real example of a supposedly fixed size data element that has changed? ISBN. I would bet that at one point Barnes & Noble thought it might be a good idea to create an alias type called
char(10) so that people wouldn't be using
nvarchar(max), and other silly ways to define the ISBN column. Then when ISBN-13 codes became standard, they would have regretted doing so. Why? Well, let's try modifying an alias type once it's in use; let's say we need to expand our email type from above to support 350 characters. Our instinct would be to use DDL like this:
ALTER TYPE [email] FROM varchar(350) NOT NULL;
But we can't:
Incorrect syntax near 'TYPE'.
In fact, there is no DDL for
ALTER TYPE. (And no,
sp_rename or using synonyms won't help you here, nor is there a system procedure called
sp_altertype.) So can't we just drop the type and re-create it? You would think you could do the following:
DROP TYPE [email]; GO CREATE TYPE [email] FROM varchar(350) NOT NULL;
But you get this error message:
Cannot drop type 'email' because it is being referenced by object 'Contacts'.
There may be other objects that reference this type.
You need to follow this process instead:
- find *all* of the objects that use this alias type (this includes parameters definitions and variable declarations);
- modify them all so that they point at the "regular" type (or a different alias type);
- drop the alias type using DROP TYPE;
- create the alias type with the new definition;
- modify all of your objects to point back at the original alias type again.
Now take the case with ISBN, which might also serve as the primary key and have related tables scattered throughout the database. In addition to the above steps, before you start step 2 above, you will also have to temporarily drop all of the constraints, and drop them in the correct order.
Once you have changed all the references back to the native type, why not just leave them that way? You can point your users at your data dictionary and/or documentation to ensure that they choose the correct data type. At least then when you have data type changes next time, you can phase the new type definition in gradually, instead of an all-or-nothing change.
Further to this, what if you need to use the same custom data type in multiple databases? Now, in addition to the above complications, you must also synchronize the alias types common to all of your databases, or just hope and pray that their definitions will never change.
There are other complications with using alias types that you won't come across when using the built-in types.
CONVERT / CAST
You cannot use CONVERT() or CAST() to change a column, variable or literal to an alias type; if you do, you get:
Type email is not a defined system type.
You also don't get syntax highlighting in Management Studio, so unlike other data type names, your alias type names will appear black instead of blue in query editor windows.
You may also experience issues when you use external or 3rd party providers or drivers to access stored procedures with these alias types, or try to data-bind, or implement Entity Framework, Linq, or other ORM solutions. I'll admit I haven't tried it, but I envision the integration will not be 100% seamless.
Another anomaly with alias types is that you cannot declare #temp tables that use them, unless the alias type also exists in tempdb (but you can use them in @table variables, which also use tempdb; go figure):
Column, parameter, or variable #1: Cannot find data type email.
For further clarification, you can select *into* a new #temp table from a source that includes a column with an alias type, however the #temp table inherits the properties of the base data type (so in this example, the column would be defined as
I hope I have demonstrated that, while tempting, the benefits of alias types are far outweighed by the potential problems they can cause down the road. In fact, I am hard-pressed to think of a single implementation of an alias type that would provide any tangible benefits at all.
CLR UDTs are a slightly different story, but personally I would stay away from those too.
See the full index.