I have a love hate affair with user defined types. I was first exposed to their benefits when I went on the original
Microsoft SQL 6.5 database design course. My first reaction was "WOW, this is a really cool idea" but now I am much more
circumspect in how I use them.
Their advantages are immediately apparent
- They greatly simplify the creation of the database schema
- They aid in standardisation across the schema.
When I was first learning about UDTs their disadvantages were not discussed and it was only through trial and
error that their limitations became apparent.
What is a user defined type?
Leaving aside the CLR data-types which are something different entirely at its simplest a UDT is simply a shortcut
to a built in data-type plus a definition of whether it is nullable or not.
So we could create a UDT of ty_rqdesc as follows
exec sp_addtype ty_rqdesc, 'VARCHAR(50)','NOT NULL'
Or using SQL 2005 syntax
CREATE TYPE [dbo].ty_rqdesc FROM VARCHAR(50) NOT NULL
This means we can create a table using our type such as
|Using a UDT||Using standard SQL|
CREATE TABLE dbo.SalesTerritory ( SalesTerritoryID INT NOT NULL , SalesTerritoryDescription ty_rqdesc ) GO
CREATE TABLE dbo.SalesTerritory ( SalesTerritoryID INT NOT NULL , SalesTerritoryDescription VARCHAR(50) NOT NULL ) GO
Where ever we use our ty_rqdesc data-type it will always be a variable length 50 character field that does not accept NULL values.
Taking it a step further
There are two other optional things we can do with our user defined type
- Bind a rule to the type to act as a CHECK constraint
- Bind a DEFAULT to the type
For example we could create a rule and bind it to our datatype as follows
CREATE RULE dbo.rl_reqdString AS LEN(RTRIM(@value))>0 GO exec sp_bindrule 'rl_reqdString','ty_rqdesc'
What this now means is that our user defined type is a compulsory variable length 50 character field into which some entry must be made.
We cannot get away with entering an empty string into the field.
A real world example
Let us suppose that we want a user defined type to represent gender. Gender is a mandatory field in the database, 80% of our customers are female
and we are going to use ISO5218 encoding.
As an aside, if you are going to store real world items in your database such as gender, countries, business classifications etc
I would always check to see if there is an international, or at least national encoding scheme for such data. In a world where
communications between organisations become ever more important, using an agreed standard makes far more sense than inventing
your own encoding scheme.
We would define our type as follows
exec sp_addtype 'ty_gender','TINYINT','NOT NULL' GO CREATE DEFAULT dbo.DEF_FEMALE AS (2) GO CREATE RULE dbo.gender_rule AS @gender IN (0,1,2,9) GO exec sp_bindrule 'gender_rule','ty_gender' exec sp_bindefault 'DEF_FEMALE','ty_gender' GO
Now let us look compare a table definition using standard SQL versus a user defined type as we did before. We will
only use our ty_gender type in the example for simplicity sake.
|Using a UDT||Using standard SQL|
CREATE TABLE dbo.SalesPerson ( SalesPersonID INT NOT NULL , SalesPersonName VARCHAR(50) NOT NULL , SalesPersonGender ty_gender ) GO
CREATE TABLE dbo.SalesPerson ( SalesPersonID INT NOT NULL , SalesPersonName VARCHAR(50) NOT NULL , SalesPersonGender TINYINT DEFAULT(2) NOT NULL CONSTRAINT CHK_SalesPersonGender CHECK (SalesPersonGender IN (0,1,2,9)) ) GO
Advantages of user defined types
So far the advantages are obvious
- We can couple a base type to a rule and default to represent a more complex entity
- We have a shorthand implementation for the above
- We have a standardised definition for a unit of business information for use in our database
The overall advantage that user defined types do have is they make it easier to build in data integrity by default. My
experience with non-DBA designed databases is that data integrity is normally very poor. Instead of the database engine
being thought of as a data integrity engine it is used as a retrieval system for what is basically just a
glorified dumping ground for data.
What is not apparent at this stage are the disadvantages of using these user defined types
The disadvantages of using user defined types
If portability between platforms is important to you then user defined types may be a no-go area. This is even more
true of SQL 2005 with its potential to use CLR data-types.
The big bugbear for user defined types is that they are not easily altered and require a great deal of work to plan for
an alteration. The time saved in creating a schema using the user defined types is lost thrice over if the user defined type has to be altered.
A concrete example of a UDT problem
Let us go back to our ty_rqdesc example. It is our standard non-nullable VARCHAR(50) with a rule bound to it to prevent zero length strings.
I decide that the use of this type is inappropriate for our dbo.SalesTerritory table and decide I want to use a nullable VARCHAR(40) field instead. I use my ALTER TABLE
statement as follows
ALTER TABLE dbo.SalesTerritory ALTER COLUMN SalesTerritoryDescription VARCHAR(40) NULL
It seems to have worked however when I go to create a new record with a null entry or zero length string I get an error telling me that a rule
has been violated!
Although I have altered the data type the rule that was originally attached to our data type remains bound to the column.
The same is also true of any default that may have been originally bound to the user defined data type.
For the purposes of creation a user defined data type can encompass the type, nullability, default and check constraint. For the purposes of altering a datatype
of a column the user defined datatype is only regarded as the datatype and nullability.
The problem is compounded because if I want to unbind the rule from my user defined type then drop, recreate and reattach the rule in a more appropriate form
then I am going to be told that I cannot drop the rule because it is bound to a column. This means that there is more involved in altering a datatype
than is immediately apparent.
To be fair if you use either sp_bindrule or sp_bindefault you are going to
run across the same problems. To amend a bound rule or default you are going to have to sp_unbindrule and
sp_unbindefault all occurrences.
Identifying all occurrences can be done using the query below however a suitable script has to be written to amend and rebind
the objects back onto their source.
-- Identify where the rule "gender_rule" is used SELECT OBJECT_NAME(id) AS TableName, C.name AS ColumnName, T.name AS TypeName FROM dbo.syscolumns AS C INNER JOIN dbo.systypes AS T ON C.xusertype = T.xusertype WHERE C.domain =OBJECT_ID('dbo.gender_rule') -- Identify where the default "DEF_FEMALE" is used SELECT OBJECT_NAME(id) AS TableName, C.name AS ColumnName, T.name AS TypeName FROM dbo.syscolumns AS C INNER JOIN dbo.systypes AS T ON C.xusertype = T.xusertype WHERE C.cdefault =OBJECT_ID('dbo.DEF_FEMALE')
The more I think about this the more this sounds like an internal SQL Server version of Don Peterson's
Lookup table madness where
an object orientated approach is grafted onto a non-object orientated system. In our case it is thinking that a table specific constraint
can be represented by a single generic object because that constraint "is a type of" gender constraint.
Retrospectively changing a column from a built in datatype to a user defined type
Let us suppose that I have created a table as follows
CREATE TABLE dbo.ASample( id SMALLINT IDENTITY(1,1) NOT NULL, sometext VARCHAR(30) NULL, CONSTRAINT PK_ASample PRIMARY KEY CLUSTERED (id ) )
I put some data into the table and run the following
ALTER TABLE dbo.ASample ALTER COLUMN sometext dbo.ty_rqdesc
If I run sp_help 'dbo.ASample' I can see that the field size and NULLability have
been addressed but any rule and default that have been bound to the user defined datatype have not been applied.
Other problems with user defined types
Sooner or later you are going to want to use your user defined type in a temporary table or table variable.
Firstly, you cannot use your user defined type in a table variable if it has a rule or default bound to it.
Msg 1710, Level 16, State 1, Line 1 Cannot use alias type with rule or default bound to it as a column type in table variable or return table definition in table valued function. Type 'ty_rqdesc' has a rule bound to it.
Secondly, before you can use your user defined type in a temporary table you have to create the type in TEMPDB. The only reliable
way of ensuring that your user defined type stays available to you after a reboot is to include the type in the MODEL database.
This means that if you need to alter your user defined type in your main database you will have to remember to amend it in MODEL and all
subsequently created databases. If you are not prepared to restart the MSSQLSERVER service this includes alterations to the TEMPDB
implementation of your user defined type.
Thirdly, if you are going to deploy your database to multiple servers then you must ensure that your user defined type is present
on all target servers. There is an exception to this rule. Replication gives the option to translate user defined types to their
native interpretation. I have to admit that I have never attempted this. I have had problems with replication where a feature
should work but does not for some reason. For example, telling the publication not to replicate DELETEs.
Lastly, a user defined type is really a template used at the point of creation and I cannot alter that template and expect it
to propagate the change all the way through my schema.
For most applications I feel that user defined types are more trouble than they are worth. If you are going to use them then
I would recommend limiting their use to types that are unlikely to change. Some examples are listed below
- Non-nullable DATETIME or SMALLDATETIME type defaulting to the GETDATE() value
- Non-nullable DATETIME or SMALLDATETIME type defaulting to the date portion (excluding the time) of the GETDATE() value
- Non-nullable numeric data types defaulting to zero
- Integer types with rules to allow YES/NO type responses. BIT fields are not part of the ANSI standard so I tend to use
TINYINT fields with defaults and rules attached to them instead.
- Email fields as VARCHAR(100) with a rule to check for the presence of an @ symbol and a . character.
- ISO5218 gender fields. Arguably this could be handled by a gender table and a foreign key but as this is most
likely to be a fixed set of values I would stick with a range that cannot be accidentally altered by DDL statements.
In the past I have used user defined types extensively and they undoubtedly allowed me to build a database schema very rapidly.
I was fortunate that I was in a position to spend a great deal of time designing the database and as a result the only schema changes
that were necessary in a 5 year period were to add new tables and the occasional field. I never had the pain of amending the
If amending a user defined datatype could be achieved in one command and the type automatically reapplied across the database
then I would be more enthusiastic about their general use but can you imagine the affect of a cascading data type upgrade on a
database with many hundreds of tables?