Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  SQL Server Tips by Burleson

User-Defined Data Types

User-Defined Data Types (UDT’s) are useful in situations where the same data type is used in several columns from different tables, this data type might change in the future and so this mechanism assures that it will remain consistent. There is also added legibility because of the extra information that the name of the data type provides.

For example, phone number being a column used in a few different tables. Not only the data type should remain the same, the nullability, default value and check constraints or rules should be identical. In the case of rules, one rule would be bound to the UDT and that would be enough. With check constraints, the best solution would be to replace the constraints for the tables by using ALTER TABLE statements.

Create the UDT:

EXEC sp_addtype UDT_phone, 'VARCHAR(12)', 'NOT NULL'

This code will create the default:

CREATE DEFAULT def_phone AS 'Unknown'

This code will bind it to the UDT:

EXEC sp_bindefault 'def_phone', 'UDT_phone'

This is the code for the rule:

CREATE RULE rule_phone
(@phone='UNKNOWN') OR (LEN(@phone)=12 AND
AND SUBSTRING(@phone,4,1)=' '
AND SUBSTRING(@phone,8,1)='-'
AND ISNUMERIC(RIGHT(@phone,4))=1 )

Binding the rule to the UDT:

EXEC sp_bindrule 'rule_phone', 'UDT_phone'

Creating a table containing the UDT:

CREATE TABLE dbo.Contacts
phone_num UDT_phone NOT NULL,
fax_num UDT_phone NULL

The UDT does its job but the column fax_num will allow NULL values despite the fact that the UDT is non nullable.

The only workaround is to change the “NULL” column property but the only way to do it is by recreating the table. To recreate a table requires removing all the relationships from the table, creating a new table with the new structure and populating it with the data from the old table. Then the old table is dropped and the new one renamed with its name; finally the relationships are created. This process is only possible when the database is not in use.
Making changes to the rule or default objects bound to the UDT must be preceded by unbinding, changing and then bind again.

To drop the UDT:

EXEC sp_droptype 'UDT_phone'

The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter