Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2003
    Posts
    20

    Unanswered: row limit in MSSQL

    There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
    My questions are :
    - Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
    - Will be this ok in SQL server 2005?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    There is no way to overcome this limit in SQL 2000 other than to split the one table into multiple tables (as it seems you have already done).

    Another workaround might be to take some of your longer varchar fields and convert them into text; however, you will lose some functionality (searching and indexing) if you choose this option.

    Regards,

    hmscott

    Quote Originally Posted by Sladky
    There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
    My questions are :
    - Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
    - Will be this ok in SQL server 2005?
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

    -PatP

  4. #4
    Join Date
    Dec 2003
    Posts
    20
    Quote Originally Posted by Pat Phelan
    Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

    -PatP
    I seems to be different for me. I have row size in table roughly 5k but I need to connect more tables. Than (during SELECT command) this error comes.

  5. #5
    Join Date
    Dec 2003
    Posts
    20

    Question Ms Sql 2005

    Does someone know if this will be fixed in MS SQL 2005 ?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try this one on for size, it generates lots of 16 Kb+ rows in the result set.
    Code:
    CREATE TABLE Sladky0 (
       pk		INT		IDENTITY PRIMARY KEY (pk)
    ,  junque00	CHAR(255)	NOT NULL DEFAULT '00'
    ,  junque01	CHAR(255)	NOT NULL DEFAULT '01'
    ,  junque02	CHAR(255)	NOT NULL DEFAULT '02'
    ,  junque03	CHAR(255)	NOT NULL DEFAULT '03'
    ,  junque04	CHAR(255)	NOT NULL DEFAULT '04'
    ,  junque05	CHAR(255)	NOT NULL DEFAULT '05'
    ,  junque06	CHAR(255)	NOT NULL DEFAULT '06'
    ,  junque07	CHAR(255)	NOT NULL DEFAULT '07'
    ,  junque08	CHAR(255)	NOT NULL DEFAULT '08'
    ,  junque09	CHAR(255)	NOT NULL DEFAULT '09'
    ,  junque0a	CHAR(255)	NOT NULL DEFAULT '0a'
    ,  junque0b	CHAR(255)	NOT NULL DEFAULT '0b'
    ,  junque0c	CHAR(255)	NOT NULL DEFAULT '0c'
    ,  junque0d	CHAR(255)	NOT NULL DEFAULT '0d'
    ,  junque0e	CHAR(255)	NOT NULL DEFAULT '0e'
    ,  junque0f	CHAR(255)	NOT NULL DEFAULT '0f'
    )
    
    CREATE TABLE Sladky1 (
       pk		INT		NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
    ,  junque10	CHAR(255)	NOT NULL DEFAULT '10'
    ,  junque11	CHAR(255)	NOT NULL DEFAULT '11'
    ,  junque12	CHAR(255)	NOT NULL DEFAULT '12'
    ,  junque13	CHAR(255)	NOT NULL DEFAULT '13'
    ,  junque14	CHAR(255)	NOT NULL DEFAULT '14'
    ,  junque15	CHAR(255)	NOT NULL DEFAULT '15'
    ,  junque16	CHAR(255)	NOT NULL DEFAULT '16'
    ,  junque17	CHAR(255)	NOT NULL DEFAULT '17'
    ,  junque18	CHAR(255)	NOT NULL DEFAULT '18'
    ,  junque19	CHAR(255)	NOT NULL DEFAULT '19'
    ,  junque1a	CHAR(255)	NOT NULL DEFAULT '1a'
    ,  junque1b	CHAR(255)	NOT NULL DEFAULT '1b'
    ,  junque1c	CHAR(255)	NOT NULL DEFAULT '1c'
    ,  junque1d	CHAR(255)	NOT NULL DEFAULT '1d'
    ,  junque1e	CHAR(255)	NOT NULL DEFAULT '1e'
    ,  junque1f	CHAR(255)	NOT NULL DEFAULT '1f'
    )
    
    CREATE TABLE Sladky2 (
       pk		INT		NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
    ,  junque20	CHAR(255)	NOT NULL DEFAULT '20'
    ,  junque21	CHAR(255)	NOT NULL DEFAULT '21'
    ,  junque22	CHAR(255)	NOT NULL DEFAULT '22'
    ,  junque23	CHAR(255)	NOT NULL DEFAULT '23'
    ,  junque24	CHAR(255)	NOT NULL DEFAULT '24'
    ,  junque25	CHAR(255)	NOT NULL DEFAULT '25'
    ,  junque26	CHAR(255)	NOT NULL DEFAULT '26'
    ,  junque27	CHAR(255)	NOT NULL DEFAULT '27'
    ,  junque28	CHAR(255)	NOT NULL DEFAULT '28'
    ,  junque29	CHAR(255)	NOT NULL DEFAULT '29'
    ,  junque2a	CHAR(255)	NOT NULL DEFAULT '2a'
    ,  junque2b	CHAR(255)	NOT NULL DEFAULT '2b'
    ,  junque2c	CHAR(255)	NOT NULL DEFAULT '2c'
    ,  junque2d	CHAR(255)	NOT NULL DEFAULT '2d'
    ,  junque2e	CHAR(255)	NOT NULL DEFAULT '2e'
    ,  junque2f	CHAR(255)	NOT NULL DEFAULT '2f'
    )
    
    CREATE TABLE Sladky3 (
       pk		INT		NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
    ,  junque30	CHAR(255)	NOT NULL DEFAULT '30'
    ,  junque31	CHAR(255)	NOT NULL DEFAULT '31'
    ,  junque32	CHAR(255)	NOT NULL DEFAULT '32'
    ,  junque33	CHAR(255)	NOT NULL DEFAULT '33'
    ,  junque34	CHAR(255)	NOT NULL DEFAULT '34'
    ,  junque35	CHAR(255)	NOT NULL DEFAULT '35'
    ,  junque36	CHAR(255)	NOT NULL DEFAULT '36'
    ,  junque37	CHAR(255)	NOT NULL DEFAULT '37'
    ,  junque38	CHAR(255)	NOT NULL DEFAULT '38'
    ,  junque39	CHAR(255)	NOT NULL DEFAULT '39'
    ,  junque3a	CHAR(255)	NOT NULL DEFAULT '3a'
    ,  junque3b	CHAR(255)	NOT NULL DEFAULT '3b'
    ,  junque3c	CHAR(255)	NOT NULL DEFAULT '3c'
    ,  junque3d	CHAR(255)	NOT NULL DEFAULT '3d'
    ,  junque3e	CHAR(255)	NOT NULL DEFAULT '3e'
    ,  junque3f	CHAR(255)	NOT NULL DEFAULT '3f'
    )
    
    DECLARE
       @loop	INT
    ,  @pk		INT
    
    SET @loop = 10
    
    WHILE 0 < @loop
       BEGIN
          INSERT INTO Sladky0 (junque00) VALUES (DEFAULT)
          SET @pk = @@identity
          INSERT INTO Sladky1 (pk) VALUES (@pk)
          INSERT INTO Sladky2 (pk) VALUES (@pk)
          INSERT INTO Sladky3 (pk) VALUES (@pk)
          SELECT @loop = @loop - 1
       END
    
    SELECT *
       FROM Sladky0
       JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
       JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
       JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
    -PatP
    Last edited by Pat Phelan; 09-09-04 at 05:01. Reason: Typo caused by cut and paste problem (oops)!

  7. #7
    Join Date
    Dec 2003
    Posts
    20
    ok , thanks

  8. #8
    Join Date
    Dec 2003
    Posts
    20
    I had a bit different SQL command in my application. I have join done over where condition and used DISTINCT in command.
    But I found if I use DISTICNT keyword before that this error comes even with your offered SQL command with JOINs. So it seems to me different with DISTINCT or not - but why? So I cannot use keyword distinct for bigger result over tables - I guess.

    (
    SELECT DISTINCT *
    FROM Sladky0
    JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
    JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
    JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
    ) doesn't work

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Avoid using DISTINCT this way (it implicity creates a table), use a GROUP BY instead.

    -PatP

  10. #10
    Join Date
    Dec 2003
    Posts
    20
    Does it mean that it is better to mention all columns in GROUP BY statement than use DISCTINCT expression? So I have very long statements. To mention only some of them is not possible I think.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Listing every returned column in a GROUP BY is the only was I know to avoid creating the work table that is created by DISTINCT.

    -PatP

  12. #12
    Join Date
    Dec 2003
    Posts
    20
    I have tryied it - it really works fine. Thanks

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I know that it is something of a pain to have to deal with the problem by changing your code, but the designers made some trade off decisions in the query engine that work very well for 99.9% of the queries, but clobber queries like yours. It is annoying to have your code be the "lucky" one that needs to be changed, but at least the change is better than waiting for another database upgrade (at least in most cases)!

    -PatP

  14. #14
    Join Date
    Dec 2003
    Posts
    20
    I agree with you. I am glad that I have solution for now for MSSQL server.
    In our application I try to have as much as possible common code for MySQL (ODBC, native), MSSQL (ODBC), ORACLE(ODBC, native),... but you can imagine a lot of different parts.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yukon allows rows to span across multiple pages.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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