This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I already have written a couple of articles on data overflow and data exceeding problem of SQL Server.
For theory – You can access below articles because, In this post, I am sharing the practical example.
SQL Server: What happened, when Row Overflow and Data Exceeding 8KB?
Execute below steps and understand the 8kb fundamentals of SQL Server:
Try to create a large table which exceeds 8055 bytes:
|
1 2 3 4 5 6 7 |
CREATE TABLE tbl_LargeTable ( CharColumn1 CHAR(5000) ,CharColumn2 CHAR(3000) ,CharColumn3 CHAR(55) ) GO |
You will get a below error:
|
1 2 |
Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'tbl_LargeTable' failed because the minimum row size would be 8062, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes. |
Now, try to create a large table by adding one varchar column:
I changed CharColumn3 CHAR(55) to CHAR(40), and added new Varchar column.
Here, you can create a table with the warning only because VARCHAR is variable type so if you try to add more data in VARCHAR which are crossing 8050 bytes, your insert or update will fail.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_LargeTable ( CharColumn1 CHAR(5000) ,CharColumn2 CHAR(3000) ,CharColumn3 CHAR(40) ,VarCollumn1 VARCHAR(1000) ) GO |
You will get a below warning:
|
1 |
Warning: The table "tbl_LargeTable" 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 Example of Successful INSERT:
|
1 2 3 4 5 6 7 8 |
INSERT INTO tbl_LargeTable VALUES ( REPLICATE('z', 5000), REPLICATE('b', 3000), REPLICATE('c', 40), REPLICATE('a', 5) ) |
The example of failed INSERT:
|
1 2 3 4 5 6 7 8 |
INSERT INTO tbl_LargeTable VALUES ( REPLICATE('z', 5000), REPLICATE('b', 3000), REPLICATE('c', 40), REPLICATE('a', 10) ) |
You will get below error:
|
1 2 3 |
Msg 511, Level 16, State 1, Line 1 Cannot create a row of size 8061 which is greater than the allowable maximum row size of 8060. The statement has been terminated. |
Leave a Reply