What Is the Meaning of the Prefix N in T-SQL Statements and When Should I Use It

What is the meaning of the prefix N in T-SQL statements and when should I use it?

It's declaring the string as nvarchar data type, rather than varchar

You may have seen Transact-SQL code that passes strings around using
an N prefix. This denotes that the subsequent string is in Unicode
(the N actually stands for National language character set). Which
means that you are passing an NCHAR, NVARCHAR or NTEXT value, as
opposed to CHAR, VARCHAR or TEXT.

To quote from Microsoft:

Prefix Unicode character string constants with the letter N. Without
the N prefix, the string is converted to the default code page of the
database. This default code page may not recognize certain characters.

---

If you want to know the difference between these two data types, see this SO post:

What is the difference between varchar and nvarchar?

What does N' stands for in a SQL script ? (the one used before characters in insert script)

N is used to specify a unicode string.

Here's a good discussion: Why do some SQL strings have an 'N' prefix?

In your example N prefix is not required because ASCII characters (with value less than 128) map directly to unicode. However, if you wanted to insert a name that was not ASCII then the N prefix would be required.

INSERT [dbo].[TableName] ([Sno], [Name], [EmployeeId], [ProjectId], [Experience]) 
VALUES (1, N'Wāhi', 'ESD157', 'FD080', 7)

What is the meaning of an N before quotes in a SQL Server assignment?

From MSDN in remarks section

Saying that string is of Nvarchar type and can have unicode charcters

Prefix Unicode character string constants with the letter N. Without
the N prefix, the string is converted to the default code page of the
database. This default code page may not recognize certain characters.

The "N" prefix stands for National Language in the SQL-92 standard, and must be uppercase. If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string.

Here is more detailed information

You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server

what 'N' denotes here?

It specifies that the string is nvarchar data type instead of varchar

You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. See Article #2354 for a comparison of these data types.

Reference: http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

What is the purpose of putting an 'N' in front of function parameters in TSQL?

It indicates a "nationalized" a.k.a. unicode string constant.

http://support.microsoft.com/kb/239530

When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N, as documented in the SQL Server Books Online topic "Using Unicode Data".

http://msdn.microsoft.com/en-us/library/aa276823%28SQL.80%29.aspx

nchar and nvarchar

Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

nchar(n)

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

nvarchar(n)

Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

SQL Server T-SQL N prefix on string literal

It's declaring the string as nvarchar data type (Unicode), rather than varchar (8-bit encoding, including ASCII).

FYI, this is a duplicate of the question:
What is the meaning of the prefix N in T-SQL statements?

---

Related Topics