SQL Server: Creating a User-Defined Data Type from Scratch

Dec 16, 2008 · 3 min read ·

Share on:

I've always been slightly jealous of PostgreSQL's network data types, speficially, it's IPv4-supporting cidr type. I wondered if Microsoft would ever implement a similar type (I'm still wondering) but until they do, I'll work with the ghetto I've got: User-Defined Datatypes, which were introduced in SQL Server 2005.

Here's how to create a UDDs in three easy steps:

Create a new data type based on a native type.
Create a new rule.
Bind the new rule to the new data type.

Below, I willl use these 3 easy steps to create a simple ipv4 data type. First, I need to figure out which native type to build upon. Based a standard IPv4 address (herein ipaddr/IP address/IPs), IPs have a max of 15 characters (255.255.255.255) so I'll go with varchar(15) as the desired native data type for the new data type, "ipv4."

EXEC sp_addtype ipv4, 'VARCHAR(15)', 'NULL'

Next, a rule must be created. I'm not going to get super picky here to include subnets and what not, so I'll just write a rule that 1. Checks to see if the value has 3 dots/periods. 2. Checks to see if the IP address is valid by getting its numerical value and... 3. Ensures the numerical value is not greater than 4294967295 (the numerical value for "255.255.255.255")

CREATE RULE IPv4Format AS ((LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3) -- if there 3 periods AND (16777216 * CAST(PARSENAME(@strIP,4) as bigint) + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1)) < 4294967296 GO

Next, the rule must be applied:

EXEC sp_bindrule 'IPv4Format', 'ipv4'

Now that I have a new data type called "ipv4" I'm going to try it out :D First, I'll create a table named "iptest" with a single column named "ipaddr" that has a ipv4 data type. Then I'll try to insert some valid and invalid data.

CREATE TABLE iptest (ipaddr ipv4) GO

INSERT INTO iptest (ipaddr) VALUES ('4.2.2.2') INSERT INTO iptest (ipaddr) VALUES ('255.255.255.255') INSERT INTO iptest (ipaddr) VALUES ('255.255.255.256') INSERT INTO iptest (ipaddr) VALUES ('random text') GO

The following results were returned, as expected:

(1 row(s) affected) (1 row(s) affected) Msg 513, Level 16, State 0, Line 4 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 'AdventureWorks', table 'dbo.test', column 'ipaddr'. The statement has been terminated. Msg 513, Level 16, State 0, Line 5 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 'AdventureWorks', table 'dbo.test', column 'ipaddr'. The statement has been terminated.

Excellent! And there's how to create a somewhat useful UDD in a couple easy steps. If you want to just copy and paste the code in its entirety, click here to get all the SQL listed in plain-text format. Thanks to Burleson Consulting for the posting the CREATE RULE that gave me hope.