SQL Server allows the use of User-Defined Functions (UDFs) in CHECK
constraints - which has a number of use-cases - which I won't get into - but I was recently faced with implementing a non-trivial business/domain constraint that validated data in one table based on non-unique, non-key data from another table - which means the constraint cannot be implemented using any of the built-in constraints (FOREIGN KEY
, UNIQUE
, or a normal row-value based CHECK
constraint).
My immediate go-to was a TRIGGER
, as Microsoft's own documentation states that TRIGGER
objects are the intended way to implement non-trivial constraints:
DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application. [...] Unlike
CHECK
constraints, DML triggers can reference columns in other tables. For example, a trigger can use aSELECT
from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
Hang a second: Microsoft wrote "Unlike CHECK
constraints, DML triggers can reference columns in other tables" - but that's misleading! CHECK
constraints can reference columns in other tables indirectly via a UDF.
...so methinks there's a bit of vague and outdated information out there - so let's do more research to see if CHECK
constraints with a UDF is the right way to go or not...

...well, it seems that UDFs in CHECK
constraints seem have a bad rep: with critical remarks concerning poor performance and their lack of formal correctness (a winning combination...), but I noticed that the articles and posts I read, including practically all of the Google search results concerning UDFs and CHECK
constraints was just old...
The #1 Google search result for "sql server check constraint udf" is an article dating back to 2001 - which doesn't give any performance advice, but the fact this is still the top result shows there's a lot of old and outdated information out there that people today might think is still current.
-
https://www.itprotoday.com/sql-server/using-udf-check-constraint-validate-column
The ANSI SQL standard lets you use subqueries in CHECK constraints, but SQL Server doesn't support this functionality. However, if you're using SQL Server 2000, you can write a user-defined function (UDF) that performs an existence check against both tables and returns 1 if a row exists in either table and 0 if no row exists.
-
A 2006 DevX.com article for SQL Server 2000 and 2005 demonstrates using a pair of UDF-based
CHECK
constraints to solve the problem ofCHECK
constraints on dependent data not being revalidated when a potentially integrity-breakingUPDATE
is run - however the article goes on to say that SQL Server 2005's snapshot-isolation feature means that invalid data can still find its way into the database.A 2009 StackOverflow post has an answer describing a UDF-based
CHECK
constraint running 100x slower than non-CHECK
-based approaches.- A common refrain in the other answers - and the comments on those other answers - is a vague warning that a UDF in a
CHECK
constraint is "slow" - but most of the commentators don't provide an explanation or qualification - nor do they even say if "UDF" refers to a deterministic, non-table-reading function (e.g. a string format validation function) - or aSELECT
-wrapping function - as I would expect a string format validation function to not significantly affect query or batch performance. - A follow-up SqlBlog.com article about that very StackOverflow post provides
SET STATISTICS
data, but I noticed the article doesn't share the full query plans and crucially: only looks at performance from a 128,000+ rowINSERT
statement - if an application only does single-rowINSERT
DML then I'd expect to see the UDF+CHECK
perform very similarly to the FK approach.- This SqlBlog article then links to a 2008 article that demonstrates the UDF+
CHECK
approach not working when the UDF reads from the same table that a multi-rowUPDATE
is applied to.
- This SqlBlog article then links to a 2008 article that demonstrates the UDF+
- A common refrain in the other answers - and the comments on those other answers - is a vague warning that a UDF in a
A 2011 SpaghettiDba.com article where the author describes how using Scalar UDFs in
CHECK
constraints causes row-by-agonizing-row evaluation of the UDF (still better thanTRIGGER
objects, of course).A 2012 post on Dba.StackExchange also gives vague warnings:
UDFs that access other tables should generally be avoided in CHECK constraints [...] There is almost no use case where they will behave entirely as expected by the user, there is almost certainly not a single SQL engine that implements them correctly according to the semantics as prescribed by the standard for
CHECK
constraints.A 2014 StackOverflow post about SQL Server 2008 has highly-upvoted answers, which links to the aforementioned SqlBlogs.com article.
...which predates significant (albeit recent) changes to SQL Server w.r.t. how UDFs are handled:
- SQL Server 2017 added Interleaved Execution for Multi-Statement Table Valued Functions which I wonder might have implications for Scalar UDFs as Scalar UDFs and MSTVFs are very similar (compared to Inline TVFs).
- SQL Server 2019 added inlining of Scalar UDFs.
However authoritative information is hard to come-by, so I'm unsure what, as of SQL Server 2019 or SQL Server 2022, the current state of UDFs-in-CHECK
constraints is, with respect to both performance and correctness, in both single-row and multi-row DML scenarios:
- Deterministic Scalar UDF in a
CHECK
that doesn't read any data, but is parameterized on a column value (e.g. a string validation function using onlyLIKE
andCHARINDEX
). - Scalar UDF in a
CHECK
that does aSELECT * FROM $table
, where$table
is the same table that theCHECK
constraint is applied to. - Scalar UDF in a
CHECK
that does aSELECT * FROM $table
, where$table
is a different table (i.e. not the same table that theCHECK
constraint is applied to).
As an alternative to UDF-in-a-CHECK CONSTRAINT
, I've been reading about using Indexed Views as a way of effectively implementing certain classes of database-wide (i.e. cross-table) integrity constraints which are always enforced when any DML modifies data in any of the included tables, whereas CHECK
constraints only take effect on INSERT
and UPDATE
, but not DELETE
(so CHECK
constraints cannot be used to block DELETE
DML) - but I'm curious if anyone has any experience with the technique.
UPDATE
andMERGE
dml with theOUTPUT
clause used extensively - and the presence of triggers breaks those operations completely - and (as far as I know) we can't disable triggers for a single session separately from the others or use them like deferrable-constraints. Also, a trigger doesn't just reject DML: it can also have writes and side-effects of its own... arghOUTPUT
(although you can useOUTPUT ... INTO
but that would take a lot of rewriting clearly). In this case the trigger would just have anIF EXISTS (Failing Rows Here) THROW ....
which is unlikely to cause extra writes, only reads. If I were you I'd go for the indexed view (although you need to be careful not to have funnySET
options such asANSI_NULLS OFF
). Whichever solution you use must have well-indexed base tables to even have a hope of performing efficiently.