none
navrchar definition shows "The storage size, in bytes, is two times the number of characters entered + 2 bytes"

    Question

  • Description: - We have a product in that for a field we store Chinese or Arabic character.

    Data Base Version: -Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)  

    Requirement: - Now we need to store these language supports characters in database. For that we are going to use nvarchar data type.

    Problem: - We are not able to understand exact definition of nvarchar. Which we have read from

    http://technet.microsoft.com/en-us/library/ms186939.aspx

    “nvarchar [ ( n | max ) ]

    Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.“

    What is the mean of this definition?

    Please enlighten above definition through below example

    Case I - CREATE TABLE FieldsData(id nvarchar(1) NULL ) 

    INSERT INTO FieldsData (ID) VALUES(N'')

     

    This I understand from definition: Here what I think, actual length of data is 1. Then according to definition

    1*2+2 bytes = 4 bytes. Is this going to store in database?

     

    Case II - CREATE TABLE FieldsData(id nvarchar(2) NULL ) 

    INSERT INTO FieldsData (ID) VALUES(N'何何')

     

    This I understand from definition: Here what I think, actual length of data is 2. Then according to definition

    2*2+2 bytes = 6 bytes. Is this going to store in database?

     

     

    I will be really thankful.

     

     

    Wednesday, September 11, 2013 4:38 AM

Answers

  • So this statement makes a nvarchar column that can store a string of 2 characters 

    >>ID nvarchar(2) NULL 

    The calculation 2*2 + 2 bytes = 6 bytes shows the "actual space occupied in the disk (storage media)" when you insert a 2 character string into that column and this is a space occupied by a "single row"(assuming you have only one column)


    Satheesh



    Wednesday, September 11, 2013 8:58 AM
  • I think you are confused with the LENGTH and the size.

    Here, the Length allowed is only 2, so you can not have more than 2 character length, but the size of the column is 2*2+2=6 Bytes. 

    Hope this is clear to you.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by S.P Singh Wednesday, September 11, 2013 9:58 AM
    Wednesday, September 11, 2013 8:49 AM

All replies

  • Hi S.P,

    In varchar(Unicode chars)  basically each character takes 1 byte of storage, where nvarchar(non - unicode chars) takes 2 byte for each storage of per character.

    where n represent the number to character it need to store for example: varchar(1) or nvarchar(1), can stored only one character but width of charater will be 1 byte in varchar(1) and 2 bytes in nvarchar(1).

    for you example you can find the size as such:

    DECLARE @A NVARCHAR(2) = N'何何'
    SELECT DATALENGTH(@A) -- Use 4bytes
    
    DECLARE @B VARCHAR(2) = '何何'
    SELECT DATALENGTH(@B)  --  Use 2bytes
    
    DECLARE @C NVARCHAR(1) = N'何'
    SELECT DATALENGTH(@C)  -- Use 2bytes
    
    DECLARE @D VARCHAR(1) = '何'
    SELECT DATALENGTH(@D)  --  Use 1bytes
    For more info follow this link - > http://sqlhints.com/2011/12/23/difference-between-varchar-and-nvarchar/


    Regards Harsh



    • Edited by Harsh Kumar Wednesday, September 11, 2013 4:57 AM
    Wednesday, September 11, 2013 4:55 AM
  • Thanks Harish, but here question is different. I am not looking for difference. It is regarding to understand definition of nvarchar in above cases.

    Please look if you can enlighten. I will be thankful.

    Wednesday, September 11, 2013 5:48 AM
  • Hello S.P Sing,

    You calculation is exactly correct.

    Its twice the number of character +  2 bytes for offset.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by S.P Singh Wednesday, September 11, 2013 8:22 AM
    • Unmarked as answer by S.P Singh Wednesday, September 11, 2013 8:24 AM
    Wednesday, September 11, 2013 5:52 AM
  • This I understand from definition: Here what I think, actual length of data is 1. Then according to definition

    1*2+2 bytes = 4 bytes. Is this going to store in database?

     -->> It will be 1*1+2 =3 bytes when VARCHAR is used

    Case II - CREATE TABLE FieldsData(id nvarchar(2) NULL ) 
    INSERT INTO FieldsData (ID) VALUES(N'何何')

     

    This I understand from definition: Here what I think, actual length of data is 2. Then according to definition

    2*2+2 bytes = 6 bytes. Is this going to store in database?   

    -->> It will be 2*1+2 =4 bytes when VARCHAR is used

    Yes that's right

    Satheesh

    Wednesday, September 11, 2013 5:55 AM
  •  

    Please have a look of these queries

     

    CREATE TABLE FieldsDataValue(ID nvarchar(2) NULL )

     

    Case: -

    According to this query, it is eligible for storing

     

    2*2 + 2 bytes = 6 bytes

    INSERT INTO FieldsDataValue(ID) VALUES(N'AAA')

    I think in insert data length is 6 byte. It is giving exception.


    Kindly enlighten on this point.



    • Edited by S.P Singh Wednesday, September 11, 2013 9:58 AM
    Wednesday, September 11, 2013 8:41 AM
  • I think you are confused with the LENGTH and the size.

    Here, the Length allowed is only 2, so you can not have more than 2 character length, but the size of the column is 2*2+2=6 Bytes. 

    Hope this is clear to you.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by S.P Singh Wednesday, September 11, 2013 9:58 AM
    Wednesday, September 11, 2013 8:49 AM
  • So this statement makes a nvarchar column that can store a string of 2 characters 

    >>ID nvarchar(2) NULL 

    The calculation 2*2 + 2 bytes = 6 bytes shows the "actual space occupied in the disk (storage media)" when you insert a 2 character string into that column and this is a space occupied by a "single row"(assuming you have only one column)


    Satheesh



    Wednesday, September 11, 2013 8:58 AM