difference between varchar and nvarchar

• nabajyoti.b

  SSCrazy

  Points: 2177

  More actions

  March 17, 2009 at 11:03 am

  #219169

  To help to my understand :

  what i think if we declare a col as varchar(10) or nvarchar(10) we can take strings with 10 characters .

  But the difference really comes into play where we have a unicode character in the middle.

  nvarchar can handle 8 simple charaters + 1 unicode(1 + 1) character

  but varchar cannot handle.

  PLease add/modify/treat my understanding.....

  Thanks ... I have started lovin this forum

• GSquared

  SSC Guru

  Points: 260824

  More actions

  March 17, 2009 at 11:09 am

  #961243

  Both can do 10 characters. One takes 10 bytes for that, the other takes 20 (plus overhead in both cases). The 10-byte one, varchar, can only hold ASCII type characters, the other, nvarchar, can hold up to 10 characters of either ASCII or non-ASCII or a mix of the two. So, the nvarchar one could hold 10 Chinese characters, or 5 Chinese and 5 Latin, or 10 Latin, or any combination like that.

  Make sense?

  And, yeah, this forum rocks.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• nabajyoti.b

  SSCrazy

  Points: 2177

  More actions

  March 17, 2009 at 11:22 am

  #961255

  pressing ctrl + 1236 givea character, which i believe is a unicode(correct me if i am wrong).

  now i tried to enter all 10 characters of these into a table with a column of varchar(10).

  Any Idea?

  Thanks...

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 17, 2009 at 11:48 am

  #961279

  SQL Server cuts the rest of the bytes.

  Try this:

  DECLARE @t TABLE (txt VARCHAR(12), ntxt NVARCHAR(12))

  INSERT INTO @t VALUES (N'????? ??????', N'????? ??????')

  SELECT txt, DATALENGTH(txt) txt_bytes, ntxt, DATALENGTH(ntxt) ntxt_bytes

  FROM @t

  Greets

  Flo

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  March 18, 2009 at 1:36 pm

  #962221

  Hi

  The shown "text" is only a invalid value. Try this extension of my previous example to see the written bytes:

  DECLARE @t TABLE (txt VARCHAR(12), ntxt NVARCHAR(12))

  INSERT INTO @t VALUES (N'????? ??????', N'????? ??????')

  SELECT txt,

  DATALENGTH(txt) txt_length,

  CONVERT(VARBINARY(100), txt) txt_binary,

  ntxt,

  DATALENGTH(ntxt) ntxt_length,

  CONVERT(VARBINARY(100), ntxt) ntxt_binary

  FROM @t

  Please any further technical questions within the forum. I'm quiet sure that I'm not one of the best members here so you might exclude the best solution ;-).

  Greets

  Flo