Calculate max number of bytes for a row

Question

Calculate max number of bytes for a row

larryd

SSCommitted

Points: 1669
More actions
October 6, 2003 at 3:55 pm

#83122

I recently got the following:
Warning: The table 'table1' has been created but its maximum row size (8350) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [SQLSTATE 01000] (Message 1708). The step succeeded.
We often add columns to existing tables, so I want to write a query that calculates the "maximum number of bytes" for any table, so that we don't go over. We have tables with many colunms so to do this by hand would be tedious. For the table I got this error on, I have not been able to replicate the number SQL Server gave. I've looked at syscolunms, information_schema.columns, etc. Any help is appreciated.

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

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

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

Hi ldaggett,

quote:
We often add columns to existing tables, so I want to write a query that calculates the "maximum number of bytes" for any table, so that we don't go over. We have tables with many colunms so to do this by hand would be tedious. For the table I got this error on, I have not been able to replicate the number SQL Server gave. I've looked at syscolunms, information_schema.columns, etc.

if you can get hands on 'Inside SQL Server 2000' you'll find an example how to do this, if I remember right.

In the meantime BOL has an example how to estimate table size.

Look out for 'calculating table size'.

Frank

http://www.insidesql.de

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

guarddata SSCertifiable Points: 6583 More actions · Answer 2

SELECT SUM( SC.length )

FROM sysobjects SO (NOLOCK)

INNER JOIN syscolumns SC (NOLOCK) ON SC.id = SO.id

WHERE SO.Type = 'U' and SO.name = <Your Table Name>

Guarddata-

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



SELECT Table_Name, SUM(COL_LENGTH(Table_Name, Column_Name))

FROM Information_Schema.Columns

WHERE Table_Name = @TableName

--Jonathan

guarddata SSCertifiable Points: 6583 More actions · Answer 4

Thanks Jonathan - I sometimes forget about the Information_Schema information ...but don't forget to GROUP BY Table_Name

Guarddata-

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

quote:
Thanks Jonathan - I sometimes forget about the Information_Schema information ...but don't forget to GROUP BY Table_Name
Guarddata-

Yeah, I obviously used GROUP BY with that query when I wrote it, but then noticed yours was for a specific table. No GROUP BY is necessary if you remove the table name from the SELECT list:



SELECT SUM(COL_LENGTH(Table_Name, Column_Name))

FROM Information_Schema.Columns

WHERE Table_Name = @TableName

--Jonathan

larryd SSCommitted Points: 1669 More actions · Answer 6

Thanks, guarddata and Johnathan. I ran both of your scripts against my table, and they both returned 7903 for the row size. But, SQL Server in the error message returned 8350. I wish I could find the code behind this error message, I could reconcile the different numbers...

larryd SSCommitted Points: 1669 More actions · Answer 7

OK, I just found a 'trick' that may be well-known: In EM, go into Design Table, position the cursor in the grid, right-click and choose Select All. You can then easily copy and paste all into a spreadsheet. This made it easy for me to sum the Length column for my 280 column names. The results agree with your scripts, so still I wonder how the value in the error message is calculated! Anyone from MS out there?

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

quote:
Thanks, guarddata and Johnathan. I ran both of your scripts against my table, and they both returned 7903 for the row size. But, SQL Server in the error message returned 8350. I wish I could find the code behind this error message, I could reconcile the different numbers...

Are you getting this error from Enterprise Manager (EM) or from using ALTER TABLE? IIRC, EM does things like create new tables and copy rather than using ALTER TABLE, so perhaps a temporary table used by EM is reaching the size reported.

--Jonathan

guarddata SSCertifiable Points: 6583 More actions · Answer 9

What was the size of the column you were attempting to add?

Guarddata-

larryd SSCommitted Points: 1669 More actions · Answer 10

Actually, the error occurred when I was using a DTS package to copy the table to another database. Others here have added columns to the table, and if it raised an error I did not hear about it.

Thanks!

guarddata SSCertifiable Points: 6583 More actions · Answer 11

One more then I will leave this... If the DTS error stops the process, you would probably be able to get away with a bps process. Since the data is already there, it will be OK (I suppose )

Guarddata-