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

Question

Let's say I have a table that has a varchar field. If I do an insert like this:

INSERT MyTable 
 SELECT N'the string goes here'

Is there any fundamental difference between that and:

INSERT MyTable 
 SELECT 'the string goes here'

My understanding was that you'd only have a problem if the string contained a Unicode character and the target column wasn't unicode. Other than that, SQL deals with it just fine and converts the string with the N'' into a varchar field (basically ignores the N).

I was under the impression that N in front of strings was a good practice, but I'm unable to find any discussion of it that I'd consider definitive.

Title may need improvement, feel free.

Answer 1

You should prefix strings with N when they are destined for an nvarchar(...) column or parameter. If they are destined for a varchar(...) column or parameter, then omit it, otherwise you end up with an unnecessary conversion.

It's definitely not a "best practice" to stick N in front of every string regardless of what it's for.

Answer 2

From INSERT (Transact-SQL)

When referencing the Unicode character data types nchar, nvarchar, and ntext, 'expression' should be prefixed with the capital letter 'N'.

Also have a read at Why do some SQL strings have an 'N' prefix?

And

Server-Side Programming with Unicode

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.

Answer 3

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.