Does SQL Server Support Custom Domains?
If you have spent time with PostgreSQL or Oracle, or read enough of the SQL standard to regret it, you have probably met the CREATE DOMAIN statement. A domain is a named, reusable type with constraints baked in: define email once as text plus a validity check, and every column declared as email inherits that check automatically. The validation travels with the type, not with each table.
So a fair question, and one that came up on Database Administrators Stack Exchange, is whether SQL Server supports the same thing. The short answer is no, not the standard CREATE DOMAIN. The longer and more interesting answer is that you can build something far more powerful, and I am going to show you how, and then talk you out of using it.
What SQL Server Gives You Out of the Box
SQL Server has three native features that get partway to a domain, and each one stops short.
Alias types. CREATE TYPE ... FROM lets you name a base type and fix its nullability. That is genuinely useful for consistency, but it carries no validation:
|
1 2 |
CREATE TYPE [dbo].[email_address] FROM varchar(254) NOT NULL; |
A column declared as email_address is just a varchar(254) NOT NULL with a friendlier name.1 Nothing stops you from storing 'not an email'.
Rules. The old CREATE RULE / sp_bindrule mechanism can attach a predicate to a type, which is the closest SQL Server ever came to a domain constraint. It has been deprecated for years and is slated for removal, so do not build anything new on it.
CHECK constraints. This is the practical answer for most validation. The catch is that a CHECK constraint lives on the table, not on the type, so you repeat it on every table that needs it:
|
1 2 3 4 5 6 |
CREATE TABLE [dbo].[subscriber] ( [email] varchar(254) NOT NULL CONSTRAINT [CK_subscriber_email] CHECK ([email] LIKE '%_@_%._%') ); |
That LIKE pattern is a crude shape check, not real validation, and you have to remember to add it everywhere. None of these three gives you the defining property of a domain: validation that is part of the type itself.
The CLR Escape Hatch
There is a fourth option that does let the validation live inside the type: a SQLCLR user-defined type. SQL Server lets you write a data type in .NET and register it in the database, where it behaves like a first-class type. You have already used types built exactly this way. hierarchyid, geometry, and geography are all system CLR types, which is why you can call methods on them like @node.GetAncestor(1). I wrote about one of them in the hierarchyid post.
Because a CLR type is just a .NET structure, the validation method can run any code you can write in .NET. Including, if you are feeling reckless, a network call. Let us be reckless.
A Self-Validating EmailType
Here is a proof-of-concept type, written in C#, that rejects any email address whose domain does not resolve in DNS. It is deliberately a proof of concept, the kind of thing you write to answer “can this even be done,” and I have left it honest rather than tidy:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
using System; using System.Data.SqlTypes; using System.IO; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType( Format.UserDefined, IsByteOrdered = true, MaxByteSize = 320, ValidationMethodName = "ValidateEmailAddress")] public struct EmailType : INullable, IBinarySerialize { private bool m_Null; private string m_EmailAddress; // Called by SQL Server to validate the value on the way in. public bool ValidateEmailAddress() { return this.IsValidDomain(); } public override string ToString() { return this.m_EmailAddress; } public bool IsNull { get { this.m_Null = (this.m_EmailAddress == null); return this.m_Null; } } public static EmailType Null { get { EmailType e = new EmailType(); e.m_Null = true; return e; } } // SQL Server calls Parse when converting a string literal to the type. public static EmailType Parse(SqlString s) { if (s.IsNull) { return Null; } EmailType u = new EmailType(); u.m_EmailAddress = (string)s; if (u.ValidateEmailAddress() == false) { throw new Exception("Invalid email address"); } return u; } public bool IsValidDomain() { int atSign = this.m_EmailAddress.IndexOf('@'); string domain = this.m_EmailAddress.Substring(atSign + 1); try { System.Net.Dns.GetHostEntry(domain); return true; } catch (Exception) { throw new Exception(this.m_EmailAddress + " is not from a resolvable domain."); } } // Persist the value to the page. public void Write(BinaryWriter w) { w.Write(this.m_EmailAddress); } // Read the value back from the page. public void Read(BinaryReader r) { this.m_EmailAddress = r.ReadString(); } } |
The two attributes at the top are doing the important work. ValidationMethodName tells SQL Server which method to call before it accepts a value, and IsByteOrdered = true is what makes the type indexable. When IsByteOrdered is true, SQL Server compares and sorts values by their stored binary representation, so an index or an equality match never has to call back into the CLR code. That is the same trick the built-in CLR types use.
Deploying It
Once the assembly is compiled, you register it and create the type. The proof-of-concept assembly is unsigned, so this quick version leans on the TRUSTWORTHY database setting, which is why you should only do it on a development instance you do not care about. The permission set is EXTERNAL_ACCESS rather than UNSAFE, because the only thing the type reaches for is a DNS lookup, which counts as external access:
|
1 2 3 4 5 6 7 |
CREATE ASSEMBLY [SQLCLREmailType] AUTHORIZATION [dbo] FROM 'C:\Path\Goes\Here\SQLCLREmailType.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; CREATE TYPE [dbo].[EmailType] EXTERNAL NAME [SQLCLREmailType].[SQLCLREmailType.EmailType]; |
Deploying It the Supported Way
The TRUSTWORTHY shortcut above is fine for a throwaway instance, but it is exactly the posture the security section below warns against. On SQL Server 2017 and later, clr strict security is on by default and treats every assembly as UNSAFE unless it is signed and trusted. Here is the supported path, which needs no TRUSTWORTHY at all.
First, strong-name sign the assembly when you build it: the Signing tab in a SQL Server Database project, or csc /keyfile: from the command line. Then create an asymmetric key from the signed DLL, bind a login to it, and grant that login UNSAFE ASSEMBLY:
|
1 2 3 4 5 6 7 8 9 10 |
USE [master]; GO CREATE ASYMMETRIC KEY [EmailTypeKey] FROM EXECUTABLE FILE = 'C:\Path\Goes\Here\SQLCLREmailType.dll'; GO CREATE LOGIN [EmailTypeLogin] FROM ASYMMETRIC KEY [EmailTypeKey]; GO GRANT UNSAFE ASSEMBLY TO [EmailTypeLogin]; GO |
Now register the assembly and the type. Note the precise split: the login needs UNSAFE ASSEMBLY, because that is what clr strict security checks, but the assembly itself only needs EXTERNAL_ACCESS:
|
1 2 3 4 5 6 7 8 |
CREATE ASSEMBLY [SQLCLREmailType] AUTHORIZATION [dbo] FROM 'C:\Path\Goes\Here\SQLCLREmailType.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO CREATE TYPE [dbo].[EmailType] EXTERNAL NAME [SQLCLREmailType].[SQLCLREmailType.EmailType]; GO |
I deployed it exactly this way on a SQL Server 2019 instance with clr strict security left on and TRUSTWORTHY off, and it behaves identically: a resolvable address is accepted and round-trips through the type, and an address whose domain does not resolve is rejected with the same Msg 6522 you are about to see.
Watching It Work
A valid address goes in without complaint:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @t TABLE ( [col] EmailType NOT NULL ); INSERT INTO @t ([col]) VALUES ('hannah@example.com') , ('someone@microsoft.com'); SELECT CONVERT(varchar(254), [t].[col]) AS [email] FROM @t AS [t]; |
An address whose domain does not resolve never makes it into the table. The validation throws during the insert:
|
1 2 |
INSERT INTO @t ([col]) VALUES ('user@asdfasdfasdfasdf90097809878907.com'); |
|
1 2 3 4 5 |
Msg 6522, Level 16, State 2, Line 27 A .NET Framework error occurred during execution of user-defined routine or aggregate "EmailType": System.Exception: user@asdfasdfasdfasdf90097809878907.com is not from a resolvable domain. |
That is a real custom domain. The type validates itself, the validation travels with the type to every column that uses it, and SQL Server can still index it. It is also a bad idea, and it is worth being precise about why.
Why This Is a Terrible Idea in Production
I called the code a terrible proof of concept on purpose. Here is what is wrong with it, in roughly the order it will hurt you.
It puts a network round trip in your insert path. Every single insert and update now waits on a DNS lookup before the row lands. One slow or unreachable DNS server, and your writes stall. Run a bulk load of a million rows and you have a million synchronous DNS queries holding a transaction open. Validation that does I/O does not belong on the hot path of a write.
It does not even check the right thing. Dns.GetHostEntry resolves an A record, the address of a host. Whether a domain can receive email is an MX record question, and plenty of valid mail domains have no A record at all while plenty of A records belong to domains that accept no mail. The check feels rigorous and is mostly theater.
UNSAFE plus TRUSTWORTHY is a security posture you will regret. TRUSTWORTHY ON lets code in the database run with elevated rights, and it is a well-known privilege-escalation path. The supported way to ship CLR is to sign the assembly with an asymmetric key or certificate, create a login from that key, and grant it UNSAFE ASSEMBLY, leaving TRUSTWORTHY off. On SQL Server 2017 and later, clr strict security treats every assembly as UNSAFE unless it is signed and trusted this way, so the signed path is not optional anymore.
The logic is invisible and hard to change. A CHECK constraint is right there in the table definition for anyone to read. This validation lives in a compiled DLL. To adjust the rule you recompile and redeploy an assembly, and the next person to read the schema has no idea why an insert failed.
What To Do Instead
For the everyday version of this problem, reach for the boring tools.
Use a CHECK constraint for a cheap shape check, and be honest that a pattern match is not real validation:
|
1 2 3 |
ALTER TABLE [dbo].[subscriber] ADD CONSTRAINT [CK_subscriber_email] CHECK ([email] LIKE '%_@_%.__%'); |
Pair it with an alias type so the base type and nullability stay consistent across tables, and centralize the constraint in a deployment script or a tool that applies it everywhere.
Then do the expensive, fallible part, confirming a domain actually exists and accepts mail, where it belongs: at the edge, in the application or a service, asynchronously, and well outside the transaction that writes the row. An email you cannot deliver to is a business problem to handle in a workflow, not a reason to roll back an insert.
Save CLR user-defined types for what they are genuinely good at: self-contained value types with custom behavior and no side effects, the way hierarchyid and the spatial types work. A type that reaches out to the network to decide whether a value is acceptable is exactly the kind of thing they are not for.
Wrapping Up
So, does SQL Server support custom domains? Not as a standard CREATE DOMAIN, but through SQLCLR you can build a type that validates itself, carries its rules wherever it is used, and still indexes cleanly. It is a great way to understand how the built-in CLR types are put together. It is also a great way to learn, the hard way, why validation that does I/O should never sit inside an insert.
The capability is real and occasionally the right tool. Most days, a CHECK constraint and a bit of validation at the edge will serve you far better.
Have you ever shipped a CLR type into production, or inherited one you wished you had not? I would like to hear the story. You can find me on Bluesky and LinkedIn.
1 Why varchar(254) and not the often-cited varchar(320)? The 320 figure comes from RFC 3696 §3, which simply adds the maximum local-part length (64 octets) to the maximum domain length (255 octets) plus the @. That sum overlooks RFC 5321 §4.5.3.1.3, which limits a forward or reverse path to 256 octets including the enclosing < and >, capping the address itself at 254 octets. RFC 3696 was formally corrected on this point by Errata ID 1690. So 254 is the true maximum length of an email address; 320 merely over-allocates. (The MaxByteSize:=320 in the CLR type above is a different quantity, the maximum serialized size of the value, which needs a little headroom over 254 for the length prefix that IBinarySerialize writes, so it is left as is.) ↩
How about regex in check constraints?
SQL Server 2025 does support
REGEXP_LIKE()per https://learn.microsoft.com/en-us/sql/t-sql/functions/regexp-like-transact-sqlFor earlier versions, it would certainly be possible to use .Net regexp functions via custom SQLCLR code.
“An email you cannot deliver to is a business problem to handle in a workflow, not a reason to roll back an insert.”
If I got nothing more valuable out of that article, that observation is worth it.