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.
SELECT * FROM sys.messages AS m WHERE m.language_id = 1033 /* English */ AND m.[text] LIKE '%view%instead of%'
One of the messages is
It doesn’t work 100% of the time. Sometimes the keyword you are looking for is replaced with the variable (like the
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%'