1

i am trying to know the data is getting loaded in single byte or double byte data, please find the column data as pasted below:

حمد بخش مبار

select CTD_BENAC_NAME 
from NAME_TRAN_DETAILS 
where CTD_SEND_INS_REF ='FTSCWOK11074799'

it will gives the arabic data in the result, now i just want to know data is getting loaded in Single byte or double byte in Sqlserver, kindly advice how can i decide?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user1702625
  • 19
  • 1
  • 7
  • @Damien_The_Unbeliever my question is that pasted arabic data is loaded in to one of the column, then it is loading in to table with single byte or double byte,i have to know that , please advice – user1702625 Sep 27 '12 at 08:24
  • Why? Why not just store all such strings in nvarchar instead of using separate columns? If you're worried about space, use data compression. I wrote a few posts about Unicode compression in 2008 R2: https://sqlblog.org/search/SearchResults.aspx?q=Unicode+compression&s=18 – Aaron Bertrand Sep 27 '12 at 10:00

1 Answers1

1

It depends on the data type of that column. In SQL Server there are two kinds of character data types — regular and Unicode. Regular data types which are CHAR and VARCHAR. Unicode data types like NCHAR and NVARCHAR. For regular characters SQL Server uses one byte of storage for each character, whereas Unicode characters NVARCHAR uses two bytes per character.

You can also get the number of bytes used by a string using DATALEGNTH or LEN like so:

SELECT DATALENGTH(inputstring);

As I explained later, If this inputstring is of type NVARCHAR or N'string' DATALENGTH will give you the number of bytes. Whereas the LEN will give you the nuber of characters in a sting and that length is not necessarily the number of bytes.

Note that, another difference between LEN and DATALENGTH is that the LEN excludes trailing blanks while the DATALENGTH doesn't. Also, be careful with theses functions because it would depend in the collation because Varchar = single byte is not always true as pointed out by @MartinSmith comment below.

Here is a demo for that, you can try it yourself.

DEMO

Community
  • 1
  • 1
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • thanks for your promt reply, yeah i have done it is given as 24, but i dunno whether it is single byte or double byte, please advice – user1702625 Sep 27 '12 at 08:22
  • @user1702625 Sorry the `VARCHAR` data types use one byte for each character but the `nvarchar` used double bytes to store each character as I explained in my updated answer. – Mahmoud Gamal Sep 27 '12 at 08:24
  • so len gives 12 and datalength gives as 24, so i can conclude that it is loaded in to database as double byte right? – user1702625 Sep 27 '12 at 08:34
  • @user1702625 Yes that what I explained, this string is `NVARCHAR` not `VARCHAR` if this string is `VARCHAR` then the two functions `LEN` and `DATALENGTH` would give you the same number. – Mahmoud Gamal Sep 27 '12 at 08:35
  • 2
    [Varchar = single byte is not always true](http://stackoverflow.com/questions/176514/what-is-the-difference-between-char-nchar-varchar-and-nvarchar-in-mssql/8250586#8250586). Also `LEN` and `DATALENGTH` would not give the same result even on single byte collations if the string has trailing spaces. – Martin Smith Sep 27 '12 at 08:35
  • @MartinSmith Thanks for the tip, So if we remove the trailing space and watch out the collation this will be true? see my edit please. – Mahmoud Gamal Sep 27 '12 at 08:56