data types

Question

data types

Marco-214819

Newbie

Points: 7
More actions
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

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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.

noeld SSC Guru Points: 96590 More actions · Answer 2

try DBCC SHOWCONTIG ('TableName') WITH TABLERESULTS

Look at AverageRecordSize, MaximumRecordSize, MinimumRecordSize

Cheers!

* Noel

Stijn977 SSC Eights! Points: 962 More actions · Answer 3

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

okie_greg SSC Eights! Points: 911 More actions · Answer 4

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.

Marco-214819 Newbie Points: 7 More actions · Answer 5

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