Seems not...
Imagine a table like this:
FORUM_MESSAGES (
ID INT,
COMMENT NVARCHAR(MAX)
)
Now see this query:
DECLARE @LIKE AS NVARCHAR(MAX) = NULL
SELECT
*
FROM
FORUM_MESSAGES
WHERE
COMMENT LIKE ISNULL(@LIKE, COMMENT)
If you have a COMMENT longer than 4000 letters it will drop a truncate error...
Looking at the execution plan I found this little change:
FORUM_MESSAGES.COMMENT like CONVERT_IMPLICIT(NVARCHAR(4000), ISNULL(@LIKE, FORUM_MESSAGES.COMMENT ), 0)
After some digging it became obvious that the LIKE operator does that to the SQL...
Looked around in the internet, but found no confirmation of that behavior...
Have you had any experience with LIKE and NVARCHAR(MAX)?
[UPDATE]
As to clear the air following OG's solution...
I do understand why LIKE does CONVERT... but there is the problem, that changing @LIKE to NVACHAR(4000) does not solve the problem...
In that case there is no truncate error (even SQL does run a CONVERT_IMPLICIT, now on COMMENT), but rows with more than 4000 letters will be removed without error... Somehow it is even worst than before as now there is no error, while clearly there is a truncation...
What I have tried:
Anything in my power...
Talk to DBA...
Intensive Google...
Actually found some solutions... but I looking for an explanation why SQL's LIKE forces me down to NVARCHAR(4000)...