Home   |   Register   |   Today's Posts   |   Rules   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > SQL server : maximum row size exceeds the allowed maximum of 8060 bytes.

Reply
Page 1 of 2 1 2
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 04-20-12, 05:43
srinivasveera srinivasveera is offline
Registered User
  
Join Date: Apr 2012
Posts: 5
SQL server : maximum row size exceeds the allowed maximum of 8060 bytes.
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -

Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
-- INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.


how can i resolve this, please suggest

Thanks
Veera
Reply With Quote
  #2 (permalink)  
Old 04-20-12, 05:53
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
350 columns?

this immediately makes me suspect that you might perhaps need to do some normalization
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 04-20-12, 07:03
srinivasveera srinivasveera is offline
Registered User
  
Join Date: Apr 2012
Posts: 5
yes, we applied normaliztion. still as per business req. we need these many columns
Reply With Quote
  #4 (permalink)  
Old 04-20-12, 08:13
Thrasymachus Thrasymachus is offline
Annie's Dog Walker
  
Join Date: Nov 2004
Location: at the bottom of the bottle
Posts: 8,131
Doubtful. This is the way it works. All of the data in the database is written to "pages". Pages are 8060 bytes in size. When you read from a database, a single disk read operation is performed for each page. This is just a warning. It is not an error or a prohibtion, but exceeding the 8060 byte limit will impact the performance of your system, and as Rudy suggests, I would revisit your design, because due to the way indexing works, any system that contains tables with that many columns of any considerable size will eventually choke and die of performance issues. Either you will not be able to properly index the the right combination of columns in the right commbination of indexes to make read operations tolerably fast enough or you will over index to the point that loading data into the table will be severely impacted. I highly suggest that you hire a database architect or at least go to google and search for "sql normalization" and perhaps read some books on database design. Or if you think your table is normailzed with 350 attributes, post the Data Definition Language for it, and we will see.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Last edited by Thrasymachus; 04-20-12 at 09:42.
Reply With Quote
  #5 (permalink)  
Old 04-20-12, 09:46
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 12,327
Quote:
Originally Posted by srinivasveera View Post
yes, we applied normaliztion. still as per business req. we need these many columns
You think you did, but you didn't.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #6 (permalink)  
Old 04-20-12, 12:00
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by Thrasymachus View Post
Or if you think your table is normailzed with 350 attributes, post the Data Definition Language for it, and we will see.
i agree with this

if you think it is normalized, post the DDL and we will confirm
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 04-23-12, 09:08
srinivasveera srinivasveera is offline
Registered User
  
Join Date: Apr 2012
Posts: 5
huge thanks for ur replies frndz, and here im giving my table structure, plz check and guide me....


