Gary Harding (5/13/2015)
Gary Harding (5/12/2015)
I've hit a few problems in the past using scalar UDFs in CHECK constraints.
The main one is that the constraint didn't always fire.
If the column(s) passed into the UDF as parameter(s) hadn't changed, it seemed like SQL Server decided it was unnecessary to reapply the constraint.
That may not be precisely what was happening, but changing the UDF call to include the columns that had changed (and whose values were queried separately in the UDF) fixed the problem for me.
For example, changing the call from Func(Id) to Func(Id + Status - Status).
This was in SQL Server 2008.
I'm a bit confused about this, unless you mean that "if the column(s) passed into the UDF as parameter(s) hadn't changed" was part of an UPDATE statement where you used DEFAULT.
Perhaps if you had an example to share, someone could figure out what is causing this.
I don't have the constraints to hand at the moment, but I'll try and post something early next week.
OK, I've tracked down the CHECK constraints.
The reason they needed to use a UDF was because the checks involve querying another table.
Basically, I need to maintain consistency between a column in the first table, T1 (the one on which the constraint is defined) and a similar column in a related second table, T2 (for which a FK is stored in T1).
To keep the UDF interface clean, I just pass the PK of T1 into the function, which then does the T1-T2 JOIN and compares the two column values from T1 and T2 to ensure they remain compatible.
It seems that the fact that I'm only passing the PK into the UDF can cause the constraint not to fire.
Possibly the optimizer is assuming that the UDF is deterministic and, as the PK is static, nothing can have changed since the last time the row was created or updated. Referencing the relevant column in the UDF call did seem to ensure the constraint was applied reliably on all updates.
I appreciate that this is not an ideal way to implement such a constraint, and maybe a trigger might be a more robust and RBAR-less way to do it. It's just that I don't recall seeing any caveats on whether constraints will fire or not, and wanted to share my experiences.