Query to find the row size in a user table

Question

Query to find the row size in a user table

jishar

SSChasing Mays

Points: 639
More actions
November 20, 2008 at 3:02 am

#126056

Hi all,
I need to find out the size of selected rows in a table of Sql server 2005.
ANyone is having the solution , please help
Thanks in Advance
Jishar

Viewing 15 posts - 1 through 15 (of 22 total)

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

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 1

What are you trying to figure out? The number of bytes required to store the selected data? Is this an audit of some type? There are queries that you can write based on the column type and the length of values within variable column, but that may or may not help you out...

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

jishar (11/20/2008)
Hi all,
I need to find out the size of selected rows in a table of Sql server 2005.
ANyone is having the solution , please help
Thanks in Advance
Jishar

You can use LEN(column_name)

Alex S

jishar SSChasing Mays Points: 639 More actions · Answer 3

Hi,

Thanks for the reply..

i need to find the size utilized on OS by the data in a specific table.

Regards,

Jishar

jishar SSChasing Mays Points: 639 More actions · Answer 4

[font="Times New Roman"][font="Verdana"]Hi,

Thanks for the reply..

i need to find the size utilized on OS by the data in a specific table.

Regards,

Jishar [/font][/font]

sqlizer SSCrazy Points: 2120 More actions · Answer 5

jishar (11/22/2008)
Hi,
Thanks for the reply..
i need to find the size utilized on OS by the data in a specific table.
Regards,
Jishar

So are you looking for table size in the database? or database file size in the OS? Pls clarify.

Lowell SSC Guru Points: 323518 More actions · Answer 6

first you said row size, then you said table size...which is it?

there is a difference between the max space that a row can take, and the actual length of any specific row;

you can alos find the max actual size of a row, which would be less than what my query below gives you:

create table ##tmp (TableName varchar(40),DefinedRowSize int)

sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '

select * from ##tmp order by DefinedRowSize desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

This script will tell you the size of every table in a database.

Script to analyze table space usage

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 8

You don't need to write anything to get what you want. Just run the following and some of the things it returns are things like Min, Max, and Average row sizes...

[font="Courier New"]DBCC SHOWCONTIG WITH TABLERESULTS[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jishar SSChasing Mays Points: 639 More actions · Answer 9

[font="Verdana"]Hi,

Thank u for ur reply

I need to find out the Row size

Below is the Scenario,

1. I have one table A with 200000 Rows

2. Retrieveing 75000 rows from A table with certain filter crietaria

3. I want to find out the size of this 75000 Rows

Regards,

Jishar[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Why? What purpose are you trying to get that information for?

If you don't need it exact, query sys.dm_db_index_physical_stats for that table, use the detailed option, check the avg_record_size_in_bytes column and multiply that by 75000. That'll give you the aproximate size in bytes of those rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlizer SSCrazy Points: 2120 More actions · Answer 11

3. I want to find out the size of this 75000 Rows

You can dump that 75000 rows in another table and get size of that table, that would be the size of 75000 rows. Now if you want individual size of each row then that would be different story.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 12

I agree with Gail... you need to tell us "Why" you want/need to do this to make sure we give you the best solution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DBA-640728 SSChampion Points: 12898 More actions · Answer 13

hi, would that be the column Average Record Size? in

DBCC SHOWCONTIG WITH TABLERESULTS

I need to find the average size of a columm in my table to be able to get the average capacity needed if the table keeps growing and I don't find the column Average Row Size in the results.

Jayaram-694833 SSC Veteran Points: 202 More actions · Answer 14

Following is one way to identifiy the filtered rows size any any given table.

Following is a SSMS Template. Paste following to a SSMS query window and press Ctrl+Shift+M. Specify the database name and Table name. Click ok and in the resultant query specifiy the where clause if you want to limit the rows.

USE <DatabaseName, sysname, AdventureWorks>

declare @sql varchar(max), @TableName varchar(500), @DbName varchar(100)

Select @DbName='<DatabaseName, sysname, AdventureWorks>',@TableName = '<TableName, sysname, DatabaseLog>'

Select @sql=isnull(@sql+'+','Select @trsf'+@TableName+'=isnull(sum(')+'isnull(DATALENGTH('+Column_Name+'),0)' from Information_Schema.Columns where table_name = @TableName