how to calculate a row size
-
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.
-
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)
-
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] -
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
-
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.
-
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:
-
8 bits = 1 byte (i.e. 10101010 = 8 bits or 1 byte)
16 bits = 2 bytes, 24 bits = 3 bytes, etc.
-
Oh! They are all in there, BOL. I was lost before and now am found. Thanks.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply