| |
 |
|
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
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 |
