SQL User Defined Types
Usage of User Defined Type in SQL Server with T-SQL Syntax
This tutorial describes usage of User Defined Types in SQL Server. Sample syntax and an explanation of when to use them and not use them is covered.
Why use User Defined Types? RULES can be applied to them which are similar to CHECK constraints. If I have a variable in a table, I can apply a CHECK constraint on it to prevent "invalid" data from being added
In the sample code below, I can create a variable called temp which will hold the value of Farenheit temperature in degrees and limiting the values to be between -140 degrees and +140 degrees.
CREATE TABLE tbl
( ID int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
temp DECIMAL(4,1) NULL
CHECK ([temp] < 140 and [temp] > -140)
)
INSERT INTO tbl (temp) VALUES (90);
INSERT INTO tbl (temp) VALUES (190);
The table is created and the first INSERT is successful, however the second one is not.
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 8
The INSERT statement conflicted with the CHECK constraint "CK__tbl__temp__3EC74557". The conflict occurred in database "AdventureWorks", table "dbo.tbl", column 'temp'.
The statement has been terminated.
What if I don't want to use a check constraint for these conditions? I can use a UDT and apply a rule to it.
CREATE TYPE myTemp FROM DECIMAL(4,1)
GO
CREATE RULE myTempRule
as @temp < 140 and @temp > -140
GO
EXEC sp_bindrule 'myTempRule', 'myTemp'
GO
I can now use the UDT as a Type for the variable temp in a new table, tbl2.
CREATE TABLE tbl2
( ID int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
temp myTemp NULL
)
INSERT INTO tbl2 (temp) VALUES (90);
INSERT INTO tbl2 (temp) VALUES (190);
The table is created and the 1st INSERT is successful however the 2nd is not.
(1 row(s) affected)
Msg 513, Level 16, State 0, Line 8
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'master', table 'dbo.tbl2', column 'temp'.
The statement has been terminated.
If I need to change the constraint, the Rule can be unbound from the UDT, Dropped and then Created with the new conditions and finally rebound to the UDT again. The UDT will pick up the changes automatically.
EXEC sp_unbindrule 'myTemp'
GO
DROP RULE myTempRule
GO
CREATE RULE myTempRule
as @temp < 190 and @temp > -190
GO
EXEC sp_bindrule 'myTempRule', 'myTemp'
GO
When not to use UDTs:
1. As a type for a variable. The following code would seem to work, but it does not enforce the rule.
DECLARE @t myTemp
SET @t = 199
SELECT @t
Instead of an error, the result is this:
---------------------------------------
199.0
(1 row(s) affected)
2. Permanent tables in your schema. If you ever need to update the Type, you will need to change UDT on the tables using it to something else, drop the UDT, recreate it and rebind it to the Rule and then change the datatype in all tables previously using the UDT back again.
The only place that I use UDTs is in temporary tables; either tables created during a stored procedure and then dropped or temp tables, in which the UDT and RULE are then created on tempdb. CLR UDTs have a little more functionality and covered in a different article.