SqlServer: string.IsNullOrWhiteSpace translation is overly complex

[bricelam]

Today, we translate this as @value IS NULL OR LTRIM(RTRIM(@value)) = N''. But I have a few questions:

1. Why do we trim both the start and the end? Just one would produce the same result with less processing.
2. More importantly, why do we trim at all? As we know from our StartsWith research, N' ' = N'' returns true in SQL Server.

IMHO, the translation should be @value IS NULL OR @value = N''

[bricelam]

In a similar vein, IsNullOrEmpty should use LIKE.

[bradmarder]

@bricelam Possible to save a few bytes with COALESCE(@value, N'')= N''? Or ISNULL if strictly SQL Server

[roji]

@bradmarder IIRC COALESCE prevents index use, so @value IS NULL OR @value = N'' should be preferred. I don't know anything about IS NULL vs. ISNULL through.

[bradmarder]

@roji You are totally right about COALESCE not using an index, I missed that aspect. I did test ISNULL([@value], N'') = N'' on SQL Server 2017 and it did use an index seek (same exact execution plan as the @value IS NULL OR @value = N''.

[roji]

Split out changing IsNullOrEmpty to #23037.