Post reply

data types

  • March 9, 2005 at 9:50 am

    #163503

    Does anyone know if there is a way to know the bytes that takes each row of a table?

    I have to find the best data types for a very large database, so i would like to estimate the size of a row to evalute the data type to use... for exemple, if i use a varchar(30) and a string is 20 character long, how many bytes it will takes? Another question is aboout the header of data types: how many bytes it takes?

    I'll be pleasing if someone could answer these question, or tell me a link to find a few documenattion about it.

    Thanks in advance!

     

    Marco

  • March 9, 2005 at 10:09 am

    #546188

    if the string is 20 then 20 unless it's nvarchar then it would be 40... plus 2 bytes overhead to keep the lenght of the string.

  • March 9, 2005 at 10:32 am

    #546193

    try DBCC SHOWCONTIG ('TableName')  WITH TABLERESULTS

    Look at AverageRecordSize, MaximumRecordSize, MinimumRecordSize

    Cheers!


    * Noel

  • March 10, 2005 at 1:49 am

    #546301

    I believe a complete calculation method and reference to the size of every datatype can be found in BOL. 'estimating table size' ... good luck!

    Stijn

  • March 10, 2005 at 9:05 am

    #546403

    Datalength(expression) will work to determine the size of the data in a row.

    Datatype is stored in 2 bytes, but only once for each column.

    For var* and nvar* data types, actual length is stored in 2 bytes.

    Indexes also take up space.

     

  • March 11, 2005 at 4:14 am

    #546572

    Thanks for your answers!

    I've fianally found in BOL the way to estimate the table dimensions: following the instruction of BOL (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp) i've created an excel application that takes as input the data types, number of columns and of rows, ecc.., and gives as result the estimated dimension of the table.

    I can't understand why, if the table doesn't include variable lenght data types, I should find the exact dimensions, but my excel application gives a result that doesn't match perfectly (error of about 2%).

    I've verifyed many times my application, and it's correct... maybe enterprise manager give a table size dimension not exactly? Or what??

    Thanks again for your help!

    Bye

    Marco

Viewing 6 posts - 1 through 5 (of 5 total)

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