Record size more than 8060B

[RamaKrishna Narla]

Hi,

In MS SQL Server, while creating the table, I am getting a warning
message saying like "maximum row size can exceed allowed maximum size
of 8060 bytes".

Is there any way in SQL Server, to increase this allowed maximum row
size?

The setting like "set ANSI_WARNINGS OFF" is not suitable in my case. I
need creation of table with around 11000 bytes in the record (Summation
of precision of all the columns). And more over, I do not want to loose
any data (truncation) in inert operation to keep on the whole record
size to 8060B.

Thanks a lot in advance.
Ramakrishna.

[Jens]

Hi,

SQL Server is limited to 8060 bytes of data stored in row. Depending on
your SQL Server version some data types can be stored in row or outta
row, but for SQL Server if you want to have more than the mentioned
limit you either have to do a 1:1 relation with pulling out some data
to another table, or use data types (But I wouldn´t prefer that as a
solution) that are not stored in row, than stored as pointers (like
text/ntext)

HTH, jens Suessmeyer.

---
http://www.sqlserver2005.de
---

[Helmut Woess]

Am 6 Jun 2006 10:46:41 -0700 schrieb RamaKrishna Narla:

In MS SQL Server, while creating the table, I am getting a warning
message saying like "maximum row size can exceed allowed maximum size
of 8060 bytes".

Is there any way in SQL Server, to increase this allowed maximum row
size?

The perfect solution would be changing to SQL Server 2005 (or SQLExpress).
There is a new datatype called VARCHAR(MAX) which can grow up till 2 Gb!
I would avoid TEXT for strings in any case. Read BOL to see which functions
do not work with TEXT, how to change TEXT (you need pointers!), ...

bye, Helmut

[Hugo Kornelis]

On 6 Jun 2006 10:46:41 -0700, RamaKrishna Narla wrote:

Hi,

In MS SQL Server, while creating the table, I am getting a warning
message saying like "maximum row size can exceed allowed maximum size
of 8060 bytes".

Is there any way in SQL Server, to increase this allowed maximum row
size?

Hi Ramakrishna,

Helmut's suggestion of upgrading to SQL Server 2005 is spot on. Not only
because of the new VARCHAR(max) datatype, but also because SQL Server
2005 will automatically store part of the data on overflow pages if your
row size exceeds the 8060 byte limit.

If you're stuck on SQL Server 2000, you'll have to work around the
limitation. For instance by creating a second table that holds some of
the data. E.g.

CREATE TABLE Reports
(ReportID int NOT NULL,
FirstBigColumn varchar(4000) NOT NULL,
SecondBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID)
)
CREATE TABLE ReportExtension s
(ReportID int NOT NULL,
ThirdBigColumn varchar(4000) NOT NULL,
FourthBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID),
FOREIGN KEY (ReportID) REFERENCES Reports(ReportI D)
)
--
Hugo Kornelis, SQL Server MVP