What Is the Purpose of Putting an 'N' in Front of Function Parameters in Tsql

What is the purpose of putting an 'N' in front of function parameters in TSQL?

It indicates a "nationalized" a.k.a. unicode string constant.

http://support.microsoft.com/kb/239530

When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N, as documented in the SQL Server Books Online topic "Using Unicode Data".

http://msdn.microsoft.com/en-us/library/aa276823%28SQL.80%29.aspx

nchar and nvarchar

Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

nchar(n)

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

nvarchar(n)

Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

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

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?

is there a downside to putting N in front of strings in scripts? Is it considered a best practice?

Short answer: fine for scripts, bad for production code.

It is not considered a best practice. There is a downside, it creates a minuscule performance hit as 2 byte characters are converted to 1 byte characters.

If one doesn't know where the insert is going, or doesn't know where the source text is coming from (say this is a general purpose data insertion utility that generates insert statements for an unknown target, say when exporting data), N'foo' might be the more defensive coding style.

So the downside is small and the upside is that your script code is much more adaptable to changes in database structure. Which is probably why you see it in bulk data-insert scripts.

However, if the code in question is something meant for re-use in an environment where you care about the quality of the code, you should not use N'the string' because you are adding a conversion where none is necessary.

SQL Server query: what is the meaning of 'N' preceding a string?

Unicode string constants that appear in code executed on the server, such as in stored procedures and triggers, must be preceded by the capital letter N. This is true even if the column being referenced is already defined as Unicode. Without the N prefix, the string is converted to the default code page of the database. This may not recognize certain characters.

For example, the stored procedure created in the previous example can be executed on the server in the following way:

EXECUTE Product_Info @name = N'Chain'

The requirement to use the N prefix applies to both string constants that originate on the server and those sent from the client.

N prefix before string in Transact-SQL query

The following articles have some good information on the question. The short answer is just that there's a type mismatch between the unicode column and non-unicode string literal you're using. From the KB article, it looks like omitting the N prefix might still work in some cases, but it would depend on the code page and collation settings of the database. That might explain the change in behavior, if you were previously having success with the no-prefix method.

https://support.microsoft.com/en-us/kb/239530

why does t-sql use the N prefix for nvarchar string literals?

As indicated by this MSDN article, N stands for national character.

SQL use Unicode N in a Stored Procedure with variable

Use Nvarchar data type in table's fields and stored procs parameters.

ADDED

See this link, maybe this will help you.

SQL: Whats the difference between ... WHERE O.type = N'U' ... and ... WHERE O.type = 'U'

The N ensures the value stated is treated as unicode

---

Related Topics