| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-28 : 08:07:13
|
| Joseph writes "I need to add additional column to a table, when i tried using alter table command, I got the following error:The total row size (8724) for table 'location' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.what should I do to add the extra column?" |
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-28 : 08:44:38
|
| I'm afraid you cannot exceed 8060 bytes per row - this is due to how SQL Server stores data in "pages". Check if your datatypes are "bigger" than they need to be - ie. do you need a varchar(1000) when a varchar(500) would do?If you can't reduce the datatype sizes then you could create a second table and then a view joining the two tables together (don't forget your indexes!).============================Chairman of The NULL Appreciation Society"Keep NULLs as NULL" |
 |
|
|
Jay99
468 Posts |
Posted - 2002-03-28 : 08:45:35
|
SQL Server used 8k datapages. Rows are not permitted to span multiple pages. So you cannot have more than 8K on a row.quote:
what should I do to add the extra column?"
You should redesign your table. Want help? Post the DDL . . .Jay<O> |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-28 : 09:26:20
|
Is that an error message? It looks like a warning to me. You can create tables that have rows where the maximum possible size exceeds 8060 bytes if the minimum possible size is no greater than 8060 bytes:CREATE TABLE t ( col1 varchar(8000), col2 varchar(8000), col3 varchar(8000), col4 varchar(8000), col5 varchar(8000)) It's rarely a good thing to do, since it makes predicting whether an INSERT or UPDATE will succeed difficult. I have used it occasionally, where I have a very wide table being loaded from a non-relational source and there is no schema to ensure individual column widths. But here I have a reasonable guarantee of total width, and it's only used in the staging table before it gets restructured.Edited by - Arnold Fribble on 03/28/2002 09:27:36 |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-28 : 09:49:22
|
| Do you guys think of the 8060 thing as a feature or a bug? I know it relates to the fundamental structure of SQL Server and all that but, you know, this has always seemed a flaw in SQL Server to me. Yes databases should be normalised etc, but maybe one day I really will need that table with 10 varchar(8000) fields in it and I just can't have it with SQl Server (not reliably anyway) |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-03-28 : 10:58:34
|
quote:
Do you guys think of the 8060 thing as a feature or a bug? I know it relates to the fundamental structure of SQL Server and all that but, you know, this has always seemed a flaw in SQL Server to me. Yes databases should be normalised etc, but maybe one day I really will need that table with 10 varchar(8000) fields in it and I just can't have it with SQl Server (not reliably anyway)
I wouldn't call it a bug. I would call it a reasonable allocation of space for holding of individual rows that allows for efficient manipulation by the RDBMS. You lobby for a 16k page row, what about the guy that has reason for a 32k row, or 64k, etc...? A row of (possible) infinite size isn't really practical, where do we cut it off at? one phyiscal disk, the total raid array, multiple computers in a cluster? My car has 14 gallon gas tank, would I like a 30 gallon tank? Sure I would, less refills needed; but that probably isnt practical given the design of the vehicle. With larger rows you also start to see other problems. For example, as fewer and fewer rows can fit on a page the concurrency of your system decreases as they go from row level, to page, and finally to table locks. If you really need that much space why can't you just use a text field. That can hold upto 2gb of data.m2c,Justin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-28 : 11:36:13
|
quote:
Do you guys think of the 8060 thing as a feature or a bug?
How about SQL 6.5 which had 2K data pages???? A maximum varchar size of 255???? Plenty of software running for the last 5+ years with it...and still in use today! No offense Jack, but if you're complaining about having an 8K data page, you need to rethink your designs. There is ALWAYS a way to find a design that works for your needs. I personally LUXURIATE IN THE CAVERNOUS, ECHOING SPACES OFFERED by the 8K data page! I can put a garage in that size page! A Jacuzzi! I can't think of a design I've done in the last 3 years that had a row size greater than 1000, and at most I had 2 tables more than 500 bytes per row.Don't forget, 8000 characters equates to AT LEAST 1.5 single-spaced, typed pages! THAT'S A LOT OF INFORMATION! IMHO if you have multiple columns like that, it's not relational data.Edited by - robvolk on 03/28/2002 11:53:27 |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-03-28 : 12:09:39
|
quote:
Is it a feature or a bug?
What's the difference? |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-28 : 13:01:06
|
Whooah! Well I haven't got any tables with 10 cols of varchar(8000), I know why it would be against all laws of nature to have one and I don't plan to have any, it's just the principle of the thing and your opinions - well I guess I got them!  Re the difference between a feature and a bug - bugs you pay for and come preloaded with the software - features you only find out about after 20 hours of reading the manual.. |
|
|
|
|
Exceed total row size