TOTAL SIZE OF A ROW IN A TABLE

Question

TOTAL SIZE OF A ROW IN A TABLE

avinashily

Say Hey Kid

Points: 661
More actions
February 18, 2011 at 2:34 am

#144991

Hi all,
i have a table with the following data types defined for the columns,
a nvarchar(50) not null,
nume numeric(18,0) not null,
time1 datetime null,
variab nvarchar(max) null,
abc text null,
numcha nchar(20) null,
can you guys tell me what will be the length of the row if the row utilizes full column size and what if it is less than the column size?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

avinashily (2/18/2011)
Hi all,
i have a table with the following data types defined for the columns,
a nvarchar(50) not null,
nume numeric(18,0) not null,
time1 datetime null,
variab nvarchar(max) null,
abc text null,
numcha nchar(20) null,
can you guys tell me what will be the length of the row if the row utilizes full column size and what if it is less than the column size?

It's basically a question of looking up the data types in Books Online, getting the size from there and adding everything up. Is this a homework or interview question?

NVARCHAR(50) = up to 102 bytes (you'll have to check BOL for why there are 2 more there)

NUMERIC(18) = 9 bytes

DATETIME = 8 bytes

NVARCHAR(MAX) = who knows (up to 2 gigs of storage, but less stored in the table, again, hit BOL)

TEXT = similar to NVARCHAR(MAX) (but you really shouldn't be using this. It's due to be deprecated with some future release of SQL Server, use NVARCHAR or VARCHAR with MAX).

NCHAR(20) = 42 bytes

That shold be enough to get you started.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You might also want to read this blog posting:

Kimberly Tripp’s blog posting

http://sqlskills.com/BLOGS/KIMBERLY/category/SQL-Server-2005.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

magasvs SSCertifiable Points: 7660 More actions · Answer 3

If this will be useful - keep in mind that when you use NVARCHAR or NCHAR data types in will take almost twice more space than VARCHAR and CHAR.