locked
table Size calculations RRS feed

  • Question

  • I am not sure how the size is calculated. I tried to follow the formula defined in the http://msdn.microsoft.com/en-us/library/ms189124.aspx however, when I inserted 100000 records, i am not getting the size that comes out of the formula.

    I am assuming that the length value I am getting from the sp_help command for the table is actual byte size for the field.

    Can someone explain what should I enter as size if my data type is VARCHAR(10) or CHAR(10).

    Should it be 10 bytes or 10*2 bytes or what?

     

    Thursday, March 3, 2011 7:21 PM

Answers

  • >>>Can someone explain what should I enter as size if my data type is VARCHAR(10) or CHAR(10).

    CHAR(n) --one byte per character n even if partially unused

    VARCHAR(n)one byte per character is stored.Declared but unsued characters do not consume storage


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Bravo2007 Monday, March 7, 2011 7:35 PM
    Sunday, March 6, 2011 8:09 AM

All replies

  • For char(10) it will be 10 bytes. For varchar(10) it's trickier. There is a good info in this blog post

    How SQL Server stores data
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, March 4, 2011 12:06 AM
    Answerer
  • >>>Can someone explain what should I enter as size if my data type is VARCHAR(10) or CHAR(10).

    CHAR(n) --one byte per character n even if partially unused

    VARCHAR(n)one byte per character is stored.Declared but unsued characters do not consume storage


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, March 6, 2011 8:09 AM
  • >>>Can someone explain what should I enter as size if my data type is VARCHAR(10) or CHAR(10).

    CHAR(n) --one byte per character n even if partially unused

    VARCHAR(n)one byte per character is stored.Declared but unsued characters do not consume storage


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Bravo2007 Monday, March 7, 2011 7:35 PM
    Sunday, March 6, 2011 8:09 AM