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.