When working with SQL Server, it is important to understand the limitations on the size of a row in a table. In SQL Server 2000 and SQL Server 2005, a table could have a maximum of 8060 bytes per row. However, there are some exceptions to this rule that we will explore in this article.
Initially, there was a misconception that SQL Server 2005 did not have the 8060 bytes per row limitation. However, upon further investigation, it was discovered that this restriction still applies. The difference lies in the exclusions for certain data types.
CLR User Defined data types, as well as varchar, nvarchar, varbinary, and sql_variant data types, are not limited to the 8060 bytes per row limitation. However, these data types have their own limitations. They cannot exceed 8000 bytes individually.
In SQL Server 2005, it is possible for a table row to contain multiple varchar(8000) fields. This means that you can have more than one column with a maximum width of 8000 bytes. However, it is important to note that this exclusion does not apply to varchar(max), nvarchar(max), varbinary(max), text, image, or xml data type columns. These data types have no restrictions on their size.
It is crucial to keep in mind that the sum of all other data type columns, excluding the ones mentioned above, must still be less than the 8060 byte row limit. This means that you cannot have three columns with char(4000) or 100K bit field columns in a single table.
In summary, in SQL Server 2005, a table row can have more than 8060 bytes, with a maximum limit of 2GB. Varchar, nvarchar, varbinary, sql_variant, or CLR user-defined type columns can have a maximum of 8000 bytes individually. Varchar(max), nvarchar(max), varbinary(max), text, image, or xml data type columns have no restrictions. All other data type columns must still adhere to the 8060 byte row limit. Indexes can only be created within the 8060 byte row limit.
Understanding these limitations is crucial when designing and working with SQL Server databases. It ensures that you can optimize your table structures and avoid any potential issues related to row size limitations.