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

Question

I have seen prefix N in some insert T-SQL queries. Many people have used N before inserting the value in a table.

I searched, but I was not able to understand what is the purpose of including the N before inserting any strings into the table.

INSERT INTO Personnel.Employees VALUES(N'29730', N'Philippe', N'Horsford', 20.05, 1), 

What purpose does this 'N' prefix serve, and when should it be used?

Answer 1

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?

Answer 2

Let me tell you an annoying thing that happened with the N' prefix - I wasn't able to fix it for two days.

My database collation is SQL_Latin1_General_CP1_CI_AS.

It has a table with a column called MyCol1. It is an Nvarchar

This query fails to match Exact Value That Exists.

SELECT TOP 1 * FROM myTable1 WHERE  MyCol1 = 'ESKİ'    // 0 result 

using prefix N'' fixes it

SELECT TOP 1 * FROM myTable1 WHERE  MyCol1 = N'ESKİ'    // 1 result - found!!!! 

Why? Because latin1_general doesn't have big dotted İ that's why it fails I suppose.