SQL Server User Defined Types
Malcolm Matthews, Dunstan Thomas Consulting
http://consulting.dthomas.co.uk
User-defined Data Types
Although the arrival of Yukon, the next version of Microsoft SQL Server, is getting ever closer along
with its promise of full .NET integration, it is still worth looking at the full range of features that SQL
Server 2000 offers, many of them appear to be under used. One such feature is user-defined data types.
They do not offer any day to day functional benefits in that you can achieve everything you would wish
with the standard data types, but they do offer some significant advantages when it comes to
maintaining and documenting a database.
Reasons for Using User-defined Types
SQL server supports a comprehensive set of standard data types for storing data, from the basic int and
char to the more exotic uniqueidentifier. User-defined data types do not extend the type of data that
may be stored; instead they allow an alias to be defined for a specific type (e.g. varchar(10)). This has
the usual benefits of adding an indirection layer. During initial database development, if the underlying
data type needs to be changed, all fields using the user-defined data type can be changed simply by
changing the definition of the user-defined type and regenerating the database.
A bigger benefit comes from the level of consistency that can be maintained in the database. A
problem that can easily be encountered is that of having two logically equivalent fields with slightly
different definitions: e.g. varchar(30) and varchar(35). If the contents of the longer field are copied to
the shorter field it will work fine, until the data is too long to fit in the shorter field. Despite the fact we
all know we should test a system rigorously, this type of error can easily slip through testing. If a userdefined
type called TShortName (I’m a Delphi programmer at heart so prefix type names with a T) was
defined as varchar(30) and used in both tables, this problem would not occur.
It can also aid documentation of the system, since the user-defined name can be selected so that it is
relevant to the application domain making the contents of tables easier to understand.
This benefit of consistency extends to stored procedures. The best practise guidelines recommend the
use of stored procedures to provide an indirection layer between the database tables and the
application. With user-defined types it is much easier to make sure the field definitions in the
procedure match those of the underlying table.
Using User-defined Types
User-defined types can either be created manually using Enterprise Manager or using a system stored
procedure in Transact-SQL. In Enterprise Manager, simply select the “User Defined Data Types”
category for the database, right click and select New User Defined Data Type. Then just complete the
dialog box that appears. (More on the Rule and Default options later.)
In Transact-SQL, use the sp_addtype stored procedure, e.g.
EXEC sp_addtype TTelephoneNum, 'varchar(24)', 'NOT NULL'
The user-defined type can now be used in place of any of the standard types in table and procedure
definitions.
E.g.
CREATE TABLE PhoneBook (
EntryID int IDENTITY (1,1) NOT NULL,
PhoneNum TTelephoneNum,
FaxNum TTelephoneNum);
GO
CREATE PROCEDURE PhoneBookAdd(
@PhoneNum TTelephoneNum,
@FaxNum TTelephoneNum)
AS
INSERT INTO PhoneBook (PhoneNum, FaxNum) VALUES (@PhoneNum, @FaxNum);
GO
A further advantage of user-defined types is that default values and validation rules can be bound to
them. This means that any field of this type in any table will use the default value and validation rule.
A simple example is shown below.
--
*****************************************************************************
-- Define Validation Rules
-- *****************************************************************************
create rule PaymentMethodValues as @Method IN ('C','D') OR @Method IS NULL
-- Paymentmethod must be C (credit card), D (Direct Debit) or null.
GO
-- *****************************************************************************
-- Define Types
-- *****************************************************************************
EXEC sp_addtype N'TPaymentMethod', N'varchar (1)', N'null'
GO
-- *****************************************************************************
-- Bind Rules to types
-- *****************************************************************************
EXEC sp_bindrule N'dbo.PaymentMethodValues', N'TPaymentMethod'
GO
Now any field in a table defined as TPaymentMethod will automatically have a check constraint
imposed on it to ensure that the value is C, D or null.
Conclusion
SQL 2000 has many powerful features to manipulate data and to help maintain it. User-defined data
types simplify the task of maintaining a consistent schema and can also help document the database.
Using them is straight forward and does not require much more effort than using the standard types.
Malcolm Matthews, Dunstan Thomas Consulting
http://consulting.dthomas.co.uk
User-defined Data Types
Although the arrival of Yukon, the next version of Microsoft SQL Server, is getting ever closer along
with its promise of full .NET integration, it is still worth looking at the full range of features that SQL
Server 2000 offers, many of them appear to be under used. One such feature is user-defined data types.
They do not offer any day to day functional benefits in that you can achieve everything you would wish
with the standard data types, but they do offer some significant advantages when it comes to
maintaining and documenting a database.
Reasons for Using User-defined Types
SQL server supports a comprehensive set of standard data types for storing data, from the basic int and
char to the more exotic uniqueidentifier. User-defined data types do not extend the type of data that
may be stored; instead they allow an alias to be defined for a specific type (e.g. varchar(10)). This has
the usual benefits of adding an indirection layer. During initial database development, if the underlying
data type needs to be changed, all fields using the user-defined data type can be changed simply by
changing the definition of the user-defined type and regenerating the database.
A bigger benefit comes from the level of consistency that can be maintained in the database. A
problem that can easily be encountered is that of having two logically equivalent fields with slightly
different definitions: e.g. varchar(30) and varchar(35). If the contents of the longer field are copied to
the shorter field it will work fine, until the data is too long to fit in the shorter field. Despite the fact we
all know we should test a system rigorously, this type of error can easily slip through testing. If a userdefined
type called TShortName (I’m a Delphi programmer at heart so prefix type names with a T) was
defined as varchar(30) and used in both tables, this problem would not occur.
It can also aid documentation of the system, since the user-defined name can be selected so that it is
relevant to the application domain making the contents of tables easier to understand.
This benefit of consistency extends to stored procedures. The best practise guidelines recommend the
use of stored procedures to provide an indirection layer between the database tables and the
application. With user-defined types it is much easier to make sure the field definitions in the
procedure match those of the underlying table.
Using User-defined Types
User-defined types can either be created manually using Enterprise Manager or using a system stored
procedure in Transact-SQL. In Enterprise Manager, simply select the “User Defined Data Types”
category for the database, right click and select New User Defined Data Type. Then just complete the
dialog box that appears. (More on the Rule and Default options later.)
In Transact-SQL, use the sp_addtype stored procedure, e.g.
EXEC sp_addtype TTelephoneNum, 'varchar(24)', 'NOT NULL'
The user-defined type can now be used in place of any of the standard types in table and procedure
definitions.
E.g.
CREATE TABLE PhoneBook (
EntryID int IDENTITY (1,1) NOT NULL,
PhoneNum TTelephoneNum,
FaxNum TTelephoneNum);
GO
CREATE PROCEDURE PhoneBookAdd(
@PhoneNum TTelephoneNum,
@FaxNum TTelephoneNum)
AS
INSERT INTO PhoneBook (PhoneNum, FaxNum) VALUES (@PhoneNum, @FaxNum);
GO
A further advantage of user-defined types is that default values and validation rules can be bound to
them. This means that any field of this type in any table will use the default value and validation rule.
A simple example is shown below.
--
*****************************************************************************
-- Define Validation Rules
-- *****************************************************************************
create rule PaymentMethodValues as @Method IN ('C','D') OR @Method IS NULL
-- Paymentmethod must be C (credit card), D (Direct Debit) or null.
GO
-- *****************************************************************************
-- Define Types
-- *****************************************************************************
EXEC sp_addtype N'TPaymentMethod', N'varchar (1)', N'null'
GO
-- *****************************************************************************
-- Bind Rules to types
-- *****************************************************************************
EXEC sp_bindrule N'dbo.PaymentMethodValues', N'TPaymentMethod'
GO
Now any field in a table defined as TPaymentMethod will automatically have a check constraint
imposed on it to ensure that the value is C, D or null.
Conclusion
SQL 2000 has many powerful features to manipulate data and to help maintain it. User-defined data
types simplify the task of maintaining a consistent schema and can also help document the database.
Using them is straight forward and does not require much more effort than using the standard types.
Comments