Does SQL Server support custom DOMAINs?

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

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, MaxByteSize:=320, ValidationMethodName:="ValidateEmailAddress")> _
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 ('[email protected]')
    , ('[email protected]');

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

The above code returns:

+------------------+
| (No column name) |
+------------------+
| [email protected] |
| [email protected]      |
+------------------+

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 "EmailType":
System.Exception: us@asdfasdfasdfasdfasdfasdfasdfasdf90097809878907098908908908908.com is not from a resolvable domain.
System.Exception:
at SQLCLREmailType.EmailType.IsValidDomain()
at SQLCLREmailType.EmailType.ValidateEmailAddress()
at SQLCLREmailType.EmailType.Parse(SqlString s)

I'm not going to mark this as chosen yet because there may be a work around, but technically they're not supported. You can see the issue on Microsoft Connect.

Hello Erland,

After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.

Thanks again for providing the product suggestion and continued support for our product.

-- Umachandar, SQL Programmability Team

The issue was opened in 2012.