8

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?

10

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 ('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 "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)

  • 1
    querying DNS for the MX record would be much better off done outside of SQL Server though. – Martin Smith Mar 4 '17 at 17:00
  • @MartinSmith - most definitely; I would never actually do it this way in practice. This is only a proof-of-concept. – Max Vernon Mar 5 '17 at 1:17
2

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.

  • The Connect item referenced indicates Microsoft is not going to implement CREATE DOMAIN in the then-current version of SQL Server (2012). It does not mean the feature is not supported, as shown by my answer. – Max Vernon Mar 3 '17 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.