Digbyswift is based in Leeds, West Yorkshire offering web and digital solutions. With over a decade of experience in corporate and agency web development, Digbyswift can meet and support your requirements, whether it be MVC or web forms development, Umbraco, bespoke CMS build and maintenance, ecommerce, SEO and Google analytics or even client training. Read more ...

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

By Digbyswift at December 14, 2010 04:59

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, I know that in most of my 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 I have 10 instances of this field then I will have 10 repetitions of the type definition. I could create a UDT for IP which itself implements the nvarchar(15) type. Each time I define a new IP field on a table I can then use the IP data type.

The following SQL creates an email UDT:

CREATE TYPE Email
FROM varchar(150) NOT NULL ;

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 I like to use this for fields that act as enum references. Often I will have an Id field in a table that references an enum, e.g. StatusId. These are always of the type tinyint, so I use a UDT called “enum”.

UDTs are especially useful if you have several developers working on a database – but only provided that they are all singing from the same hymn sheet.