18:I[1009,["874","static/chunks/874-0a4b6a176a83a671.js","353","static/chunks/353-ee5c84adec3cceb2.js","264","static/chunks/264-32b7aa9df841b4fa.js","340","static/chunks/app/%5B%5B...slug%5D%5D/page-45da6931db326616.js"],"default"] 19:I[4436,["874","static/chunks/874-0a4b6a176a83a671.js","353","static/chunks/353-ee5c84adec3cceb2.js","264","static/chunks/264-32b7aa9df841b4fa.js","340","static/chunks/app/%5B%5B...slug%5D%5D/page-45da6931db326616.js"],"default"] 1a:I[7043,["874","static/chunks/874-0a4b6a176a83a671.js","353","static/chunks/353-ee5c84adec3cceb2.js","264","static/chunks/264-32b7aa9df841b4fa.js","340","static/chunks/app/%5B%5B...slug%5D%5D/page-45da6931db326616.js"],"default"] 1b:T5d3,

So you’d like more consistency in your database? Try SQL User-Defined Types (UDT).

The concept is simple. You essentially create a new, custom type based upon an existing type but with predefined properties.

For example, we know that in most of our databases, many tables will have an IP field. This will retain the IP of the user submitting data. The field in the database will always be a nvarchar(15) type to cater for the IPv4 format, i.e. 255.255.255.255.

If we have 10 instances of this field then we will have 10 repetitions of the type definition. We could create a UDT for IP which itself implements the nvarchar(15) type. Each time we define a new IP field on a table, we can then use the IP data type.

The following SQL creates an email UDT:

Type defined like this will automatically be added to the type drop down menu of a column in a table’s design mode.

Consider using this for fields that need consistency, like:

IP;
Email;
Financial or decimal fields;
URLs or file names.

Also, we like to use this for fields that act as enum references. Often we will have an Id field in a table that references an enum, e.g. StatusId. These are always of the type tinyint, so we use a UDT called “enum”.

UDTs are e

Et harum quidem rerum facilis est et expedita distinctio

Browse content

Use SQL User-defined Types for more consistency in your database