CHECK constraint does not work if using UDF

We were having a bug a few days ago at work. It looked like somehow, we had some values in the database that they shouldn’t have been there at all due to our CHECK. After a while trying to find out what happened and how the values could be introduced, I started debugging the CHECK. When inserting, the ‘error’ would be displayed, however, when I tried to UPDATE the row… wow!! it looks like the row was perfectly updated, even though the CHECK shouldn’t allow it.

I fixed it using one of the workarounds, passing by parameter ALL the fields that we will need to check, even if they are not being used in the UDF. This could have been also fixed using a trigger. However, passing the column worked ok for me.

Example about how I fixed my code: Continue reading to see the example!

Please follow:

Coalesce

Yesterday, while I was answering some questions on “stackoverflow.com”, someone talked about ‘coalesce’ and I had not idea about it. I was always using ISNULL, however, after reading about it, it looks like it can have some advantages.

COALESCE is a shortcut for the CASE expression, but only when we compare it with NOT NULL.

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

NULL is also a shortcut, but it will only allow one expression instead.
The other difference is the type of data that returns. ISNULL will return the type of the first data type, however COALESCE will return the highest data type precedence.

It has some other differences that are very well explained on this webpage http://sqlmag.com/t-sql/coalesce-vs-isnull.

Practice. Continue reading for some practice

Please follow: