Post reply

Calculate max number of bytes for a row

  • 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.

  • October 7, 2003 at 12:43 am

    #477300

    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]

  • October 7, 2003 at 8:19 am

    #477386

    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-

  • October 7, 2003 at 8:41 am

    #477398 

    

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

    FROM Information_Schema.Columns

    WHERE Table_Name = @TableName

    --Jonathan



    --Jonathan

  • October 7, 2003 at 9:16 am

    #477410

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

    Guarddata-

  • October 7, 2003 at 9:28 am

    #477413

    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



    --Jonathan

  • October 7, 2003 at 9:40 am

    #477417

    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...

  • October 7, 2003 at 9:55 am

    #477421

    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?

  • October 7, 2003 at 10:08 am

    #477423

    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



    --Jonathan

  • October 7, 2003 at 10:41 am

    #477429

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

    Guarddata-

  • October 7, 2003 at 2:25 pm

    #477458

    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!

  • October 7, 2003 at 2:49 pm

    #477462

    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-

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

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