TSQL Prefixing String Literal on Insert - Any Value to This, or Redundant?

Question

I just inherited a project that has code similar to the following (rather simple) example:

DECLARE @Demo TABLE
(
    Quantity INT,
    Symbol NVARCHAR(10)
)

INSERT INTO @Demo (Quantity, Symbol)
SELECT 127, N'IBM'

My interest is with the N before the string literal.

I understand that the prefix N is to specify encoding (in this case, Unicode). But since the select is just for inserting into a field that is clearly already Unicode, wouldn't this value be automatically upcast?

I've run the code without the N and it appears to work, but am I missing something that the previous programmer intended? Or was the N an oversight on his/her part?

I expect behavior similar to when I pass an int to a decimal field (auto-upcast). Can I get rid of those Ns?

Answer 1

Your test is not really valid, try something like a Chinese character instead, I remember if you don't prefix it it will not insert the correct character

example, first one shows a question mark while the bottom one shows a square

select '作'

select N'作'

A better example, even here the output is not the same

declare @v nvarchar(50), @v2 nvarchar(50)

select @v = '作', @v2 = N'作'

select @v,@v2

Since what you look like is a stock table why are you using unicode, are there even symbols that are unicode..I have never seen any and this includes ISIN, CUSIPS and SEDOLS

Answer 2

Yes, SQL Server will automatically convert (widen, cast down) varchar to nvarchar, so you can remove the N in this case. Of course, if you're specifying a string literal where the characters aren't actually present in the database's default collation, then you need it.

It's like you can suffix a number with "L" in C et al to indicate it's a long literal instead of an int. Writing N'IBM' is either being precise or a slave to habit, depending on your point of view.

One trap for the unwary: nvarchar doesn't get automatically converted to varchar, and this can be an issue if your application is all Unicode and your database isn't. For example, we had this with the jTDS JDBC driver, which bound all parameter values as nvarchar, resulting in statements effectively like this:

select * from purchase where purchase_reference = N'AB1234'

(where purchase_reference was a varchar column)

Since the automatic conversions are only one way, that became:

select * from purchase where CONVERT(NVARCHAR, purchase_reference) = N'AB1234'

and therefore the index of purchase_reference wasn't used.

By contrast, the reverse is fine: if purchase_reference was an nvarchar, and an application passed in a varchar parameter, then the rewritten query:

select * from purchase where purchase_reference = CONVERT(NVARCHAR, 'AB1234')

would be fine. In the end we had to disable binding parameters as Unicode, hence causing a raft of i18n problems that were considered less serious.