Monday, June 05, 2006 10:32 AM
I am getting following error during executing a insert query.
Error Message : Cannot create a worktable row larger than allowable maximum.
Resubmit your query with the ROBUST PLAN hint.
1. The max rowsize for a table in SQL Server 2000 is 8060 characters.
2. The query was working fine for data below the limit, it failed
for the data above the limit.
3. I had tested the UPDATE query with option (ROBUST PLAN ), but it didn’t work.
if any one have clue to solve this problem plzz reply soon.
-thanks in advance
Monday, June 05, 2006 5:56 PM
A row in SQL server can not exceed a page = 8K = 8060 bytes + header info etc
you can create a table that could hold 16000 bytes
create table abc (Col1 varchar(8000), col2 varchar(8000))
and you will get this warning
Warning: The table 'abc' has been created but its maximum row size (16025) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
-- no problem
insert abc values (replicate('a',4000), replicate('a',4000))
-- no problem
insert abc values (replicate('a',8000), null)
-- here we get the error
insert abc values (replicate('a',4030), replicate('a',4031))
Server: Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8074 which is greater than the allowable maximum of 8060.
The statement has been terminated.
--let's start from scratch
drop table abc
-- use text columns
create table abc (Col1 text, col2 text)
insert abc values (replicate('a',40030), replicate('a',40031))
when using text columns you can insert the data without a problem however you can not use certain functions, order by etc etc
In SQL 2005 you can use VARCHAR(MAX) and you can eliminate some of the problems of working with text data ( TEXTPTR,READTEXT, WRITETEXT, and UPDATETEXT )
Denis the SQL Menace
- Proposed As Answer by Douglas Turque Wednesday, October 17, 2012 6:45 PM
Tuesday, June 06, 2006 4:07 AM
Thanks for quick reply but i want to also know is this be possible with some
column nvarchar and some text column in a table i.e.
create table abc (Col1 text, col2 nvarchar(4000))
insert abc values (replicate('a',40031), replicate('a',4000))
Tuesday, June 06, 2006 7:04 AMBLOB field values usually stored out of the record (until otherwise is specified), it's only 16 byte pointer that stored in the record.