October 22, 2001 at 6:16 am
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
October 22, 2001 at 6:45 am
Varchar is limited to 8000 chars. Try changing to that, then adding another col of varchar.
Andy
October 22, 2001 at 9:53 pm
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.
Mouli
October 23, 2001 at 4:35 am
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)
Andy
October 23, 2001 at 10:09 am
create table testtablesize (col1 varchar(8000), col2 varchar(35))
Message:
The command(s) completed successfully.
create table testtablesize (col1 varchar(8000), col2 varchar(36))
Message:
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 ?
October 23, 2001 at 11:33 am
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.
Andy
October 24, 2001 at 3:00 pm
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