Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec,


A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists of a data type, possibly a default option, and zero or more (domain) constraints.

This allows us to do really cool stuff like implement a domain for the HTML5-spec for email over a case-insensitive text type. It ensures that all clients accessing the database have an integrity check on the data inserted.

CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*

Does SQL Server support any such functionality outside of the trigger system? );

Does SQL Server support any such functionality outside of the trigger system?

SQLCLR presents a surface area for creating fully custom data types. In fact, that is how SQL Server supports geometric and hierarchy data types.

Since SQLCLR is based on the Microsoft.Net common-intermediate-language, a large variety of constraints are possible for a SQLCLR data type. For instance, you could easily ensure an email address is from a valid domain by querying DNS for the MX record as part of the data validation code.

SQL Server can index a CLR UDT as long as IsByteOrdered:=True is set. There are a ton of properties like that, which you can change to affect how SQL Server uses the UDT. For equality matching like that required by an index, SQL Server simply looks at the binary value stored in the page i.e. it doesn't need to look at the UDT code at all.

As an example of a SQLCLR User-defined-type that checks a domain-space for validity, I've written the following terrible proof-of-concept VB.Net code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

_
_
Public Structure EmailType
Implements INullable
Implements IBinarySerialize

Private m_Null As Boolean
Private m_EmailAddress As String

Public Function ValidateEmailAddress() As Boolean
'is the email address valid?
If Me.IsValidDomain Then
Return True
Else
Return False
End If
End Function

Public Overrides Function ToString() As String
Return Me.m_EmailAddress
End Function

Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
' Put your code here
If Me.m_EmailAddress Is Nothing Then
Me.m_Null = True
Else
Me.m_Null = False
End If
Return m_Null
End Get
End Property

Public Shared ReadOnly Property Null As EmailType
Get
Dim h As New EmailType
h.m_Null = True
Return h
End Get
End Property

'called when SQL Server passes in a SqlString value to the UDT
Public Shared Function Parse(ByVal s As SqlString) As EmailType
If s.IsNull Then
Return Null
End If

Dim u As New EmailType

u.m_EmailAddress = CType(s, String)
If u.ValidateEmailAddress = False Then
Throw New Exception("Invalid Email Address")
End If
Return u
End Function

Public Function IsValidDomain() As Boolean
Dim iAtSign As Int32 = Microsoft.VisualBasic.Strings.InStr(Me.m_EmailAddress, "@")
Dim iDomainLength As Int32 = Microsoft.VisualBasic.Strings.Len(Me.m_EmailAddress) - iAtSign
Dim sDomain As String = Microsoft.VisualBasic.Strings.Right(Me.m_EmailAddress, iDomainLength)
Dim bResolvable As Boolean = False
Try
Dim ip As System.Net.IPHostEntry = System.Net.Dns.GetHostEntry(sDomain)
bResolvable = True
Catch ex As Exception
Throw New Exception(Me.m_EmailAddress & " is not from a resolvable domain.")
End Try
Return bResolvable
End Function

' save the value to the database
Public Sub Write(w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
w.Write(Me.m_EmailAddress)
End Sub

' retrieve the value from the database
Public Sub Read(r As System.IO.BinaryReader) Implements IBinarySerialize.Read
Dim sTemp As String = r.ReadString
Dim sTemp1 As String = ""
For Each n As Char In sTemp.ToCharArray
sTemp1 = sTemp1 & n.ToString
Next
Me.m_EmailAddress = sTemp
End Sub

End Structure


Since the code above is not signed, you should test it only on a development machine where you can enable the TRUSTWORTHY database setting. Once the code is compiled, you import it into SQL Server via the following:

CREATE ASSEMBLY SQLCLREmailType 
AUTHORIZATION dbo 
FROM 'C:\Path\Goes\Here\SQLCLREmailType.dll' 
WITH PERMISSION_SET = UNSAFE;

CREATE TYPE EmailType
EXTERNAL NAME SQLCLREmailType.[SQLCLREmailType.EmailType]

The UDT can then be used like this:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES ('mvernon@mvct.com')
    , ('us@them.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

The above code returns:

+------------------+
| (No column name) |
+------------------+
| mvernon@mvct.com |
| us@them.com |
+------------------+

However, when attempting to insert an address belonging to a non-existent email domain, as in:

DECLARE @t TABLE (
    col EmailType NOT NULL
    );

INSERT INTO @t (col)
VALUES , ('us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com');

SELECT CONVERT(varchar(50), t.col)
FROM @t t
GO

You see an error:


Msg 6522, Level 16, State 2, Line 27

A .NET Framework error occurred during execution of user-defined routine or aggregate "Email

StackExchange Database Administrators Q#165980, answer score: 10