sql server new users: SQL2005 Max Row Size 8060 bytes -- sql server new users

You're in the

sql server new users

group:

SQL2005 Max Row Size 8060 bytes

Re: SQL2005 Max Row Size 8060 bytes

Alan Brewer [MSFT]
3/21/2007 9:57:44 AM

sql server new users: SQL Server has an 8K page size, so the largest a single row can be is 8060
bytes (allows space for a page header). SQL Server will let you create a
table with variable columns that, if all were filled, would exceed the 8060
limit, but gives you a warning at create time. If you later try to insert or
update a row such that it has more than 8060 bytes of data, that insert or
update will fail.

Have a look at the varchar(max) and varbinary(max) data types introduced in
SQL Server 2005. varchar(max) values can go to 2GB and are stored on as many
pages are required to hold the data. However, this means that varchar(max)
values can be stored on seperate physical pages from the page holding the
rest of the data for the row. The issue that you will need to factor into
your design is going from a case where the data for each row is on one page,
to having the data for each varchar(max) column potentially being on
seperate pages. That would force the DB Engine to do potentially 13 read or
write operations instead of a single read or write, which would impact
performance and scalability.

--
Alan Brewer [MSFT]
SQL Server Documentation Team

Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

This posting is provided "AS IS" with no warranties, and confers no rights.

SQL2005 Max Row Size 8060 bytes

stevesj
3/21/2007 10:44:12 AM

I have need to create a table with 12 varchar(2000) fields. SQL does not
prevent me from doing this but I'm wondering about the row size. The help
documentation indicates that the max size is 8060 bytes.

What will happen if I try updating this table and all 12 fields have data
that exceeds 8060 bytes ? Not sure if I'm understanding what 'row size'
actually is.

Thanks for any direction

Steve

Re: SQL2005 Max Row Size 8060 bytes

Raymond D'Anjou
3/21/2007 3:10:10 PM

In addition to what the others have said, another solution may be to move
these columns to another table.
12 columns... 12 months in a year... sounds like a redesign may be in order.

Give us more details about what you're trying to do.

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Steve Dassin
3/21/2007 4:05:04 PM

I think it's time for todays dose of common sense.

Gertrude Stein may just as well have been talking about databases when she
said "a rose is a rose is a rose".
A row is a row is a row.

An application developer knows what a row is. It is, after all, one of the
salient parts of the exercise. A row is his problem. How that row is stored,
whether its stored horizontally, vertically, on earth or mars is NOT his
problem, it's MSs problem. As in players play and coaches coach. I don't
want to give a wit about page splits or pointers. And I certainly am not the
least bit interested in how my 'row', my logical row, impacts performance.
Not my problem. If I'm the pilot flying the plane am I supposed to be the
navigator and stewardness too. I think not. MS has been given a 10 year
raincheck for getting row size straightened out and they apparently haven't
cashed it in yet. Not my problem. If MS is going to cost me getting my
checked signed, I'm going to sign off on MS. I design my apps from the
outside and stay outside. The tail does not wag my dog.
The best way MS can support application developers is to do their job and do
it right.

An ounce of preventation is worth more than tons of cure.
It's just sense, although I'm not so sure it's so common.

best,
steve

Re: SQL2005 Max Row Size 8060 bytes

Steve Dassin
3/21/2007 4:20:45 PM

[quoted text, click to view]

It's a good point if the motivation is a better design.
It's a ridiculous point if the motivation is the design of the database
system.

Re: SQL2005 Max Row Size 8060 bytes

Hugo Kornelis
3/21/2007 7:33:34 PM

[quoted text, click to view]

Hi Steve,

Alan forgot to include in his answer that SQL Server 2005 has a new
feature that didn't yet exist in SQL Server 2000: if several varying
length columns, each with a length <= 8000 bytes, make the total row
size exceed 806 bytes, then some of this varying length data will
automatically be pushed outside the main page, in an overflow page. The
main page will in this case hold only a pointer to the main data.

This will of course impact the speed of data retrieval where that
specific row and column is read, since the overflow page has to be read
in addition to the regular data page.

--
Hugo Kornelis, SQL Server MVP

Re: SQL2005 Max Row Size 8060 bytes

Hugo Kornelis
3/21/2007 9:04:22 PM

[quoted text, click to view]

Hi Raymond,

Good point!!

--
Hugo Kornelis, SQL Server MVP

Re: SQL2005 Max Row Size 8060 bytes

Anthony Thomas
3/22/2007 12:00:00 AM

You are correct, to a point.

As the application developer, yes, your primary concern is at the logical
level.

That's where the Database Engineer comes in. They are responsible for
translating your logical design into a physical product. That translation
is dependent on the chosen platform.

You can rant if you want, but Oracle and DB2 have the same exact problem (as
well as every other DBMS). The only difference is some of those other
platforms give you more choices on row length, but none of them give you
infinite, variable, and undetermined sizes. The Database Engineer still has
to choose one of them, and then stick to it.

Unfortunately, in today's solutions, the designer often has to wear multiple
hats. As the application designer, you will still be only responsible for
the logical definition, however, if you also put on the Database Engineer
hat, you also must assume the responsibilities that go with that mantel.

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Anthony Thomas
3/22/2007 12:00:00 AM

This is part of the new 2005 physical database architecture (check it out in
Books Online) called Allocation Units. One of these is called the Overflow
Area, to which Hugo is referring.

