February 25, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 3

  • By Karl Moore
  • Send Email »
  • More Articles »

User Defined Data Types are simply customised versions of the basic data types.

As an example, you might create a particular User Defined Data Type (UDT) to hold a postal code. In England, that would probably be defined as a char (string) of seven characters long that doesn't allow nulls.

When designing your tables, you'll find any UDTs you created among all the regulars.

The main purpose of UDTs is to allow for easier, centralised maintenance of data integrity.

After all, if the postal code system or something else slightly more volatile changes, you only have to update your one UDT, not a myriad of random tables.

Another magical advantage is that you can bind defaults (as per the last section) and rules (as per the next section) to specific UDTs.

To create a UDT:

  • View your Database in Enterprise Manager (as before)
  • Right-click the 'User Defined Data Types' icon, and select 'New User Defined Data Type'
  • Type in a Name for your UDT
  • Select a data type, length (if applicable) and check for whether or not you wish to allow null values
    • None of this information can be changed after you have created the UDT
    • Also, it would appear that whether or not you check to allow null values or not, this option is ignored meaning you must implement this functionality at table level.
  • Optionally, specify a Rule and Default
    • The Rule and Default options can be altered after the UDT has been created
  • Click OK to save

To implement a UDT:

  • Start creating your table (as before)
  • On the field you want to use the UDT, select it from the list of available data types
  • Continue the table creation process as normal

Page 5 of 6

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date