Discover potential obstacles in your design

Tom

2021-09-29

2 minutes

Whenever I consider adding a SQL Server feature that I haven’t utilized yet, there is always a risk of some edge case that could jeopardize the whole design. Even when you read documentation, there might not be a complete list of all interactions between the features.

Did you know that when you try to update a VIEW, that has an INSTEAD OF TRIGGER, you cannot use the UPDATE statement with the JOIN clause? Probably not. Why would you? But it might be the edge case that makes you rethink your plans.

You won’t find it in the VIEW documentation or the TRIGGER documentation.
But the one place you’ll find it (eventually) is the error messages. Just apply some pattern matching like this:

SELECT *
FROM sys.messages AS m
WHERE
    m.language_id = 1033 /* English */
    AND m.[text] LIKE '%view%instead of%'

One of the messages is

UPDATE is not allowed because the statement updates view "%.*ls" which participates in a join and has an INSTEAD OF UPDATE trigger.

It doesn’t work 100% of the time. Sometimes the keyword you are looking for is replaced with the variable (like the %.*ls).

Still, it’s a useful little trick I use. Are you considering In-Memory OLTP? Columnstore? Partitioning? Or maybe all of them together?
Check the error messages before you commit to a design. It might save you a headache later.

Perhaps I would get even better results with a Full-Text Search?
I don’t know, because I haven’t played around with this feature much - but then again…

SELECT *
FROM sys.messages AS m
WHERE
    m.language_id = 1033
    AND m.[text] LIKE '%full-text%'

TSQL