User-Defined Datatypes


User-defined datatypes (UDDTs) allow you to define custom datatypes based on the system datatypes. For example, you could create a name datatype defined as varchar(30) . Any columns you create with the name datatype would automatically be varchar(30) . This helps create consistent fields across the database, which is especially helpful for join columns to ensure the absence of datatype mismatches . To create more complex UDDTs, you can bind rules and defaults (see Chapter 14, "Implementing Data Integrity") to them to add even more functionality.

To create and drop UDDTs, use sp_addtype and sp_droptype :

 Sp_addtype phone, 'char(13)'  Sp_droptype phone 

You can also add UDDTs through Enterprise Manager. Figure 12.1 shows the UDDT Properties box.

Figure 12.1. Using Enterprise Manager to create a UDDT.

graphics/12fig01.jpg

If the system datatype expression requires parentheses, you must surround it with quotes. You cannot drop a UDDT if any tables are using it. When you want to use a UDDT, simply specify it in place of a system datatype in your CREATE TABLE command. Listing 12.2 illustrates using the phone UDDT when creating a table.

Listing 12.2 Creating a Table Using a UDDT
 CREATE TABLE call_me (call_id int identity,  name VARCHAR(20),  phone_no phone) 

The datatype allows you to define the storage for data within a column. The following section will look at creating tables to define the columns and group them in a logical fashion.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net