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

User · Answer

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 = 'ESKI'  

// 0 result

using prefix N'' fixes it

SELECT TOP 1 * FROM myTable1 WHERE  MyCol1 = N'ESKI'  

// 1 result - found!!!!

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

User · Answer

Assuming the value is nvarchar type for that only we are using N

User · Answer

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

User · Answer

1  Performance   Assume your where clause is like this   WHERE NAME  JON    If the NAME column is of any type other than nvarchar or nchar  then you should not specify the N prefix  However  if the NAME column is of type nvarchar or nchar  then if you do not specify the N prefix  then  JON  is treated as non-unicode  This means the data type of NAME column and string  JON  are different and so SQL Server implicitly converts one operand   s type to the other  If the SQL Server converts the literal   s type to the column   s type then there is no issue  but if it does the other way then performance will get hurt because the column s index  if available  wont be used   2  Character set   If the column is of type nvarchar or nchar  then always use the prefix N while specifying the character string in the WHERE criteria UPDATE INSERT clause  If you do not do this and one of the characters in your string is unicode  like international characters - example - a  then it will fail or suffer data corruption

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

Examples related to sql

Examples related to sql-server

Examples related to tsql