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: