What is the meaning of the prefix N in T-SQL statements and when should I use it?
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?
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?
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.
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 - ā) then it will fail or suffer data corruption.
Assuming the value is nvarchar type for that only we are using N''
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- What is the difference between "INNER JOIN" and "OUTER JOIN"?
- What is the difference between UNION and UNION ALL?
- What is the difference between varchar and nvarchar?
- Should I use != or <> for not equal in T-SQL?
- What is the best way to auto-generate INSERT statements for a SQL Server table?
- When should I use CROSS APPLY over INNER JOIN?
- How can I do 'insert if not exists' in MySQL?
- How to Join to first row
- How do I UPDATE from a SELECT in SQL Server?
- SELECT INTO a table variable in T-SQL
Nonly when i am using following datatypesCHAR, VARCHAR or TEXT?? because ` NCHAR, NVARCHAR or NTEXT ` are inherently store UNICODE i do not require to add it seperatly ....does it true?databases aspfaq comredirects to extremely dubious websites (fkref com,za1 zeroredirect1 com,i0z13 trackvoluum com), which have been marked by our corporate firewall as pornography, malicious sites and spam URLs. I've contacted the aspfaq.com owner and edited the answer to remove the link.Nis Unicode" seems unnuanced. Also MS: consider a column defined as NVARCHAR(100) that stores 180 bytes of Japanese characters... the column data is encoded using UCS-2 or UTF-16; 2 bytes per char. Converting the column to VARCHAR(200) [does not] prevent data truncation, [because] Japanese characters require 3 bytes in UTF-8. The column must be defined as VARCHAR(270) to avoid data loss through data truncation. (heavily edited)