CODEWITHSUNDEEP
sqlsql-server
Steam
Steam

Reputation: 9856

The 'N' in the where clause of SQL

I saw this query somewhere - 

SELECT *
FROM HR.Employees
WHERE country <> N'JAP';

What does that "N" mean ? I am not sure if this is valid only for SQL server.

Upvotes: 18

Views: 21856

Answers (2)

Steam
Steam

Reputation: 9856

Additional information taken from the book - http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059

If you write an expression that involves operands of different types, SQL Server will have to apply implicit conversion to align the types. Depending on the circumstances, implicit conversions can sometimes hurt performance. It is important to know the proper form of literals of different types and make sure you use the right ones. A classic example for using incorrect literal types is with Unicode character strings (NVARCHAR and NCHAR types).

The right form for a Unicode character string literal is to prefix the literal with a capital N and delimit the literal with single quotation marks; for example, N'literal'. For a regular character string literal, you just delimit the literal with single quotation marks; for example, 'literal'. It’s a very typical bad habit to specify a regular character string literal when the filtered column is of a Unicode type, as in the following example.

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

Because the column and the literal have different types, SQL Server implicitly converts one operand’s type to the other. In this example, fortunately, SQL Server converts the literal’s type to the column’s type, so it can still efficiently rely on indexing. However, there may be cases where implicit conversion hurts performance. It is a best practice to use the proper form, like in the following.

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

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280510

The N stands for "National Character" and it means that the content of the string is Unicode.

You should be using Unicode (nchar/nvarchar) whenever you might come across proper names or other entities that can contain characters outside of the default ASCII character set. If you don't surround such strings with the N prefix, you will lose data. For example:

SELECT N'ук ферт хер', 'ук ферт хер';

Results:

-----------        -----------
ук ферт хер        ?? ???? ???

You should also be sure to use the N prefix in your WHERE or other clauses against n(var)char columns. When you don't use the N prefix, you could suffer serious performance issues due to implicit conversion.

Upvotes: 35

Related Questions