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

Question

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

I searched, but I can't 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' serve as prefix, and when should it be used? 

Answer 1

It's simply 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.

Difference:

varchar: Variable-length, non-Unicode character data. The database collation decides which code page the data is stored using.

nvarchar: Variable-length Unicode character data. It depends on the database collation for comparisons. 

Want to be a SQL expert? Come and join this SQL server training and certification by Intellipaat.

If you are a beginner and want to know more about SQL, then do refer to the below SQL tutorial video that will help you out in a better way:

Answer 2

It implies through the N prefix used within T-SQL statements that the string is data-type Unicode, and due to this, it could actually store characters from most the languages using UTF-16 encoding. For example, N'Text' may imply that "Text" is a Unicode type, whereas 'Text' sans the prefix is an English type.

Use N when working with strings that may contain non-English characters or special characters from other languages because N ensures compatibility and proper storage of international text. This is very important in databases where there is multilingual data or where character encoding varies between different sources of data.