How can I get (Programatically) the size of a row in a table in SQL stored
procedure?

Evan

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_92k3.asp


Show quote Hide quote

I need a function or whatever to calculate the size since the procedure will
be used on different tables!

Evan


Show quoteHide quote

I found a thread where someone posted a stored procedure for calculating row
size. It accepts @TableName as a parameter and returns @Row_Size as output.
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/3d4e2272f814540d/7f33627d8c355841


Show quoteHide quote

So do you want max possible, max actual, avg possible, avg actual, actual of
a random row, ...?

Sorry, avg possible doesn't really make sense, I guess...

There are some developers who would spend a week coding a request for
"average possible" before go back and asking the obvious question.

Show quoteHide quote

There are a few in this case.  Which one do you mean?  :-)

I want to create a stored proc which returns not more than X kilobytes of
records.

Evan


Show quoteHide quote

This would be a lot more simple if the row only contained non-NULL columns
of fixed width data types (integers, char, datetime, etc.). If it contains
any columns of type VarChar or columns of NULL value, then the width of each
row will vary significantly.

Let's assume that you determine the average row with to be 385 bytes and you
want to return only 200KB of data.

200,000 / 385 = ~519

Therefore, your stored procedure would need to use the SELECT TOP ... clause
to return the appropriate number of rows. For example:

select top 519 from mytable

At least in SQL Server 2000, SELECT TOP cannot reference a variable, so you
would need to either hard code the number 519 or, if the stored procedure
allows specifying the number of KB as a parameter, you will need to first
build your select statement in a VarChar string and then execute it using
the EXEC command. The following article describes techniques for using
dynamic SQL in such a way:
http://www.sqlteam.com/item.asp?ItemID=4599

Keep this in mind: If your goal here is to reduce the amount network
bandwidth or memory consumption of the dataset, then keep in mind that even
though SQL Server may store the data internally using 200 KB, the data's
size will be different (probably much more) once converted into XML or an
ADO recordset by the middle tier or client application.

Show quoteHide quote

Getting an unused number from a set of numbers?
Design: multiple columns for primary key
TSQL Debugger Oddity
Whats Wrong with the Query?
Newbie:UNION with SELECT TOP