maximum row size exceeds

hi,
I receive following Warning can any one tell me why this is occurs and how
can I eliminate it.


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


Farhan Iqbal

On Mon, 24 May 2004 02:52:29 -0700, Farhan Iqbal wrote:

 >hi,
 >I receive following Warning can any one tell me why this is occurs and how
 >can I eliminate it.
 >
 >
 >Warning: The table 'Part_Tags' has been created but its maximum row size
 >(14647) 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.
 >
 >
 >Farhan Iqbal
 >

Hi Farhan,

Your table definition includes at least one, probably more varchar,
nvarchar of varbinary columns. The theoretic maximum number of bytes in a
row, if all these columns are filled with the maximum length for that
column, is 14,647 bytes. The minimum length (is all varying length columns
are length zero) is below 8,060 bytes.

This is a warning message, there is no error -yet! But as soon as you try
to insert values into the table that make the total length of one row
exceed 8,060 bytes, you will get an error. SQL Server can't handle rows
that exceed 8,060 bytes of data.

Simple repro script to try it for yourself:

-- this will yield a warning similar to the one above,
-- but the table will be created.
create table testit(pk int not null primary key,
vc1 varchar(6000) not null,
vc2 varchar(6000) not null)
go
-- total length < 8,060 - no error
insert testit (pk, vc1, vc2)
select 1, replicate ('x', 3000), replicate ('y', 3000)
go
-- total length > 8,060 - error and insert rejeected
insert testit (pk, vc1, vc2)
select 2, replicate ('x', 6000), replicate ('y', 6000)
go
-- check that only first row was inserted
select * from testit
go
-- growing data beyond 8,060 bytes fails as well
update testit
set vc1 = replicate ('z', 6000)
where pk = 1
go
-- check that row was not updated
select * from testit
go
-- cleanup
drop table testit
go


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)