Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.