Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Love Hate Affair with User Defined Types

By David Poole,

Introduction

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?

The basics

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.

Code Meaning
0Unknown
1Male
2Female
9Not applicable

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.

Unfair accusations?

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.

Conclusion

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 schema.

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?

Total article views: 6404 | Views in the last 30 days: 8
 
Related Articles
FORUM

Can i use Default in Alter Statements

Can i use Default in Alter Statements

FORUM

Default parameters changing when new database is created

Default parameters when database is created with Studio

FORUM

Alter Database with variable

Alter Database with variable

ARTICLE

Stairway to SQL Server Indexes: Level 12, Create Alter Drop

Options and impacts when creating, altering and dropping an index

FORUM

ALTER DATABASE SGCT SET SINGLE_USER

ALTER DATABASE SGCT SET SINGLE_USER

Tags
advanced    
database design    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones