How the bytes are calculated

  • Hi

    How Do I calculate Current total Bytes Per Row used

    Created table with h1 to h663 no error msg displayed

    Created table with column names h1 to h664 it says maximum row size (8071) exceeds the maximum number of bytes per row (8060)

    Created table with column names h1 to h665 it says maximum row size (8084) exceeds the maximum number of bytes per row (8060)

    Created table with column names h1 to h1024 it says maximum row size (12436) exceeds the maximum number of bytes per row (8060)

    For creating table i used

    create table mytable

    DECLARE @I INT

    SET @I=1

    WHILE @I<=1024

    BEGIN

    PRINT +'h'+Convert(varchar,@I)+' '+'Varchar (10),'

    SET @I=@I+1

    END

    INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. What it means?

    Then i tried inserting into mytable values in column h1 to h664

    h1 HRU to H665 HRU there is no error

    Then i tried with inserting into mytable values in column h1 to h664

    h1 HRU to H124 HRU there is error

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

    For inserting values Insert into Mytable i used

    DECLARE @I INT

    SET @I=1

    WHILE @I<=1024

    BEGIN

    PRINT +'''h'+Convert(varchar,@I)+' '+'HRU'+''','

    SET @I=@I+1

    END

    How the bytes are calculated

    Thanks

    Parthi

    Thanks
    Parthi

  • Please refer to the books online link for how many bytes each data types takes.

    msdn.microsoft.com/en-us/library/ms187752.aspx

    It will allow you to create table of more than 8060 byte row if you have variable length columns line varchar etc. but when row lengh exceeds 8060 it will throw the error.

    If you have varchar(MAX), VARBINARY(MAX) etc data types it will allow row length more than 8060.

  • vidya_pande (3/19/2010)


    Please refer to the books online link for how many bytes each data types takes.

    msdn.microsoft.com/en-us/library/ms187752.aspx

    It is rather more complex than that, though that can be useful to provide a very rough estimate.

    See Estimating the Size of a Table in Books Online.

    It will allow you to create table of more than 8060 byte row if you have variable length columns line varchar etc. but when row lengh exceeds 8060 it will throw the error.

    The length of the IN_ROW data cannot exceed 8060 bytes (slightly less in some circumstances), but much more variable-length data can be stored in a single row, using ROW_OVERFLOW allocation units. See Row-Overflow Data Exceeding 8KB in Books Online.

    Older LOB types and the MAX data types are never stored in ROW_OVERFLOW allocation units, but may be stored either fully IN_ROW, or off-row in LOB_DATA allocation units.

Viewing 3 posts - 1 through 2 (of 2 total)

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