Post reply

how to calculate a row size

  • February 17, 2005 at 9:55 am

    #163689

    Hi,

    I'm trying to estimate a row size. Let's say the table schema is shown below

    intID (int) - primary key and auto grow, vchJobNo (varchar(12)), tintPMID(tinyint), datStatusDate(datetime), chrUserID(char(10))

    I don't know how to convert these data types into byte. I looked around for the information about this but I couldn't find any. I'll appreciate your help. Thanks.

    Dong.

     

     

  • February 17, 2005 at 11:15 am

    #542896

    tinyint = 1

    smallint =2

    int = 4

    bigint = 8

    bit = 1 (for the first 8, then 2 bytes from 9 to 16, 3 from 17 to 24...)

    datetime = 8

    smalldatetime = 4

    any char or varchar will take one byte per character

    nchar and nvarchar will take up 2.

    So in this case it would be 35 (max)

  • February 18, 2005 at 4:13 am

    #543042

    May I add that this might turn out a phantastic occasion to explore the collected wisdom of the SQL Server Books Online (aka BOL, aka the online manual)?

    Never mind.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 18, 2005 at 8:43 am

    #543097

    This query gives you the row size of every table in the db.

    select o.name, sum(c.length)

    from sysobjects o,

         syscolumns c

    where o.id = c.id

      and o.type = 'U'

    group by o.name

    order by o.name

  • February 18, 2005 at 10:57 am

    #543128

    Thank you guys for your QUICK responses.

    I have a quetion for Remi on bit. You said bit = 1 byte (for the first 8...). What is 8 here?

    Dong.

  • February 18, 2005 at 11:06 am

    #543129

    As advised above, please read BOL, it's all there. From the BOL section titled "Bit":

    bit

    Integer data type 1, 0, or NULL.

    Remarks

    Columns of type bit cannot have indexes on them.

    Microsoft® SQL Server™ optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.

     

    Can't find BOL ? No problem, it's on your start menu:

  • February 18, 2005 at 11:09 am

    #543130

    8 bits = 1 byte (i.e. 10101010 = 8 bits or 1 byte)

    16 bits = 2 bytes, 24 bits = 3 bytes, etc.

  • February 18, 2005 at 11:14 am

    #543132

    Oh! They are all in there, BOL. I was lost before and now am found. Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply