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

Question

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

Answer 1

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

Answer 2

i am using sql2005 , how i can extend this

Answer 3

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

Answer 4

kindly check my post

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8ef5867e-568d-487e-a359-2e902b5e17c9

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

Answer 5

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

Answer 6

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 http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi