Is there a way to get the largest row size (in bytes) from a table?

Question

Post reply

Is there a way to get the largest row size (in bytes) from a table?

dave_wendt

Say Hey Kid

Points: 688
More actions
January 13, 2004 at 11:22 am

#177831

We have a table with hundreds of columns and I would like to see just how close we are getting to the sql-server row size limit of 8060 bytes.
I'm pretty sure that I could using something like such,
select max(
isnull(datalength(column1),0)
+ isnull(datalength(column2),0)
+ isnull(datalength(column3),0))
from tablename
but I really don't want to have to go through the pain of listing out all of the columns if there is some kind of built in function or easier way to do the same thing.
Anybody know?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Wouldn't something like this be more useful?

SELECT SUM(Length)

FROM syscolumns

WHERE OBJECT_NAME(Id) = 'Tablename'

--Jonathan

dave_wendt Say Hey Kid Points: 688 More actions · Answer 2

Wouldn't that be the length of the table definition, rather than the largest row of data inside the table?

BTW - when I run this I get the value 23448.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Yes; that's what I meant by "more useful."

http://www.sqlservercentral.com/columnists/awarren/worstpracticedefiningrowsthatexceedthemaxlength.asp

As for your original question, you could use the Object Browser in QA to script out the select statement, then use Edit|Replace to replace the commas (", ") with " + ISNULL(DATALENGTH(", and then replace the " +" with "),0) +". Then add the MAX() and clean up the beginning and end of the column list.

--Jonathan

gljjr SSChampion Points: 10080 More actions · Answer 4

Give this a try. Please note that I just whipped this up so it is untested!

-- Run this script and paste the results into a new QA Window. Then run the resulting script. Be prepared to wait!

DECLARE @cCol cursor

, @cTbl cursor

, @sql nvarchar(4000)

, @Tbl sysname

, @Col sysname

SET @cTbl = cursor for

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME

FOR READ ONLY

OPEN @cTbl

FETCH @cTbl INTO @Tbl

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'SELECT RecordLen = MAX('

SET @sql = ''

SET @cCol = CURSOR FOR

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tbl ORDER BY ORDINAL_POSITION

FOR READ ONLY

OPEN @cCol

FETCH @cCol INTO @Col

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = @sql + 'ISNULL(DATALENGTH([' + @Col + ']),0)' + ' + '

FETCH @cCol INTO @Col

END

PRINT LEFT(@SQL, LEN(@SQL) - 2) + '), TblName = ''' + @Tbl + ''''

PRINT 'FROM ' + @Tbl

PRINT 'UNION'

CLOSE @cCol

DEALLOCATE @cCol

FETCH @cTbl INTO @Tbl

END

CLOSE @cTbl

DEALLOCATE @cTbl

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

gljjr SSChampion Points: 10080 More actions · Answer 5

OOPS... I forgot... You'll also have to strip off the last Union in the above...

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

This one might also be interesting for you

http://www.sqlservercentral.com/columnists/sjones/pagesize.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

dave_wendt Say Hey Kid Points: 688 More actions · Answer 7

So even doing something like this:

select max(

isnull(datalength(column1),0)

+ isnull(datalength(column2),0)

+ isnull(datalength(column3),0))

from tablename

may be close but not totally accurate since it omits what is needed for internal storage. I would suppose varchar columns would also need to be added into the equation in order to get a completely accurate row size?

I guess this is why I was hoping that some system function existed for returning the maximum row size.

Thanks to all who posted.

gljjr SSChampion Points: 10080 More actions · Answer 8

If you look in BOL at the topic "Estimating the Size of a Table" it will outline how to actually calculate the rowsize and table size. It also talks about the "Null Bitmap" that is used to manage the nullability of the column. It would be nice if there was a tool that would give you this. However I haven't seen one yet that does it. Nor have I seen a good tool for estimating the DB size when designing the DB in the first place.

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Jonathan SSC-Insane Points: 20427 More actions · Answer 9

DBCC SHOWCONTIG('Tablename') WITH TABLERESULTS.

Look at the MaximumRecordSize value.

--Jonathan

dave_wendt Say Hey Kid Points: 688 More actions · Answer 10

Now that's what I was looking for. Thank you very much!