4

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 a SELECT 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 of CHECK constraints on dependent data not being revalidated when a potentially integrity-breaking UPDATE 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 a SELECT-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+ row INSERT statement - if an application only does single-row INSERT DML then I'd expect to see the UDF+CHECK perform very similarly to the FK approach.

  • 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 than TRIGGER 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.

  • In a 2016 article Erik Darling described how using Scalar UDFs in CHECK constraints breaks parallelism.

...which predates significant (albeit recent) changes to SQL Server w.r.t. how UDFs are handled:

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 only LIKE and CHARINDEX).
  • Scalar UDF in a CHECK that does a SELECT * FROM $table, where $table is the same table that the CHECK constraint is applied to.
  • Scalar UDF in a CHECK that does a SELECT * FROM $table, where $table is a different table (i.e. not the same table that the CHECK 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.

Improve this question
3
  • What do you have against using triggers? A well-written trigger will nearly always outperform a UDF for multi-table constraints, because they are set-based. I have myself written, and seen, multiple answers on Stack Overflow regarding the indexed view trick, but never used it in anger. It's a very good if obscure trick, and probably the most performant
    – Charlieface
    Jun 14, 2022 at 2:05
  • @Charlieface my main reason for avoiding triggers in this project is that this project uses a lot of UPDATE and MERGE dml with the OUTPUT 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... argh
    – Dai
    Jun 14, 2022 at 5:05
  • I'll admit the point about OUTPUT (although you can use OUTPUT ... INTO but that would take a lot of rewriting clearly). In this case the trigger would just have an IF 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 funny SET options such as ANSI_NULLS OFF). Whichever solution you use must have well-indexed base tables to even have a hope of performing efficiently.
    – Charlieface
    Jun 14, 2022 at 8:20

1 Answer 1

Reset to default
2

Partial answer. Requirements to inline scalar function specifically states it cannot be in a computed column or check constraint.

The UDF is not used in a computed column or a check constraint definition

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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