T-SQL语句中前缀N的含义是什么？

本文翻译自：What is the meaning of the prefix N in T-SQL statements?

I have seen prefix N in some insert T-SQL queries. 我在某些插入T-SQL查询中看到前缀N。 Many people have used N before inserting the value in a table. 在表中插入值之前，许多人都使用了N

I searched, but I was not able to understand what is the purpose of including the N before inserting any strings into the table. 我搜索，但我无法理解什么是包括的目的N插入任何字符串到表之前。

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

---

#1楼

参考：https://stackoom.com/question/g3y4/T-SQL语句中前缀N的含义是什么

---

#2楼

It's declaring the string as nvarchar data type, rather than varchar 它将字符串声明为nvarchar数据类型，而不是varchar

You may have seen Transact-SQL code that passes strings around using an N prefix. 您可能已经看到了Transact-SQL代码，该代码使用N前缀传递字符串。 This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). 这表示后续的字符串采用Unicode（N实际上代表本国语言字符集）。 Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. 这意味着您正在传递一个NCHAR，NVARCHAR或NTEXT值，而不是CHAR，VARCHAR或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. 使用字母N作为前缀Unicode字符串常量。没有N前缀的情况下，该字符串将转换为数据库的默认代码页。 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: 如果您想知道这两种数据类型之间的区别，请参阅以下SO文章：

What is the difference between varchar and nvarchar? varchar和nvarchar有什么区别？

---

#3楼

假设值是nvarchar类型，那是因为只有我们在使用N''

---

#4楼

Let me tell you an annoying thing that happened with the N' prefix - I wasn't able to fix it for two days. 让我告诉您N'前缀发生的一件令人讨厌的事情-我两天都无法修复它。

My database collation is SQL_Latin1_General_CP1_CI_AS . 我的数据库排序规则是SQL_Latin1_General_CP1_CI_AS 。

It has a table with a column called MyCol1 . 它具有一个包含名为MyCol1的列的表。 It is an Nvarchar 这是一个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 使用前缀N''修复它

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. 因为latin1_general没有大的点缀İ ，所以我认为这就是失败的原因。

---

#5楼

1. Performance: 1.性能：

Assume your where clause is like this: 假设您的where子句是这样的：

WHERE NAME='JON'

If the NAME column is of any type other than nvarchar or nchar, then you should not specify the N prefix. 如果NAME列的类型不是nvarchar或nchar，则不应指定N前缀。 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. 但是，如果NAME列的类型为nvarchar或nchar，则如果未指定N前缀，则'JON'被视为非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. 这意味着NAME列和字符串'JON'的数据类型不同，因此SQL Server将一个操作数的类型隐式转换为另一种。 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. 如果SQL Server将文字的类型转换为列的类型，则没有问题，但是如果以其他方式这样做，则性能会受到损害，因为将不使用列的索引（如果可用）。

2. Character set: 2.字符集：

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. 如果列的类型为nvarchar或nchar，则在WHERE条件/ UPDATE / INSERT子句中指定字符串时，请始终使用前缀N。 If you do not do this and one of the characters in your string is unicode (like international characters - example - ā) then it will fail or suffer data corruption. 如果不这样做，则字符串中的一个字符是unicode（例如国际字符-例如-ā），则它将失败或遭受数据损坏。