CODEWITHSUNDEEP
sql-server
user8179431
user8179431

Reputation:

Nvarchar in SQL Server

Why do we need to add N'பட்டப்பகலில்' for unicode strings in nvarchar.

We are inserting unicode as well as non unicode string in a column that is nvarchar type, Since type of a column is nvarchar why do we need to add N'' before unicode string, we will not be knowing what is unicode and non unicode string .

Do we have any way we can insert unicode and non unicode in nvarchar field without mentioning N in it.

Upvotes: 0

Views: 1684

Answers (3)

Gabriel Luci
Gabriel Luci

Reputation: 41008

If the column is type nvarchar, then everything is stored as unicode. Even if you use characters that would not need unicode to store, they are still stored as unicode. So you can't insert "non-unicode" strings.

You can omit the N if you'd like, if you're not using any special characters. But SQL Server will just convert it to unicode before storing it.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

Since type of a column is nvarchar why do we need to add N''

By the time the value is assigned to a column or variable, it's already been processed as a string literal.1

It's therefore far too late to consider the type of the column or variable to decide how to process it. Indeed, it may not be assigned to a column or variable at all - it may be part of a larger expression.

That's why you have to separately indicate the type of each literal. But as others have commented, there's no great penalty in just marking all of your literals as unicode (unless you're working with lots of ~6000 character literals).

1This is the same as many other languages where the type of an expression has to be determined without any regard to if it's going to be assigned to a particular variable, and therefore the type of such a variable does not play a part in determining the type of the expression.

Upvotes: 3

Thom A
Thom A

Reputation: 96014

Putting N at the start of a literal string means it is an nvarchar. The difference between 'abc' and N'abc' is that the first literal string is a varchar(3), and the second is a nvarchar(3).

Why is it important? Well one reason is that an nvarchar is double the size of a varchar, so creating a good query plan for the size of the values you have is incredibly important. Also, an nvarchar can only have a maximum length of 4000 before you need to use MAX. A varchar can have up to 8000.

Upvotes: 2

Related Questions