Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

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
AS
(@phone='UNKNOWN') OR (LEN(@phone)=12 AND
ISNUMERIC(LEFT(@phone,3))=1
AND SUBSTRING(@phone,4,1)=' '
AND ISNUMERIC(SUBSTRING(@phone,5,3))=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
) ON [PRIMARY]


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

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter