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.