Warning Message... row size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Warning Message... row size

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2003-08-07 : 15:37:39

After an alter statement on my table, here is the message I'm receiving

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

Could it be because I have two varchar(8000) in this table? Could I have trouble if my fields field one is a string of 5000 char and the second a string of 7000 char?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 15:45:36

quote:

exceeds the maximum number of bytes per row (8060

Pretty self explanatory if you ask me....

No row is SQL Server can be greater that 8k

Brett

8-)

SELECT POST=NewId()

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 15:47:51

Yep - the insetr will fail if it would create a row greater than 80860 bytes in length.
It will be ok if the row size is always less than that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

1fred
Posting Yak Master

158 Posts

Posted - 2003-08-07 : 16:00:42

Is there any solution? I really need in the same row two fields that could be up to varchar(8000).

Thanks

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 16:02:42

No. 8060 bytes to a data page, that's all there is.

Split your table into 2 or use a TEXT column.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 16:29:17

Why settle for 2 when you can have n

It's a relational thing



USE Northwind
GO

CREATE TABLE myParent (col1 int IDENTITY, col2 varchar(10))
GO
CREATE TABLE myChild (Cola int IDENTITY(1,1), col1 int, col2 varchar(8000))
GO

DECLARE @x int, @y int
INSERT INTO myParent(col2) SELECT 'stuff'
SELECT @x = MAX(col1) FROM myParent
SELECT @y = 1
WHILE @y < 10
	BEGIN
		INSERT INTO myChild (col1, col2) SELECT @x, REPLICATE('X',8000)
		SELECT @y = @y + 1
	END
SELECT * FROM myChild
GO
DROP TABLE myParent
GO
DROP TABLE myChild
GO

Brett

8-)

SELECT POST=NewId()

1fred
Posting Yak Master

158 Posts

Posted - 2003-08-07 : 16:40:01

Thanks X002548, this was the next solution, but I was just curious why the row size limit is 8060 and if there was something to do about it.

Thanks all!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 16:42:19

quote:
Originally posted by 1fred

Thanks X002548, this was the next solution, but I was just curious why the row size limit is 8060 and if there was something to do about it.

Thanks all!

Because that's the limit that a page can hold.

Tara

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-07 : 16:50:55

Why would that have been your next solution? You specifically said you had 2 columns. If you truly don't know how long your text will be, use a TEXT column.

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 16:53:04

quote:
Originally posted by tduggan
that's the limit that a page can hold.

Tara

What color pages do you use Tara?

Historically, was sql server at 2k page? Also, why do they limit themselve to 8?

Why not 32?

gotta be a performance thing...

Brett

8-)

SELECT POST=NewId()

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-07 : 16:55:31

1Fred,

Oh, and the identities are for example only..please use natural keys, and maybe an ADD_Date column for the child...maybe add even a message type column...

PLeeeeeeeeeeeeeeeaaaaaaaaaaasssssssssssssssssseeeeeeeeeeeeeeeeeee

Brett

8-)

SELECT POST=NewId()

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 16:55:50

2k was for SQL Server 6.5. 8k is for 7.0 and 2000. Maybe Yukon will be 32k. Who knows? Well maybe the MVPs know...

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-07 : 20:13:02

Nope, they're sticking with an 8K page size (AFAIK). SQL Server allocates space in extents, which consist of 8 pages (64K total). Since NTFS works very well with 64K cluster sizes, it fits neatly in place for fast disk access without forcing the file system to do any particular gymnastics.

If you look in Books Online or Inside SQL Server under "pages", "extents" or "space allocation" you'll find excellent examples of how exactly SQL Server uses pages in all their various forms (data, text, index, IAM, GAM, etc.) It's almost self-explanatory as to how and why an 8K page - 8 page extent came to be chosen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources