Has anyone ever seen this error before?

A google search turned up nothing when I searched for this.
Is it saying that if my total VARCHARs for all columns in a row add up
to more than 8060, then it can't do it?
I've never heard of such a limitation before!
ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
Warning: The table 'dat_table' has been created but its maximum row
size (17500) 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.
Does any guru have knowledge of this?
Thanks,
Stewart>A google search turned up nothing when I searched for this.
You should have gotten many hits. Perhaps you inadvertently included the
table name or maximum row size in the search. This is also well documented
in the SQL Server Books Online.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
Not exactly. The ALTER TABLE succeeds but the warning message indicates
that the total row size has the *potential* to exceed the 8060 max. If you
later try to insert a row that requires more than 8060 bytes of actual data,
the insert will fail. Other inserts will succeed.
BTW, SQL 2005 provides some relief. When the combined widths exceed the
8060, overflow data are stored separately. However, there more I/O is
required since more pages need to be accessed.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154604543.119450.193230@.75g2000cwc.googlegroups.com...
>A google search turned up nothing when I searched for this.
> Is it saying that if my total VARCHARs for all columns in a row add up
> to more than 8060, then it can't do it?
> I've never heard of such a limitation before!
> ALTER TABLE dat_table ADD some_stuff VARCHAR(4000) DEFAULT NULL NULL;
> Warning: The table 'dat_table' has been created but its maximum row
> size (17500) 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.
> Does any guru have knowledge of this?
> Thanks,
> Stewart
>|||Thank you Dan.
Useful to know.
Dan Guzman wrote:
> >A google search turned up nothing when I searched for this.
> You should have gotten many hits. Perhaps you inadvertently included the
> table name or maximum row size in the search. This is also well documented
> in the SQL Server Books Online.
> > Is it saying that if my total VARCHARs for all columns in a row add up
> > to more than 8060, then it can't do it?
> Not exactly. The ALTER TABLE succeeds but the warning message indicates
> that the total row size has the *potential* to exceed the 8060 max. If you
> later try to insert a row that requires more than 8060 bytes of actual data,
> the insert will fail. Other inserts will succeed.
> BTW, SQL 2005 provides some relief. When the combined widths exceed the
> 8060, overflow data are stored separately. However, there more I/O is
> required since more pages need to be accessed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||From: S Karthikeyan
Hi,
SQL Server can only have 8060 bytes in a row. Incase you want to exceed
this
limit try using image, text etc as the data types.
This is because a SQL data page is 8k in size i.e. 8192 bytes...
Approximately 132 bytes are used for storing information about the rows
like
headers, offset etc.
But if you try to insert/update with more than 8060 bytes in a row, SQL
will
automatically truncate the row to fit within 8060 bytes.
Thank you.
Regards,
Karthik|||> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
No, this is not the case. The insert will fail if you try to insert a row
with more than 8060 bytes (including overhead):
CREATE TABLE dbo.BigRowTable
(
Col1 varchar(8000),
Col2 varchar(8000)
)
--no problem
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 47))
--fails
INSERT INTO dbo.BigRowTable (col1, col2)
VALUES(REPLICATE('x', 8000), REPLICATE('x', 48))
SELECT COUNT(*) FROM dbo.BigRowTable
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Stewart" <stewart.cambridge@.gmail.com> wrote in message
news:1154686180.641004.144020@.b28g2000cwb.googlegroups.com...
> From: S Karthikeyan
> Hi,
> SQL Server can only have 8060 bytes in a row. Incase you want to exceed
> this
> limit try using image, text etc as the data types.
> This is because a SQL data page is 8k in size i.e. 8192 bytes...
> Approximately 132 bytes are used for storing information about the rows
> like
> headers, offset etc.
> But if you try to insert/update with more than 8060 bytes in a row, SQL
> will
> automatically truncate the row to fit within 8060 bytes.
> Thank you.
> Regards,
> Karthik
>