T-SQL 语句中前缀 N 的含义是什么，我应该什么时候使用它？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10025032/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the meaning of the prefix N in T-SQL statements and when should I use it?
提问by Kartik Patel
I have seen prefix N in some insert T-SQL queries. Many people have used Nbefore inserting the value in a table.
我在一些插入 T-SQL 查询中看到了前缀 N。许多人N在将值插入表中之前使用过。
I searched, but I was not able to understand what is the purpose of including the Nbefore inserting any strings into the table.
我进行了搜索，但我无法理解N在将任何字符串插入表中之前包含 的目的是什么。
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?
这个“N”前缀有什么用途，什么时候应该使用？
回答by Curt
It's declaring the string as nvarchardata type, rather than varchar
它将字符串声明为nvarchar数据类型，而不是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.
您可能已经看到 Transact-SQL 代码使用 N 前缀传递字符串。这表示后续字符串是 Unicode（N 实际上代表国家语言字符集）。这意味着您传递的是 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. This default code page may not recognize certain characters.
用字母 N 前缀 Unicode 字符串常量。如果没有 N 前缀，字符串将转换为数据库的默认代码页。此默认代码页可能无法识别某些字符。
If you want to know the difference between these two data types, see this SO post:
如果您想知道这两种数据类型之间的区别，请参阅此 SO 帖子：
回答by bh_earth0
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. It is an Nvarchar
它有一个表，其中有一列名为MyCol1。它是一个Nvarchar
This query fails to match ExactValue 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 没有大点？这就是我想它失败的原因。
回答by variable
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. 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.
如果 NAME 列的类型不是 nvarchar 或 nchar，则不应指定 N 前缀。但是，如果 NAME 列的类型为 nvarchar 或 nchar，那么如果您不指定 N 前缀，则 'JON' 将被视为非 unicode。这意味着 NAME 列和字符串 'JON' 的数据类型不同，因此 SQL Server 隐式地将一个操作数的类型转换为另一种。如果 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. 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.
如果列的类型为 nvarchar 或 nchar，则在 WHERE 条件/UPDATE/INSERT 子句中指定字符串时始终使用前缀 N。如果您不这样做并且字符串中的一个字符是 unicode（如国际字符 - 示例 - ā），那么它将失败或遭受数据损坏。
回答by RickyRam
Assuming the value is nvarchar type for that only we are using N''
假设值是 nvarchar 类型，因为只有我们使用 N''
