SQL Server Performance Forum – Threads Archive
SQL Query Length problem. Please help
Hi, I have a query which updates a record in my table which has a field of varchar(8000).I dont want it as "text".Now my problem is , when I fire the SQL query, with the description having a length of 8000 characters, I get an error "Cannot create a row of size 8126 which is greater than the allowable maximum of 8060" Please help. Thanks.
maximum size of a record in sql rerver 2000 is 8060. but you can virtually create a table with a record length more than 8060. if you have columns with variant lengths
like varchars, or nullable columns, sql server assumes it might or might not exceed the maximum size, so it leaves to calculate the actual record length at inserts or updates of the table for every single record. it seems your table has more than one column, so sum 8000 bytes up with the actual length of the other columns, if get a number more 8060, that it is, you get that error.
if you hate using text type columns, you must split your table.
This error message indicates that you have variable length columns in your table (such as nvarchar or varbinary) and that the total maximum length of all the columns adds up to more than 8,060 bytes. You can still insert rows into the table if the total length of the data in each row does not exceed 8,060 bytes. HTH Satya SKJ
SQL has a limit of 8060 bytes for a single row. VARCHAR fields will only take up as much space as they need (hence the VAR part of the name – VARiable) so most of the time you will not run into this limit if the data VARCHAR field is much smaller than 8000 characters. Unfortunately you will have to either truncate your data, use a text column or split your table into two. If you remove the VARCHAR(8000) from the table and create a new table with the VARCHAR(8000) column and the primary key column, you can then join the two tables to retrieve the same results (possibly using a view). Your inserts/updates will have to be re-written to insert the data into both tables.
Also keep in mind that when you use a very large VARCHAR (essentially any row that stores more than about 4030 bytes long) an entire data page is used, even if a VARCHAR. This is because a data page, which is 8K, can only hold one row if the size of the data is over half the size of the page. What this means that a lot of data can potentially be wasted. This wasted space is not only space wasted on disk, but space wasted in the buffer cache when the page is read in. Wasted space like this has the potential of unnecessarily wasting server resources, which can hurt performance. Of course, this may not apply in your case, but if you find that there are a lot of rows just over this half way mark, it could negatively affect your application’s performance.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>