Refer:http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html
You may have seen Transact-SQL code that passes strings around using a N prefix. This denotes, the subsequent string is in Unicode (the nth actually stands for national language character set). Which means that is passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. See article #2354 for a comparison of these data types.
Unicode is typically used in database applications which be designed to facilitate code pages which extend beyond the Eng Lish and Western Europe code pages (Erland Sommarskog, a native of Sweden, refers to this set as "Germanic and romance Lan Guages "), for example Chinese. Unicode is designed so, extended character sets can still "fit" into database columns. What's this means was that Unicode character data types was limited to half the space, because each byte actually takes B Ytes (Unicode is sometimes referred to as "Double-wide"). For more information on Unicode, seeunicode.org. Note that there is many encoding schemes in the Unicode standard, but SQL Server is only supports one:utf-16.
While using Unicode are a design choice you can do in building your own applications, some facilities in SQL server Requi Re it. One example is sp_executesql. If you try the following:
| EXEC sp_executesql ' SELECT 1 ' |
You'll get this error:
Server:msg 214, Level A, state 2, Procedure sp_executesql, line 1
Procedure expects parameter ' @statement ' of type ' Ntext/nchar/nvarchar '. |
You can get around this in the ways:
--(a) using the N prefix
EXEC sp_executesql N ' SELECT 1 '
--(b) using a variable
DECLARE @sql NVARCHAR (100)
SET @sql = N ' SELECT 1 '
EXEC sp_executesql @sql |
Note that implicit conversion makes, the N prefix optional in case (b); However, for legibility and consistency, you should always use the prefix when defining Unicode strings. One reason is this leaving it off can actually change your data if it contains Unicode characters (losing the additional I nformation), as in the following example:
DECLARE @n NVARCHAR (10)
SET @n = ' a '
PRINT @n
SET @n = n ' a '
PRINT @n |
The first assignment, which didn ' t use the N prefix, gets printed as a regular a. Only the second maintains the character is actually supposed to be represented. As can imagine, if you is intending to support data entry in foreign languages and code pages, you'll likely need t o Test for Unicode support (making sure this such columns support Unicode, and that data won ' t is lost when passed into St ORed procedures, functions, etc.). Note that your application would need to handle Unicode as well; For example, when you try to print this character from ASP ...
... it actually prints out the string AA. (This result might depend on your codepage and regional settings.) So you might consider translating your data to its ASCII equivalent, e.g. a = & #0257;.
Another reason you want to avoid implicit conversion was that there was some potentially serious performance issues. Consider the following quite simple repro:
Use tempdb GO to create TABLE a ( b VARCHAR (3), C NVARCHAR (3)) CREATE index B on a (b) create index C on a (c) GO SET NOCOUNT on insert a select ' foo ', n ' foo ' INSERT a Select ' Bar ', n ' bar ' DECLARE @b VARCHAR (3), @c NVARCHAR (3) Select @b = ' foo ', @c = N ' foo ' select * from a WHERE B = @b SELECT * from a WHERE B = @c SELECT * from a WHERE c = @b select * from a WHERE c = @c select * from a where B like @b SELECT * from a where B like @c SELECT * from a where C like @b SELECT * from a where C like @c DROP TABLE A
Paste the code into Query Analyzer, turn execution plan on, and let her rip. You'll observe the following breakdown of percentage of work (roughly, depending on your hardware):
| varchar = varchar |
4.48% |
| VARCHAR = NVARCHAR |
13.31% |
| NVARCHAR = VARCHAR |
4.48% |
| NVARCHAR = NVARCHAR |
4.48% |
| varchar like varchar |
4.48% |
| VARCHAR like NVARCHAR |
13.31% |
| NVARCHAR like VARCHAR |
4.48% |
| NVARCHAR like NVARCHAR |
4.48% |
Now, that's not the whole story; We all know that there is many other factors, such as I/O, that would impact the actual time each portion of the query Tak Es. The key is a. Implicit conversion *can* cause a table scan instead of an index seek, and on larger tables this can reall Y hurt. While it's important to understand what this happens and in which scenarios, my recommendation was to match your character-b Ased datatypes as explicitly as possible.
One other thing to watch out for:your the database may be using Unicode without your knowledge. If you upsize from Access to SQL Server, for example, character-based text columns might is translated to Unicode (I belie ve this is a catch-all technique; In case Access is storing Unicode strings, or if you might is storing Unicode strings later, you won ' t lose data or Requi Re changes). I think the Access upsizing Tools should be updated to force a conscious choice, so that you aren ' t wasting space for Noth ING, and so, you know that's made a decision at all.
For a further thorough discussion of Unicode and the N prefix, please see KB #239530, this MSDN article, and this Google thre Ad.
In other RDBMS platforms, or in the ANSI and/or ISO specifications, you might see prefixes other than N being used against Values. (current versions of SQL Server is only support Unicode.) Here is the additional monikers I am aware of:
| B |
This is used to denote a BINARY string expressed in bits (0 and 1 only) |
| X |
This is used to denote a BINARY string expressed in hexadecimal (0-F) |
Related articles
Why does some SQL strings has an ' N ' prefix?
