SQL Server IN clause within the boolean of IF statement

Question

I have a query to debug and it contains something I haven't seen. Here's the analogue query of my problem.

DECLARE @i AS INT
DECLARE @total AS INT
SET @total = 0
SET @i = 0

IF DATENAME(WEEKDAY,GETDATE()) IN (N'Wednesday') BEGIN
    SELECT @total = @total + 1
        SET @i = @i + 1
END

SELECT @i

My question is in this part: IN (N'Wednesday'). What is that N before the varchar 'Wednesday'. I have no idea if it's a kind of keyword or something like that. This query is okay to be executed; there will be no error. On the other hand, it would be okay too (as far as I know) when I remove this N. What interests me the most is that, when I change it to be n, it yields error.

In SQL Server, it specified a national character set string -- one where the characters are more than 1 byte. — Gordon Linoff, May 06 '20 at 02:39

score 0 · Answer 1 · answered May 06 '20 at 02:51

The N prefix means the following string literal is an nvarchar value rather than a varchar value.

In SQL Server, nvarchar and nchar values are stored using UTF-16 and this has problems with storing higher-plane Unicode characters - but they can be worked-around. Whereas the varchar and char values use the server's configured default encoding (ISO 8859-1) which SQL Server calls "iso_1".

(I note that since SQL Server 2019, SQL Server now finally supports UTF-8, which renders nvarchar obsolete in my opinion - as the only advantage UTF-16 has over UTF-8 is more efficient storage of code-points in the range 2048-65535 (where it uses 2 bytes whereas UTF-8 uses 3 bytes), but this is moot as most characters used by most languages are either within the 0-2047 range - so in the vast majority of cases for most applications, especially those processing predominantly Latin-based alphabets it's better to stick with UTF-8).

This query is okay to be executed; there will be no error. On the other hand, it would be okay too (as far as I know) when I remove this N. What interests me the most is that, when I change it to be n, it yields error.

It errors because DATENAME returns an nvarchar value, not a varchar value, and you can't use the IN operator to compare nvarchar and varchar values together (but you can use the = operator) - or

Keep the N'' syntax. Even if the IN operator did support mixing varchar and nvarchar operands, it means you avoid having to force SQL Server to convert values between varchar and nvarchar's encoding - it also means that DATENAME will work in non-Latin contexts where "Wednesday" is not the name for hump-day.

SQL Server IN clause within the boolean of IF statement

1 Answers1