So you know, there are 3 AUs: data pages, overflow area, and the LOB
references (which is were the new VARCHAR(MAX) and NVARCHAR(MAX) values are
distributed along with the older TEXT, NTEXT, and IMAGE types.

There is a question I have regarding how the XML type is handled in this new
architecture. I am assuming that XML is treated similarly to the other LOB
types.

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Anthony Thomas
3/22/2007 12:00:00 AM

This is one of the cases I have found to explain the use of 1 to 1
relationships. The other being sparse data (all those columns that allow
NULLs; however, SS2K5 has also introduced new mechanisms, although at the
file level, to also handle sparse data more efficiently).

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Anthony Thomas
3/22/2007 12:00:00 AM

You know, if you had a "good" point, I'm sure more people would listen.

At this point, you're just flapping your gums.

How would you change the product to handle this situation?

You might actually try contributing instead of ranting. Offer some
alternatives.

--

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Raymond D'Anjou
3/22/2007 12:00:00 AM

I was thinking more along the lines of a 1 to 12 relationship.

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Kevin G. Boles
3/22/2007 10:11:45 AM

Alan Brewer covered one possible solution - varchar(max) if you are on SQL
2005.

Other options are:
1) row overflows in SQL 2005. You can go ahead and design 12 2000 byte
fields in the same table. If the data for each row fits, great. If not,
SQL Server automatically creates a row-overflow page to handle it. NOTE:
this is a performance issue if lots of rows overflow due to extra
(non-contiguous) reads.

2) Design 4 tables, each with a common int (or big_int) PK, each containing
4 2000 byte fields. Drop a view on top of them for simple data reads and
you are done.

--
TheSQLGuru
President
Indicium Resources, Inc.

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

stevesj
3/22/2007 11:16:16 AM

Thanks all,

I have a clearer picture now of row size and the possible performance hit of
exceeding this. I decided to redesign and now perhaps better normalized
with depth over/against width?

Am designing an online form which has 5 sections. Each section 10~12
questions and hence the varchar(2000) for comments/answers.

The redesign now has 5 tables, 1 for each section. When someone completes
the form each of the 5 tables will get 10~12 added records.

Thanks much for the guidance - and still open to any suggestions.
Steve

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Hugo Kornelis
3/22/2007 9:27:46 PM

[quoted text, click to view]

Hi Anthony,

I didn't know this, so I set up a test database and used the DBCC PAGE
command to check how data in an XML column was allocated.

I found that this data is stored in row if it's less than 8000 bytes,
and pushed off row if it exceeds that amount.

--
Hugo Kornelis, SQL Server MVP

Re: SQL2005 Max Row Size 8060 bytes

Anthony Thomas
3/22/2007 10:09:40 PM

Which makes sense since now you can also do schema binding and XML indexing
that it would belong to data and overflow pages instead of the LOB types.

Thanks for running the quick test and confirming.

Sincerely,

Anthony Thomas

--

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Andrew J. Kelly
3/23/2007 12:00:00 AM

I agree 100% with Anthony on this. That's the biggest problem with database
designs these days in that too many of them are done by people who are
mostly application developers and not database centric. While I am not
blaming the developer it definitely is a consideration on how well a system
performs and scales. This is not just a SQL Server issue, it happens across
the board. But in my experience most shops when using Oracle or DB2 will
have someone that is more dedicated to the database in addition to the app
developer than a SQL Server shop.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Jay
3/29/2007 12:00:00 AM

Am I right in saying that if the maximum data size that I put in a row is much less than 8k (let's
say 100 bytes), the row will still take up 8k, so 100,000 rows will take 800M? If so, it sounds very
inefficient on storage space.

[quoted text, click to view]
SQL Server has an 8K page size, so the largest a single row can be is 8060
bytes (allows space for a page header). SQL Server will let you create a
table with variable columns that, if all were filled, would exceed the 8060
limit, but gives you a warning at create time. If you later try to insert or
update a row such that it has more than 8060 bytes of data, that insert or
update will fail.

Have a look at the varchar(max) and varbinary(max) data types introduced in
SQL Server 2005. varchar(max) values can go to 2GB and are stored on as many
pages are required to hold the data. However, this means that varchar(max)
values can be stored on seperate physical pages from the page holding the
rest of the data for the row. The issue that you will need to factor into
your design is going from a case where the data for each row is on one page,
to having the data for each varchar(max) column potentially being on
seperate pages. That would force the DB Engine to do potentially 13 read or
write operations instead of a single read or write, which would impact
performance and scalability.

--
Alan Brewer [MSFT]
SQL Server Documentation Team

Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: SQL2005 Max Row Size 8060 bytes

Andrew J. Kelly
3/29/2007 12:00:00 AM

No that is not true at all. How much space it takes up is dependent on what
datatype you use and how much data you put in it. For instance a
varchar(100) with only 10 characters in it will only take up 10 bytes not
100.So if you only have 100 bytes for a row you take up 100bytes plus a
little overhead to manage the row and the page.You could fit approx 800 rows
on a page. BooksOnLine goes into details on this and there are plenty of
resources on the web and in Books such as Inside SQL Server 2000 or 2005
that will explain all this.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Re: SQL2005 Max Row Size 8060 bytes

Jay
3/29/2007 6:39:33 PM

Thanks very much for your reply Andrew, that's very helpful.

[quoted text, click to view]
No that is not true at all. How much space it takes up is dependent on what
datatype you use and how much data you put in it. For instance a
varchar(100) with only 10 characters in it will only take up 10 bytes not
100.So if you only have 100 bytes for a row you take up 100bytes plus a
little overhead to manage the row and the page.You could fit approx 800 rows
on a page. BooksOnLine goes into details on this and there are plenty of
resources on the web and in Books such as Inside SQL Server 2000 or 2005
that will explain all this.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn