i am getting error in T-SQL Cannot create a row of size


  • Cannot create a row of size 8563 which is greater than the allowable maximum of 8060.

    is it possible to increase this size so that i can run the query
    Monday, June 15, 2009 10:42 PM

All replies

  • Are you using SQL2K, or SQL2005/08 ?  The limit in SQL2K is fixed. 

    In later versions, SQL Server should move your larger columns to overflow pages to prevent this error, though it impacts performance (and you can still generate it if you try hard enough).
    Michael Asher
    Monday, June 15, 2009 11:21 PM
  • i am using sql2005 , how i can extend this
    Monday, June 15, 2009 11:28 PM
  • SQL2005 moves variable length rows off page, so if you're still getting this, you must have a huge number of fixed width columns...either that, or you have so many variable-width ones that even the row-overflow pointers are overflowing the 8K limit.    Can you post your schema?  Rows this wide are generally an indication of a poor design in some manner (wrong data tables, repeating column groups, etc).

    Michael Asher
    Monday, June 15, 2009 11:35 PM
  • kindly check my post

    it has the information about scheema you are right the design is not quite good for tables as it contain more then 540 columns but even after my repted reminder the decision makers are not ready to change the table format so can't help it 

    but if you can suggest some action on how to extend this size 

    with regards
    Monday, June 15, 2009 11:40 PM
  • There isn't any way around it.  Maybe your decision-makers will reconsider the table format in this light.   If those are all fixed-length columns, you're basically stuck. 

    If you don't want to rewrite the entire application around a new schema, you can possibly offload some of the columns into a new table, that is 1:1 with the existing one.   Alternately, you might be able to change some of the datatypes to smaller versions (SMALLINT vs. INT, SMALLDATETIME vs. DATETIME, etc)

    Michael Asher
    Tuesday, June 16, 2009 12:08 AM
  • Is this when you create a table or when you run a query? If the later, then try the ROBUST PLAN hint. It is documented in Books Online, where you can read about it and how to use it.
    Tibor Karaszi, SQL Server MVP
    Tuesday, June 16, 2009 6:44 AM