Dreaming of User-Defined Data Types

Keeping the T-SQL variables (like stored procedure parameters) consistent with actual table column definitions might be a real headache, if you don't rely on ORM framework to do the job for you. You modify your table and you go searching for any place in your code where you might use the variable to keep your column value.

Dependency viewer is a great help, still it has it's limitations.

1. Prior to SQL Server 2008, dependency information between objects was not reliable (Dependency information was recorded only if the referenced object existed when the referencing object was created).
2. Still, SQL Server doesn’t record dependency information for references that appear in dynamic SQL and CLR code.

Using UDDT sounds like a remedy. Let's see.

Ideally, instead of creating table using primitive types, as follows:

CREATE TABLE dbo.Category
(
    Id UNIQUEIDENTIFIER NOT NULL
    , UserName NVARCHAR(256) NOT NULL
    , Title NVARCHAR(256) NOT NULL
    , Color INT NOT NULL
), CONSTRAINT CategoryIdPK PRIMARY KEY NONCLUSTERED (Id)
GO

I'd like to have something like this:

CREATE TYPE dbo.TUSERNAME FROM NVARCHAR(256);
GO
CREATE TYPE dbo.TCATEGORYTITLE FROM NVARCHAR(256);
GO
CREATE TYPE dbo.TCOLOR FROM INT;
GO


CREATE TABLE dbo.Category
(
    Id UNIQUEIDENTIFIER NOT NULL
    , UserName TUSERNAME NOT NULL
    , Title TCATEGORYTITLE NOT NULL
    , Color TCOLOR NOT NULL
), CONSTRAINT CategoryIdPK PRIMARY KEY NONCLUSTERED (Id)
GO

And it does, actually, look promising.

Alas! When you try to modify your Title column type or size (isn't it what you were trying to get ready for), you briefly discover that there is no good way to do it. There is no ALTER TYPE statement. You might thing that dropping a type and creating it again will do the work. Mistake. What you will actually get will be:

Msg 3732, Level 16, State 1, Line 1
Cannot drop type 'dbo.TUSERNAME' because it is being referenced by object 'Category'. There may be other objects that reference this type.

It turns out you need to create a new UDDT, and alter you column to use this new UDDT, then only you can get rid of the old one.

Imperfect, imperfect, imperfect world.

UPD: And yes, the scope of the UDDT is the current database, so if you want to create a temporary table using UDDT, you should create this UDDT in tempdb as well.