In SQL Server 2000 and SQL Server 2005 a table can have a maximum of 8060 bytes per row. One of my fellow DBA said that he believed that SQL Server 2000 had that restriction but SQL Server 2005 does not have that restriction and it can have a row of 2GB. I totally agreed with him but after we discussed this problem in depth, we realized that there are more into it than only 8060 bytes limit.
It is still true for SQL Server 2005 that a table can have maximum of 8060 bytes per row however the restriction has exclusions of few data types. CLR User Defined data-types as well as varchar, nvarchar, varbinary, sql_variants are not limited to bytes per row limitation. However, those data types have limitation that they can not be more than 8000 bytes individually. In SQL Server 2005 one table row can contain more than one varchar(8000) fields. One more thing, the exclusions have exclusions also the limit of each individual column max width of 8000 bytes does not apply to varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns. Though, sum of all other other kind of data type should be less than 8060 bytes. That means one table can not have three columns with char(4000) or 100K bit field columns.
In summary in SQL Server 2005
- Table row can have more than 8060 bytes. (2GB Max)
- varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns can have max 8000 bytes.
- varchar(max), nvarchar(max), varbinary(max), text, image or xml data type columns have no restrictions.
- All the other data type columns (other than mentioned in above three points) width addition must be still under 8060 byte row limit.
- Index can only be created which falls with-in 8060 byte row limit.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL, SQL Stuff
8 Comments. Leave new
I have a table in SQL Server 2000 with two text fields. I’d like to change the two text fields to varchar(8000), but I get this error:
– Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot create a row of size 14844 which is greater than the allowable maximum of 8060.
I’m not concerned about data truncation; that’s fine for my situation. I don’t understand, though, how a table can allow two text fields but not two varchar fields. Do you know if there is a way around this?
Thanks in advance! This site is a really great resource.
because Varchar has a maximum size a 1kb,
thus 255 characters. each character is 2 bytes,
plus 2 bytes as a header.
and a text type is an array of varchars,
so think of each line sa a varchar, and there are many lines.
keep in mind that the maximum row size is 8kb.
there is method of calculating field sizes, google it for more info.
I hope this helps
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!
i had a problem to store more than 8000 characters in msssql 2005. i had used varchar(max), text and all possible datatypes but could not find the solution. can u please give me solution???
Is there a way to prevent row-overflow? In Oracle we use pctfree on the creation of a table to prevent this allowing extra freespace for column updates. Will rebuilding the table get rid of the overflow values?
Well I have a solution I have made and tested in C#
1 Create a Split File App to Split the large file
2 Create a button click button to search the folder for the split file and insert them peace by peace
I did it and it work
So the fist the code will split the large file into multiple peaces then insert them one by one
3 jst do the same in reverse download n merge the files from sql server