none
Error:max row size can not be more than 8060 characters in Sql-server2000

    Question

  • Hello

    I am getting following error during executing a insert query.

    Error Message : Cannot create a worktable row larger than allowable maximum.

    Resubmit your query with the ROBUST PLAN hint.

    1. The max rowsize for a table in SQL Server 2000 is 8060 characters.

    2. The query  was working fine for data below the limit, it failed

       for the data above the limit.

    3. I had tested the UPDATE query with option (ROBUST PLAN ), but it didn’t work.

    if any one have clue to solve this problem plzz reply soon.

    -thanks in advance

    --dharmendra singh

     

    Monday, June 05, 2006 10:32 AM

Answers

  • BLOB field values usually stored out of the record (until otherwise is specified), it's only 16 byte pointer that stored in the record.
    Tuesday, June 06, 2006 7:04 AM

All replies

  • A row in SQL server can not exceed a page = 8K = 8060 bytes + header info etc

    you can create a table that could hold 16000 bytes

    create table abc (Col1 varchar(8000), col2 varchar(8000))

     

    and you will get this warning

    Warning: The table 'abc' has been created but its maximum row size (16025) 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.

    -- no problem

    insert abc values (replicate('a',4000), replicate('a',4000))

    -- no problem

    insert abc values (replicate('a',8000), null)

    -- here we get the error

    insert abc values (replicate('a',4030), replicate('a',4031))

    Server: Msg 511, Level 16, State 1, Line 1
    Cannot create a row of size 8074 which is greater than the allowable maximum of 8060.
    The statement has been terminated.

     

    --let's start from scratch
    drop table abc

    -- use text columns

    create table abc (Col1 text, col2 text)
    insert abc values (replicate('a',40030), replicate('a',40031))

     

    when using text columns you can insert the data without a problem however you can not use certain functions, order by etc etc

    In SQL 2005 you can use VARCHAR(MAX) and you can eliminate some of the problems of working with text data ( TEXTPTR,READTEXT, WRITETEXT, and UPDATETEXT )

    Denis the SQL Menace
    http://sqlservercode.blogspot.com/

    • Proposed as answer by Douglas Turque Wednesday, October 17, 2012 6:45 PM
    Monday, June 05, 2006 5:56 PM
  • Thanks for quick reply but i want to also know is this be possible with some

    column nvarchar and some text column in a table i.e.

    create table abc (Col1 text, col2 nvarchar(4000))
    insert abc values (replicate('a',40031), replicate('a',4000))

     

    Tuesday, June 06, 2006 4:07 AM
  • BLOB field values usually stored out of the record (until otherwise is specified), it's only 16 byte pointer that stored in the record.
    Tuesday, June 06, 2006 7:04 AM