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.

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

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.