USE [XXX]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[AAA](
[A001] [bigint] IDENTITY(1,1) NOT NULL,
[A002] [varchar](20) NULL, [A003] [varchar](100) NULL,
[A004] [varchar](100) NULL, [A005] [varchar](50) NULL,
[A006] [varchar](100) NULL, [A007] [varchar](100) NULL,
[A008] [varchar](100) NULL, [A009] [varchar](100) NULL,
[A010] [varchar](200) NULL, [A011] [varchar](200) NULL,
[A012] [varchar](50) NULL, [A013] [varchar](100) NULL,
[A014] [datetime] NULL, [A015] [datetime] NULL,
[A016] [varchar](100) NULL, [A017] [varchar](100) NULL,
[A018] [varchar](100) NULL, [A019] [varchar](30) NULL,
[A020] [varchar](100) NULL, [A021] [varchar](100) NULL,
[A022] [varchar](100) NULL, [A023] [varchar](100) NULL,
[A024] [varchar](100) NULL, [A025] [varchar](100) NULL,
[A026] [varchar](100) NULL, [A027] [varchar](100) NULL,
[A028] [varchar](100) NULL, [A029] [varchar](100) NULL,
[A030] [varchar](100) NULL, [A031] [varchar](100) NULL,
[A032] [varchar](100) NULL, [A033] [varchar](100) NULL,
[A034] [varchar](100) NULL, [A035] [varchar](100) NULL,
[A036] [varchar](100) NULL, [A037] [varchar](100) NULL,
[A038] [varchar](100) NULL, [A039] [varchar](100) NULL,
[A040] [varchar](100) NULL, [A041] [varchar](100) NULL,
[A042] [varchar](100) NULL, [A043] [varchar](100) NULL,
[A044] [varchar](100) NULL, [A045] [varchar](100) NULL,
[A046] [varchar](100) NULL, [A047] [varchar](100) NULL,
[A048] [varchar](100) NULL, [A049] [varchar](100) NULL,
[A050] [varchar](100) NULL, [A051] [varchar](100) NULL,
[A052] [varchar](100) NULL, [A053] [varchar](100) NULL, [A054] [varchar](100) NULL,
[A055] [varchar](100) NULL, [A056] [varchar](100) NULL,
[A057] [varchar](100) NULL, [A058] [varchar](100) NULL,
[A059] [varchar](100) NULL, [A060] [varchar](100) NULL,
[A061] [varchar](100) NULL, [A062] [varchar](100) NULL,
[A063] [varchar](100) NULL, [A064] [varchar](100) NULL,
[A065] [varchar](100) NULL, [A066] [varchar](100) NULL,
[A067] [varchar](100) NULL, [A068] [varchar](100) NULL,
[A069] [varchar](100) NULL, [A070] [varchar](100) NULL,
[A071] [varchar](100) NULL, [A072] [varchar](100) NULL,
[A073] [varchar](100) NULL, [A074] [varchar](100) NULL,
[A075] [varchar](100) NULL, [A076] [varchar](100) NULL,
[A077] [varchar](100) NULL, [A078] [varchar](100) NULL,
[A079] [varchar](100) NULL, [A080] [varchar](100) NULL,
[A081] [varchar](100) NULL, [A082] [varchar](100) NULL,
[A083] [varchar](100) NULL, [A084] [varchar](100) NULL,
[A085] [varchar](100) NULL, [A086] [varchar](100) NULL,
[A087] [varchar](100) NULL, [A088] [varchar](100) NULL,
[A089] [varchar](100) NULL, [A090] [varchar](100) NULL,
[A091] [varchar](100) NULL, [A092] [varchar](100) NULL,
[A093] [varchar](100) NULL, [A094] [varchar](100) NULL,
[A095] [varchar](100) NULL, [A096] [varchar](100) NULL,
[A097] [varchar](100) NULL, [A098] [varchar](100) NULL,
[A099] [varchar](100) NULL, [A100] [varchar](100) NULL,
[A101] [varchar](100) NULL, [A102] [varchar](100) NULL,
[A103] [varchar](100) NULL, [A104] [varchar](100) NULL,
[A105] [varchar](100) NULL, [A106] [varchar](100) NULL,
[A107] [varchar](100) NULL, [A108] [varchar](100) NULL,
[A109] [varchar](100) NULL, [A110] [varchar](100) NULL,
[A111] [varchar](100) NULL, [A112] [varchar](100) NULL,
[A113] [varchar](100) NULL, [A114] [varchar](100) NULL,
[A115] [varchar](100) NULL, [A116] [varchar](100) NULL,
[A117] [varchar](100) NULL, [A118] [varchar](100) NULL,
[A119] [varchar](100) NULL, [A120] [varchar](100) NULL,
[A121] [varchar](100) NULL, [A122] [varchar](100) NULL,
[A123] [varchar](100) NULL, [A124] [varchar](100) NULL,
[A125] [varchar](100) NULL, [A126] [varchar](100) NULL,
[A127] [varchar](100) NULL, [A128] [varchar](100) NULL,
[A129] [varchar](100) NULL, [A130] [varchar](100) NULL,
[A131] [varchar](100) NULL, [A132] [varchar](100) NULL,
[A133] [varchar](100) NULL, [A134] [varchar](100) NULL,
[A135] [varchar](100) NULL, [A136] [varchar](100) NULL,
[A137] [varchar](100) NULL, [A138] [varchar](100) NULL,
[A139] [varchar](100) NULL,
[A140] [varchar](100) NULL, [A141] [varchar](100) NULL,
[A142] [varchar](100) NULL, [A143] [varchar](100) NULL,
[A144] [varchar](100) NULL, [A145] [varchar](100) NULL,
[A146] [varchar](100) NULL, [A147] [varchar](100) NULL,
[A148] [varchar](100) NULL, [A149] [varchar](100) NULL,
[A150] [varchar](100) NULL, [A151] [varchar](100) NULL,
[A152] [varchar](100) NULL, [A153] [varchar](100) NULL,
[A154] [varchar](100) NULL, [A155] [varchar](100) NULL,
[A156] [varchar](100) NULL, [A157] [varchar](100) NULL,
[A158] [varchar](100) NULL, [A159] [varchar](100) NULL,
[A160] [varchar](100) NULL, [A161] [varchar](100) NULL,
[A162] [varchar](100) NULL, [A163] [varchar](100) NULL,
[A164] [varchar](100) NULL, [A165] [varchar](100) NULL,
[A166] [varchar](100) NULL, [A167] [varchar](100) NULL,
[A168] [varchar](100) NULL, [A169] [varchar](100) NULL,
[A170] [varchar](100) NULL, [A171] [varchar](100) NULL,
[A172] [varchar](100) NULL, [A173] [varchar](100) NULL,
[A174] [varchar](100) NULL, [A175] [varchar](100) NULL,
[A176] [varchar](100) NULL, [A177] [varchar](100) NULL,
[A178] [varchar](100) NULL, [A179] [varchar](100) NULL,
[A180] [varchar](100) NULL, [A181] [varchar](100) NULL,
[A182] [varchar](100) NULL, [A183] [varchar](100) NULL,
[A184] [varchar](100) NULL, [A185] [varchar](100) NULL,
[A186] [varchar](100) NULL, [A187] [varchar](100) NULL,
[A188] [varchar](100) NULL, [A189] [varchar](100) NULL,
[A190] [varchar](100) NULL, [A191] [varchar](100) NULL,
[A192] [varchar](100) NULL, [A193] [varchar](100) NULL,
[A194] [varchar](100) NULL, [A195] [varchar](100) NULL,
[A196] [varchar](100) NULL, [A197] [varchar](100) NULL,
[A198] [varchar](100) NULL, [A199] [varchar](100) NULL,
[A200] [varchar](100) NULL, [A201] [varchar](100) NULL,
[A202] [varchar](100) NULL, [A203] [varchar](100) NULL,
[A204] [varchar](100) NULL, [A205] [varchar](100) NULL,
[A206] [varchar](100) NULL, [A207] [varchar](100) NULL,
[A208] [varchar](100) NULL, [A209] [varchar](100) NULL,
[A210] [varchar](100) NULL, [A211] [varchar](100) NULL,
[A212] [varchar](100) NULL, [A213] [varchar](100) NULL,
[A214] [varchar](100) NULL, [A215] [varchar](100) NULL,
[A216] [varchar](100) NULL, [A217] [varchar](100) NULL,
[A218] [varchar](100) NULL, [A219] [varchar](100) NULL,
[A220] [varchar](100) NULL, [A221] [varchar](100) NULL,
[A222] [varchar](100) NULL, [A223] [varchar](100) NULL,
[A224] [varchar](100) NULL, [A225] [varchar](500) NULL,
[A226] [varchar](500) NULL, [A227] [varchar](500) NULL,
[A228] [varchar](500) NULL, [A229] [varchar](500) NULL,
[A230] [varchar](20) NULL, [A231] [datetime] NULL,
[A232] [varchar](100) NULL, [A233] [varchar](100) NULL,
[A234] [varchar](100) NULL, [A235] [varchar](100) NULL,
[A236] [varchar](100) NULL, [A237] [varchar](100) NULL,
[A238] [varchar](100) NULL, [A239] [varchar](100) NULL,
[A240] [varchar](100) NULL, [A241] [varchar](100) NULL,
[A242] [varchar](100) NULL, [A243] [varchar](100) NULL,
[A244] [varchar](100) NULL, [A245] [varchar](100) NULL,
[A246] [varchar](100) NULL, [A247] [varchar](100) NULL,
[A248] [varchar](100) NULL, [A249] [varchar](100) NULL,
[A250] [varchar](100) NULL, [A251] [varchar](100) NULL,
[A252] [varchar](100) NULL, [A253] [varchar](100) NULL,
[A254] [varchar](100) NULL, [A255] [varchar](100) NULL,
[A256] [varchar](100) NULL, [A257] [varchar](100) NULL,
[A258] [varchar](100) NULL, [A259] [varchar](100) NULL,
[A260] [varchar](100) NULL, [A261] [varchar](100) NULL,
[A262] [varchar](100) NULL, [A263] [varchar](100) NULL,
[A264] [varchar](100) NULL, [A265] [varchar](100) NULL,
[A266] [varchar](100) NULL, [A267] [varchar](100) NULL,
[A268] [varchar](100) NULL, [A269] [varchar](100) NULL,
[A270] [varchar](100) NULL, [A271] [varchar](100) NULL,
[A272] [varchar](100) NULL, [A273] [varchar](100) NULL,
[A274] [varchar](100) NULL, [A275] [varchar](100) NULL,
[A276] [varchar](100) NULL, [A277] [varchar](700) NULL,
[A278] [varchar](700) NULL, [A279] [varchar](700) NULL,
[A280] [varchar](700) NULL, [A281] [varchar](700) NULL,
[A282] [varchar](700) NULL, [A283] [varchar](700) NULL,
[A284] [varchar](700) NULL, [A285] [varchar](700) NULL,
[A286] [varchar](700) NULL, [A287] [varchar](50) NULL,
[A288] [varchar](100) NULL, [A289] [varchar](100) NULL,
[A290] [varchar](100) NULL, [A291] [varchar](100) NULL,
[A292] [varchar](100) NULL, [A293] [varchar](100) NULL,
[A294] [varchar](100) NULL, [A295] [varchar](100) NULL,
[A296] [varchar](100) NULL, [A297] [varchar](100) NULL,
[A298] [varchar](100) NULL, [A299] [varchar](100) NULL,
[A300] [varchar](100) NULL, [A301] [varchar](100) NULL,
[A302] [varchar](100) NULL, [A303] [varchar](100) NULL,
[A304] [varchar](100) NULL, [A305] [varchar](100) NULL,
[A306] [varchar](100) NULL, [A307] [varchar](100) NULL,
[A308] [varchar](100) NULL, [A309] [varchar](100) NULL,
[A310] [varchar](100) NULL,
[A311] [varchar](100) NULL, [A312] [varchar](100) NULL,
[A313] [varchar](100) NULL

CONSTRAINT [PK_XXX] PRIMARY KEY CLUSTERED
(
[A001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_XXX_LogicalKeys] UNIQUE NONCLUSTERED
(
[A001] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
Reply With Quote
  #8 (permalink)  
Old 04-23-12, 09:48
Thrasymachus Thrasymachus is offline
Annie's Dog Walker
  
Join Date: Nov 2004
Location: at the bottom of the bottle
Posts: 8,131
Ok I will bite. What is it that we are storing in here?
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #9 (permalink)  
Old 04-23-12, 10:07
r937 r937 is offline
SQL Consultant
  
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by Thrasymachus View Post
Ok I will bite. What is it that we are storing in here?
that's obvious -- we are storing A's

tree hunnert and tirteen A's, each a 100-byte VARCHAR

so "yes, we applied normaliztion. still as per business req. we need these many columns" is a slight exaggeration

srini, this table is ~not~ normalized
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #10 (permalink)  
Old 04-23-12, 10:22
Brett Kaiser Brett Kaiser is offline
Window Washer
  
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
nope...some are 700

I don't know what you would call this..it's not even denormalized

It a big bucket with lots of compartments
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #11 (permalink)  
Old 04-23-12, 10:45
Thrasymachus Thrasymachus is offline
Annie's Dog Walker
  
Join Date: Nov 2004
Location: at the bottom of the bottle
Posts: 8,131
This is what I call the filing cabinet approach to database development.
__________________
“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.
Reply With Quote
  #12 (permalink)  
Old 04-23-12, 12:55
blindman blindman is offline
World Class Flame Warrior
  
Join Date: Jun 2003
Location: Ohio
Posts: 12,327
It's "Abby-Normalized".
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #13 (permalink)  
Old 04-24-12, 04:55
srinivasveera srinivasveera is offline
Registered User
  
Join Date: Apr 2012
Posts: 5
we have varchar fields with 100, 500 and 700 lenghts....
how can i proceed ?

and this is just a warning, not an error. still it allowing to create new columns, but in future this may become bottle neck
Reply With Quote
  #14 (permalink)  
Old 04-24-12, 05:52
gvee gvee is offline
www.gvee.co.uk
  
Join Date: Jan 2007
Location: UK
Posts: 10,339
Just as an aside: I have worked on a "legit" system with a naming convention similar to this related to the education sector. The column names refer to "fields" on a [paper] "form" e.g. A01 was learner reference number.

The design was in fact "normalized" [to a point] but you had to look up every column name before writing a query. The first thing I did was write views of every table that simply gave more appropriate field names.
__________________
George
Home | Blog
Reply With Quote
  #15 (permalink)  
Old 04-24-12, 07:58
myle myle is offline
(Making Your Life Easy)
  
Join Date: Feb 2004
Location: New Zealand
Posts: 1,237
Now I know why the eduction is stuff up

Sorry

You have answer you own question
Why not create a table call

Paper
Paperid
Pagename

Then

Client (table name)
Clientid
...
...
...

Paperdone (table name)
Clientid
Paperid
Mark
Pass/fail (yes/no)

Then in you could get smart

Course (tablename)
Courseid
Coursename
Paperid

There u could build a course that has a number of papers

Then write a query that will show if a client has competed a course base on the number of paper that client has done

With some out joins it tell you what papers need to pass to get that course completed

Or have I miss the boat
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Last edited by myle; 04-24-12 at 08:02. Reason: Spelling
Reply With Quote
Reply
Page 1 of 2 1 2

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.