Row size clarification

  • I Create a table with the row size 8020 but still i get the message that the max row size is 8000 byte even though the max row size is 8060 bytes

    Create table Employee ( Name varchar (8020))

    Error Message

    The size (8020) given to the column 'Name' exceeds the maximum. The largest size allowed is 8000.

    Edited by - mouli on 10/22/2001 9:58:05 PM

  • Varchar is limited to 8000 chars. Try changing to that, then adding another col of varchar.


  • Well irrespective of the datatype (char or varchar) the message is the same that the row size cannot exceed 8000 bytes.Can some one check and answer this please.


  • Ran the following:

    create table testtablesize (col1 varchar(8000), col2 varchar(60))

    Generated this:

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

    Ran this:

    create table testtablesize2 (col3 varchar(8000))

    Generated this:

    The command(s) completed successfully.

    Running this:

    Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)


  • create table testtablesize (col1 varchar(8000), col2 varchar(35))


    The command(s) completed successfully.

    create table testtablesize (col1 varchar(8000), col2 varchar(36))


    The total row size (8061) for table 'testtablesize' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.

    The maximum after splitting into two columns

    is 8035 Bytes

    8060 - 8035 = 25 Bytes

    Is the 25 Bytes used for header information ?

  • Basically - according to Inside SQL Server 2000, each data row has some version info as well (if you have it, try page 252). The amount of space this info takes up depends on the type and number of columns, plus how many nullable columns you have.

    I try to avoid tables anywhere near this wide, really hurts your IO.


  • Yes, there is 25 bytes. If you change to char columns, you can get to 8039. Each var column has 2 bytes of overhead.

    There are 21 bytes of overhead for every row at a minimum, so the actual max size is 8039.

    I have an article almost complete on this. I spent the better part of a night and a day tracking this one down.

    Steve Jones

Viewing 7 posts - 1 through 6 (of 6 total)

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