StoryA long time ago we ran our application on PostgreSQL. We had a table which had 5 text fields and we had no problem to query that table without any limitations.Later on we were forced by customer to switch to MS SQL 2000 database and we converted our PostgreSQL fields to TEXT in SQL 2000 and found that some queries stopped working as TEXT data type has some limitations (for example DISTINCT can not be used on table if you select text column as part of the query). Being a smart guys and lazy programmers we counted stats on text fields and found that it will be safe to convert all our TEXT columns to VARCHAR(8000) and most importantly to keep code unchanged. The only thing we had to do is to validate if users input do not exceed 8000 char length. So far so good.After this was done we noticed that every time we alter tables with multiple varchar(8000) columns we have a warning that data will be truncated if row size will exceed row size limit.We did not like it. We counted not combined field’s length stats but individual columns size stats.Now if total size of the record exceeded size limit for SQL 2000 we could potentially loose some data which is not good.Being a smart guys and lazy programmers we decided to replace table X with table x_table and create view X instead and at the same time move all varchar(8000) column to separate tables. In this case we thought we do not need to change code as it still works with X even though it is a view now until …. today I hit following error on UPDATE -Server: Msg 510, Level 16, State 2, Line 1Cannot create a worktable row larger than allowable maximum.Resubmit your query with the ROBUST PLAN hint.
Query:select sum(character_maximum_length) from information_schema.columns where table_name= 'pap' and data_type like '%char'Returns:40000
Same query with 'OPTION (ROBUST PLAN)' at the end as suggested by previous messageServer: Msg 8619, Level 16, State 2, Line 1Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes. Resubmit your query without the ROBUST PLAN hint.
This is a view which basically joins main table with five tables each keeping varchar(8000) field and linked with main record 1:1 relationship.SELECT dbo.pap_table.pap_id, dbo.pap_table.facility_id, dbo.pap_table.pap_status_id, dbo.pap_description_det.descr AS description, dbo.pap_table.responsible_id, dbo.pap_table.date_planned, dbo.pap_table.date_actual, dbo.pap_result_det.descr AS result, dbo.pap_table.department_id, dbo.pap_comments_visible_det.descr AS comments_visible, dbo.pap_comments_invisible_det.descr AS comments_invisible, dbo.pap_table.created_by, dbo.pap_table.date_created, dbo.pap_table.last_modified_user_id, dbo.pap_table.last_modified, dbo.pap_table.pap_external_id, dbo.pap_table.pap_type_id, dbo.pap_planned_action_det.descr AS planned_actionFROM dbo.pap_table INNER JOIN dbo.pap_description_det ON dbo.pap_table.pap_id = dbo.pap_description_det.id INNER JOIN dbo.pap_result_det ON dbo.pap_table.pap_id = dbo.pap_result_det.id INNER JOIN dbo.pap_comments_visible_det ON dbo.pap_table.pap_id = dbo.pap_comments_visible_det.id INNER JOIN dbo.pap_comments_invisible_det ON dbo.pap_table.pap_id = dbo.pap_comments_invisible_det.id INNER JOIN dbo.pap_planned_action_det ON dbo.pap_table.pap_id = dbo.pap_planned_action_det.id
SolutionBeing a smart guys and lazy programmers I see that we will have to convert varchar(8000) back to text and finally rewrite all code to accommodate this new data structure. What will you suggest?Thank you
Orchestrating Architecture
Posted - 2008-04-04 : 03:18:48