Discover potential obstacles in your design

Did you know that when you try to update a VIEW that has an INSTEAD OF TRIGGER, you cannot use the UPDATE statement with a JOIN clause? Probably not. Why would you?

That edge case won't appear in the VIEW documentation or the TRIGGER documentation. But when two features collide in SQL Server, the failure mode has to live somewhere - and that place is sys.messages.

Whenever I'm considering a SQL Server feature I haven't used yet, I run a quick search against the error messages before committing to a design:

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

One of the results:

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're looking for is swallowed by a variable placeholder like %.*ls. But it's a useful signal for catching incompatibilities before they catch you.

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.

Still not convinced? Thinking about enabling Full-Text Search?

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

Run that first. Now you know what you're getting into.

If errors make it to production anyway, Extended Events are how you hunt them down.

Thank you for reading