Posts tagged bug

Have you ever used an alias type in SQL Server? I bet you have. It’s really cool that you can create your own alias type with CREATE TYPE statement in your database and then use it in your database objects. What drives people to using the alias types is a thought that these types can be easily changed by a single operation which does not require looking into every individual database object and checking whether the alias type is used or not. Well, is this really the way it is? Unfortunately not.

There is no ALTER TYPE statement in SQL Server. So whenever you want to change your own alias type (for example to make it longer in case of variable length character types) you have to perform quite a lot of operations, like:

• script and drop (or alter to make independent of the “altered” alias type) all procedural objects that use the “altered” alias type,
• script and drop all constraints on columns of the “altered” alias type,
• script and drop all foreign key referencing the columns mentioned above,
• rename the “altered” alias type,
• create the new alias type with the new definition and the old name of the “altered” alias type,
• alter all columns of the “old type” to make them using the new alias type,
• recreate all dropped objects and constraints,
• refresh just recreated procedural objects.

That doesn’t look simple, does it? The bad news is that this is not all – you should perform all those operations in a single transaction… The reason is quite obvious – you are going to do some nasty things with the database objects (drops, alters, recreates). It would be bad if the process stopped somewhere in the middle (leaving some objects not restored to their original definitions). So, there is plenty of T-SQL code to write, all with transaction and error handling, dynamic T-SQL, lurking into metadata and so forth.

Unfortunately, there is a bug making all the operation practically impossible to perform in a single transaction in certain scenarios (when you use a table variable with a column of the “altered” alias type). See this item for details:
Deadlock occurs when creating user-defined data type and objects that use it

They promised to fix it in SQL Server 2008 R2. Just can’t wait to check it out (for now it’s not fixed in CTP).

My final point is to encourage you to voting for the item: MSFT-MSO: Support ALTER TYPE. This (ALTER TYPE statement) is something that I really wish to see in the next release of SQL Server. So please, vote for this item to be fixed/implemented. Let me know if you vote. Thanks in advance!