Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Unanswered: 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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    350 columns?

    this immediately makes me suspect that you might perhaps need to do some normalization
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    5
    yes, we applied normaliztion. still as per business req. we need these many columns

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    Last edited by Thrasymachus; 04-20-12 at 10:42.
    “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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    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.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's "Abby-Normalized".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    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

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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

  15. #15
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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
    Last edited by myle; 04-24-12 at 09:02. Reason: Spelling
    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/A2007/A2010
    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
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •