locked
SQL Server stores data in multiple of 8??? RRS feed

  • Question

  • Hi There,

    Yesterday, I heard from someone saying SQL Server stores values of VARCHARs and NVARCHARs in increments of 8

    So, VARCHAR(63) or VARCHAR(65) would be slower than VARCHAR(64). is this RIGHT?

    I hunted for such thing over the net but couldnt find anything.

    So, I disagree to this, but as I need a firm stand on it thus asking the question to Experts. :)

    If i am wrong, i would be glad to know the details.

    Friday, August 3, 2012 7:42 AM

Answers

All replies

  • Hi,

    I recommend to read the Pages and Extents architecture of SQL Server + articles from Paul Randal about on-disk structures.

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Friday, August 3, 2012 8:46 AM
  • Thanks for your help Janos.

    The document was helpful in understanding the storage, structure and architecture of SQL Server.

    Monday, August 6, 2012 7:27 AM
  • >>>So, VARCHAR(63) or VARCHAR(65) would be slower than VARCHAR(64). is this RIGHT?

    No, sql server consumes bytes  for Variable-length character data as it was store and not declared.For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.

    http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar(n)-anymore/


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Monday, August 6, 2012 7:36 AM
  • Thanks Uri,

    Yes, you are correct. VARCHARs are variable Length Characters.

    But, per my knowledge, Suppose, if i am storing any data between VARCHAR(1) till VARCHAR(255), to store the address or length, it will consume say 1 byte.. ( like 2^8 = 256 --> 0 to 255)

    and for any length of data beyond size 255, it will consume 2 bytes to store length or size which essentially means it will consume 1 more byte that makes the number of bytes used for storing address to be 2 bytes.

    Now since 2 bytes have total 16bits, i believe, using 2 bytes, we can store a data of length (2^16) -1 i.e, 65535.

    So this, way, for storing any length more than 255, a extra byte is consumed.

    this is what i understand.

    Though, this under my knowledge, would be worthless in terms of performance improvement....

    Please let me know if I am not correct.. :)

    Thanks for your response..

    Cheers$Life~

    Anupam

    Monday, August 13, 2012 1:08 PM
  • Thanks Ishtiyaq for sharing.....

    I am aware of the page concept and its storage.

    But i was pretty confused for this VARCHAR instance, as i heard about it and didnt get anything relevant over internet.. :)

    Cheers$Life~

    Anupam

    Monday, August 13, 2012 1:11 PM