I’ve been reasonably happy with SQL Server 2000, especially after I got it working with PHP from FreeBSD. Today though, I ran into something that I was surprised to see in SQL Server. There is a maximum row size of 8060 bytes. I was disappointed that Microsoft’s current production database system would still have this kind of limitation.
According to Chris Hedgate this is sort of fixed in SQL Server 2005. You’ll be able to have longer row sizes if they contain variable length fields which don’t exceed the 8060 byte limit. So a fixed length field greater than 8060 bytes will still be an issue. This is a partial fix at best.
I’m going to invoke the Scobleizer (Robert Scoble) and ask that he point this out the SQL Server development team. It’s 2005, if PostgreSQL can have a maximum row size of 1.6 TB and MySQL has a maximum of 65,534 bytes then surely Microsoft can throw a few million at SQL Server and get it caught up to one or both of these competing products.
10 replies on “SQL Server 2000: Maximum Row Size 8060 Bytes”
It’s teally odd to read this today since I ran into this same problem yesterday.
I too am running it with PHP (from Apache on Windows), and am quite happy with it. I’ve always thought SQL Server was one of Microsoft’s best products.
Anyway, I went to add a recursive foreign key to a large table (60+ fields) — so the table joins against itself. Sure enough, I was told that the row size was too small and it may cause failures on insert statements.
Odd to read this post the very next day.
Ah, you may want to consider using a Text field. It has a max size of around 2GB (not sure if this is right) and its “data” is not stored in the table row but rather the table row stores a “pointer” to the data that lives elsewhere. There is a performance hit, since whenever you read a record from a table with a text field, the text “data” has to be read from elsewhere on the disk. So this may be less than ideal for you.
why sql server contains row size in 8060 bytes
You may also review your table design.
A lot of times (more than should be) the problem is that the design has not been normalized.
Text data type will work for what is called free-form text but you have to retrieve and update the data differently. Refer to Books OnLine for specifics.
You may also want to investigate the ‘sp_tableoption’ stored procedure. You can set “text in row” to ON and SQL will store text, ntext or image data with the row, provided it does not exceed the row storage limit.
This is useful for performance purposes, provided your data length does not always exceed the row storage limit. See Books Online for details.
HI,
I am getting following error while inserting a row into database.
” Database Error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 8078 which is greater than the allowable maximum of 8060.”
What I suppose to do? I am using SQL server 2005.
Can u please help me out !.
Thanks & regards,
Rakesh Vende
Hi,
just now i saw a warning while creating the table in SQL Server 200, that the row size(15409) exceeds the maximum(8060).I searched for other options.TEXT field is there to fix this but i think there will be a performance hit.since my application has extensive searches.I have used nvarchar.In normal case the size of a row in my table ll be below the limit, but in worst case it can be more.
is there any option without compromisisng with performance??
@Kundan Kr Kaushik –
I never got around to looking at what the performance implications were for using text fields instead.
Just shows how cheeky Microsoft is to want to come up with different versions of SQL. I do not see why the row field limitations to 8060 bytes in SQL Server 2000 had to wait for the release of the 2005 version??
It would be nice to “truncate” microsoft from our lives forever, but I am not quite sure how!
Maybe try VARCHAR instead of NVARCHAR? NVARCHAR requires 2X as many bytes to store as a VARCHAR (I think).
Hope this helps,
Sam