Create database from SQL script

  • mdp

    Ten Centuries

    Points: 1172

    I got this warning message while trying to create the database, "Warning: The table 'Zeuqram_Tools' has been created but its maximum row size (9757) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes."

    Is it true that it will not let me INSERT or UPDATE to this table?

    Any help would be greatly appreciated.

  • GRN

    Hall of Fame

    Points: 3210

    What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail

    as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts

  • mdp

    Ten Centuries

    Points: 1172

    What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail

    as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts

    Thank you very much for your help. Does SQL SERVER 2000 allow you to change the maximum number of bytes per row?

  • mdp

    Ten Centuries

    Points: 1172

    quote:


    What it means is that the row size for the table has exceeded 8060 which is the max allowed and if u insert/update a rec which exceeds this size the insert or update WILL fail

    as long as the update / insert keeps the row size within the limit u are safe ..an option would be to split the table into 2 parts

    Thank you very much for your help. Does SQL SERVER 2000 allow you to change the maximum number of bytes per row? Also, if the maximum number of bytes per row is only 8060 how can you store images or CLOB data?


  • GRN

    Hall of Fame

    Points: 3210

    nope u cannot change the max size of the row...

    for the blob/image/text fields the storage is different . i think in sql 7 it was implemented as a linked list and in 2000 its pointers . so the data is not actually stored as a part of the table...

  • Andy Warren

    SSC Guru

    Points: 119676

    You're definitely taking a risk defining a table where there is the possibility that the data length could exceed the row capacity. Splitting the table and using triggers to maintain the 1 to 1 relationship is one way, but I'd suggest you take another look your design to see if you can't normalize this table further.

    Not only do you risk your app blowing up at some point, super wide rows like this kill your io rates.

    Andy

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Andy makes a good point. Pages are 8KB in SQL Server 7 to take advantage of the I/O from the OS perspective, an improvement over SQL 6.5's 2K pages. Because of this, SQL Server 2000 makes no increase in the row size in order to stay within the 8K page limit. Columns with text, ntext, and image data types are treated differently.

    From Books Online:

    quote:


    Text, ntext, and image values are not stored as part of the data row but in a separate collection of pages of their own. For each text, ntext, or image value, all that is stored in the data row is a 16-byte pointer. For each row, this pointer points to the location of the text, ntext, or image data. A row containing multiple text, ntext, or image columns has one pointer for each text, ntext, or image column.


    K. Brian Kelley

    bk@warpdrivedesign.org

    K. Brian Kelley
    @kbriankelley

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply