Hi folks, I added a column into one of my table and got this error upon saving changes:
Warning: The table 'MY_TABLE' has been created but its maximum row size (8777) 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
What's the max limit of columns within the table? And how i would get error upon inserting or updating a row for that table?
In sql rows are stored in pages of 8K in which a maximum of 8060 bytes of data can be stored (not including text and image datatypes). In your case, the sum of the bytes for all of the columns exceed that maximum. When updating or inserting a row which exceeds this maximum, the command will fail and rollbacked.
create table mytable (myrow1 varchar(5000))
alter table mytable add myrow2 varchar(5000)
insert into mytable (myrow1) values ('aa')
declare @i as integer
, @r1 as varchar(5000)
, @r2 as varchar(5000)
set @r1 = ''
set @i = 500
while (@i > 0)
set @r1 = @r1 + '0123456789'
set @i = @i - 1
insert into mytable (myrow1) values (@r1)
insert into mytable (myrow1, myrow2) values (@r1, @r1)
select * from mytable