0

I know that N prefix means Unicode, and regular character takes one byte to store and Unicode character takes two bytes (sometimes four bytes if surrogate pair is needed) to store

I saw some SQL book uses N for ASCII characters such as:

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE lastname = N'davis';

but what if lastname is a column is an ASCII column, does using N prefix causes the value from lastname automatically to be converted to Unicode so that it can be compared with N'davis'? because it is more sense to me that we need to use N prefix on both side such as:

SELECT empid, firstname, lastname
FROM HR.Employees
WHERE N`lastname = N'davis';  -- sth like this, or it could be a system function like WHERE N(lastname) = N'davis';

And what happen if users (who don't know whether the column is ASCII or Unicode column) want to update the table:

UPDATE HR.Employees
SET lastname = N'davis'
WHERE empid= 2022

and if the lastname column only supports ASCII?

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • Why don't you try and see what happens? – jarlh Feb 02 '22 at 07:35
  • In Unicode the ASCII character range is a unity transform, i.e.: 0x01-x7F are mapped directly to U+0001-U+007F. Using National character literals (strings with the N prefix) isn't going to store Unicode encoded text into char/varchar/text data types, those values will be converted according to the database's default collation - or the column's collation if it has one specified. The danger is that any characters outside the collation will be silently converted to a question mark, `?`, by SQL Server so you could (likely will) lose information if you're trying to insert international characters. – AlwaysLearning Feb 02 '22 at 07:51

2 Answers2

1

The N prefix is only valid for string literals.

WHERE N'lastname' = N'davis'

is just comparing two strings. It is not applying anything to the "lastname" column.

For a string literal in a WHERE clause you should only generally use the N prefix if you know that the column you are comparing with is of nvarchar/nchar datatype.

The danger of using

WHERE lastname = N'davis';

in the case that lastname is varchar is that this will cause an implicit cast on the column and this can prevent an index seek from happening in some collations.

An exception to this would be if the column is varchar and uses a collation different from your database's default collation. Then you may need to use the N prefix on the literal to avoid characters being lost that could actually exist in the column. A particular case of this will be varchar columns using UTF8 collations as they will then support the full range of Unicode.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • NVARCHAR OR _NCHAR_ ! – SQLpro Feb 02 '22 at 11:34
  • Ah yes, I did originally have `n[var]char` but thought that might be a bit opaque and forgot to add that when removing the brackets – Martin Smith Feb 02 '22 at 11:37
  • By the way, some performance trouble can appear when using systematically N prefix on ASCII strings... – SQLpro Feb 02 '22 at 11:41
  • This is exactly what my answer already says. It causes implicit casts and can prevent index seeks – Martin Smith Feb 02 '22 at 11:42
  • @Martin Smith that's one thing I really don't understand, you know 'davis' is ASCII character, then why you need to use N'davis'? can't we just do `WHERE lastname = 'davis', and if lastname is an unicode column, and the sql server engine should convert ASCII 'davis' to unicode version of 'davis' and then do the comparsion? –  Feb 03 '22 at 11:05
0

With ASCII only character strings you can safely compare, mix and assign [VAR]CHAR and N[VAR]CHAR expressions in T-SQL. The engine will handle conversions with no problem (unless you'll assign a long value to a shorter column / variable). For example

declare @lastName   char(5) = N'Davis';
declare @NlastName nchar(5) =  'Davis';
select  @lastName ln, @NlastName nln where @lastName = @NLastName;

Returns

ln  nln
Davis   Davis

Nevertheless I would strongly advice using those types consistently in your code to prevent a future disaster.

Serg
  • 22,285
  • 5
  • 21
  • 48