none
How to increase row size in SQL 2008

    Question

  • Hi,
    While altering my table in sql 2008 standard. It throws error

    Warning: The table "t1_Data" 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.
    Msg 511, Level 16, State 1, Line 1
    Cannot create a row of size 14510 which is greater than the allowable maximum row size of 8060.
    The statement has been terminated.

    Help me how to increase row size
    Monday, December 07, 2009 7:06 AM

Answers

All replies

  • Hi,

    If you want to alter your column size

    Alter Table TableName Alter Column ColumnName Nvarchar(max)
    Rajesh Jonnalagadda http://www.ggktech.com
    Monday, December 07, 2009 7:19 AM
  • Hi Jhonny,


    It looks like you have too much data in the row already.
    What is the total size of the other columns?
    SQL Server will allow you to define a column of a variable length size, that
    could end up leading to errors on insert or update.

    For example, you could create a table that has 10 varchar(8000) columns,
    with no error.
    But if you tried to insert 8000 bytes into 2 of those columns, you would get
    an error.

    The only way to have more than 8060 bytes in a row is to use the
    text data type.


    Thanks,
    Ramakrishnae
    Monday, December 07, 2009 7:36 AM
  • Hi RamaKrishnae,

         Thanks to reply. You are correct. i have 1500 columns in my table & with VARCHAR(4000) as data type. Is there any settings to increase the row size


    Monday, December 07, 2009 7:44 AM
  • Hello ,


    The max size of a row is 8060 bytes. This is because the size of a data page is 8K.



    http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/7d432ec0-129c-4485-adfe-90ab9fe888ac



    Thanks,
    Krishna
    www.sqlserver.in
    http://blogs.sqlserver.in
    Monday, December 07, 2009 8:34 AM
  • Hi Rajesh,

                 Even if you run alter statement also we will get same sql error.
    Monday, December 07, 2009 8:35 AM
  • Hi,

    Unfortunately, with SQL Server it is not possible to configure the row size. Use  the text data type  to overcome this.


    Regards,
    Ramakrishna


    • Marked as answer by Jhonny.Greypad Monday, December 07, 2009 10:15 AM
    Monday, December 07, 2009 8:35 AM
  • Hello,


    Could you pls give me the table structure script & also the script you are trying to alter with.



    Thanks,
    Krishna
    www.sqlserver.in
    http://blogs.sqlserver.in
    Monday, December 07, 2009 10:04 AM
  • Hi Rajeesh,

          I cant send the scripts. But its nothing but 1500 columns of type varchar(8000). Alter each column types to varchar(100) that's all.
    Monday, December 07, 2009 10:14 AM
  • Hi

    Thanks
    • Marked as answer by Jhonny.Greypad Monday, December 07, 2009 10:15 AM
    Monday, December 07, 2009 10:15 AM
  • Hello,


    You could only have 1024 columns & also can alter it say to varchar(100) with out errors.


    But note here from SQL 05 , the column for a record more than the 8k size is moved to another page. ie., rows will span to other pages, which is a performnce impact. This is called ROW_OVERFLOW_DATA allocation unit.



    Thanks,
    Krishna
    www.sqlserver.in
    http://blogs.sqlserver.in
    Monday, December 07, 2009 11:27 AM
  • U can use the datatype nvarchar(max) or ntext, then it support up to 2GB of Data.


    For more details log on www.thiruna.blog.com

    Regards,
    RM Thirunavukkarasu

    Monday, December 07, 2009 12:35 PM
  • Hi,

    I too am receiving the warning

    Warning: The table "TEST_1" 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.

    The table has 346 columns, I have set the datatypes of each column to nvarchar (max) But am still receiving this warning on creation.

    With reference to the following comment from RM Thirunavukkarasu;

    U can use the datatype nvarchar(max) or ntext, then it support up to 2GB of Data.

    I had expected to no longer receive this warning after using the datatype nvarchar(max)..

    Have I misunderstood? Any clarification greatly appreciated. Thanks



    • Edited by learner1959 Tuesday, January 22, 2013 5:00 PM
    Tuesday, January 22, 2013 4:57 PM
  • quick/deep hint for MS

    we're already in the 21st century...

    Saturday, March 16, 2013 3:19 PM