Is it safe to use N prefix for regular ASCII character-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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.