March 19, 2010 at 1:25 am
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
March 19, 2010 at 1:32 am
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.
March 28, 2010 at 6:39 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply