June 22, 2020 at 8:42 am
Hi all
We've got some fairly lengthy pieces of (free)text we need to store for reporting purposes.
We've created the fields to store them as VARCHAR(MAX) fields in the vain hope that they would be stored off-row.
Unfortunately, that's not the case.
The database engine is still trying to store them in-row as they are each less than 8000 characters.
I did a bit of research and found the following code (edited to match my needs):-
USE MeditechDocuments
GO
EXEC sp_tableoption
@TableNamePattern =
'dbo.wtbl_MT_EMR_Document_Holistic_Needs_Assessment_GENEHNA',
@OptionName =
'large value types out of row',
@OptionValue = 1
GO
which should (according to my research) have forced all the varchar(max) fields off-row but it's not happening.
I'm still the following error:-
Cannot create a row of size 8093 which is greater than the allowable maximum row size of 8060.
The biggest field is 513 characters and, unfortunately, we have to store the lot. This is also a big document with quite a few fields.
The code to create all the relevant tables is almost self-sustaining so I don't want to tinker with it as it will affect around 600 other tables that are working fine.
Do anyone have any ideas as to why the code above isn't forcing the varchar(max) fields off-row with just a 16-byte pointer?
Is it something as simple s retsarting the SQL Server Service to make this take effect?
I've cleared down the affected table (i.e. truncated it) and tried a full reload but I'm still getting the error.
Any help on this would be greatly appreciated.
TIA
Richard
June 22, 2020 at 1:28 pm
Richard
What indexes do you have on that table? Do any of them have a max column as part of the key?
John
June 22, 2020 at 1:38 pm
These fields are part of the primary key:-
[pkDocumentID] varchar(30)
[pkEditedByStaffID] varchar(25)
[pkEditedDateTime] datetime
This is the only index on this table.
Like I said in my OP, I did find a method of forcing the VARCHAR(MAX) columns off-row but I can't get it to work.
June 22, 2020 at 1:46 pm
Changing the option only applies for new rows. The rows that were in the table before you changed the option will stay where they were. Could that be what's happening here? You'll need to copy the rows out and pull them back in to move the existing rows.
John
June 22, 2020 at 1:58 pm
Changing the option only applies for new rows. The rows that were in the table before you changed the option will stay where they were. Could that be what's happening here? You'll need to copy the rows out and pull them back in to move the existing rows.
John
That's mostly correct except you don't need to copy the rows out. Updates on the MAX column(s) will have the same effect. Just do an "In-Place" update where you update the MAX column to it self. You CAN save a whole lot of time by filtering by the length of the column for your update.
To recover the disk space and increase the page density of the Clustered Index after the "move" to out of row, you'll need to rebuild the clustered index, which should go a lot faster than ever before. The performance of necessary table scans should also increase by quite a bit when all is said and done.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2020 at 1:58 pm
I actually did a drop/create on the table and then tried a reload but still ran into the same issue.
June 22, 2020 at 2:21 pm
John Mitchell-245523 wrote:Changing the option only applies for new rows. The rows that were in the table before you changed the option will stay where they were. Could that be what's happening here? You'll need to copy the rows out and pull them back in to move the existing rows.
John
That's mostly correct except you don't need to copy the rows out. Updates on the MAX column(s) will have the same effect. Just do an "In-Place" update where you update the MAX column to it self. You CAN save a whole lot of time by filtering by the length of the column for your update.
To recover the disk space and increase the page density of the Clustered Index after the "move" to out of row, you'll need to rebuild the clustered index, which should go a lot faster than ever before. The performance of necessary table scans should also increase by quite a bit when all is said and done.
@jeff - that was my understanding but the change doesn't seem to have made a difference as the data still won't load.
June 22, 2020 at 3:33 pm
Have you verified that the out of row setting on the table is actually active?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2020 at 3:39 pm
We'd have to see the table DDL to find the exact cause of that.
Definitely force the varchar(max) data off row, as you have done.
You should also page compress the table (the clus index), given the large number of varchar(<max) columns you have. Note: it could take a long time to initially compress a large volume of data, although varchar(max) data won't be compressed. You'd have to explicitly compress (max) data yourself, although I very often find it's worth doing, since it can save a huge amount of disk space.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 22, 2020 at 3:39 pm
Aaaaarrrrrggggghhhhh!!!!!!
Thanks Jeff.
I've just checked and it's been switched off. This is potentially because we had a bug in our code which meant that the table was being dropped and recreated in error.
I'll add the specific code to do this into our table-creation code and then try again.
June 22, 2020 at 3:58 pm
Aaaaarrrrrggggghhhhh!!!!!!
Thanks Jeff.
I've just checked and it's been switched off. This is potentially because we had a bug in our code which meant that the table was being dropped and recreated in error.
I'll add the specific code to do this into our table-creation code and then try again.
You're welcome. Heh... I'm a little embarrassed to say it was the "voice of experience" on this one. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2020 at 8:54 am
And the plot thickens......
I've just verified that the large_value_types_out_of_row is 1 on the table in question.
I'm still getting an error that reads:-
"Cannot create a row of size 8093 which is greater than the allowable maximum row size of 8060."
The table creation statement is here:-
USE [MeditechDocuments]
GO
/****** Object: Table [dbo].[tbl_MT_EMR_Document_Holistic_Needs_Assessment_GENEHNA] Script Date: 23/06/2020 09:49:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_MT_EMR_Document_Holistic_Needs_Assessment_GENEHNA](
[pkDocumentID] [VARCHAR](30) NOT NULL,
[pkEditedByStaffID] [VARCHAR](25) NOT NULL,
[pkEditedDateTime] [DATETIME] NOT NULL,
[fkAccountNumber] [VARCHAR](12) NOT NULL,
[fkPatientLocalID] [VARCHAR](10) NULL,
[fkCreatedByStaffID] [VARCHAR](25) NULL,
[CreatedDateTime] [DATETIME] NULL,
[DocumentStatus] [VARCHAR](15) NULL,
[ConcernsChecklist_Ehnalabel] [VARCHAR](MAX) NULL,
[ConcernsChecklist_Ehnalabel2] [VARCHAR](MAX) NULL,
[ConcernsChecklist_KeyWorker] [VARCHAR](MAX) NULL,
[ConcernsChecklist_KeyWorker_Comment] [VARCHAR](MAX) NULL,
[ConcernsChecklist_ContactNumber] [VARCHAR](MAX) NULL,
[ConcernsChecklist_ContactNumber_Comment] [VARCHAR](MAX) NULL,
[PhysicalConcerns_BreathingDifficulties_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_BreathingDifficulties_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_BreathingDifficulties] [VARCHAR](MAX) NULL,
[PhysicalConcerns_BreathingDifficulties_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PassingUrine_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PassingUrine_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PassingUrine] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PassingUrine_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Constipation_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Constipation_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Constipation] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Constipation_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Diarrhoea_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Diarrhoea_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Diarrhoea] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Diarrhoea_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_EatingAppetiteOrTaste_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_EatingAppetiteOrTaste_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_EatingAppetiteOrTaste] [VARCHAR](MAX) NULL,
[PhysicalConcerns_EatingAppetiteOrTaste_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Indigestion_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Indigestion_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Indigestion] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Indigestion_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swallowing_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swallowing_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swallowing] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swallowing_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Cough_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Cough_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Cough] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Cough_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SoreOrDryMouthOrUlcers_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SoreOrDryMouthOrUlcers_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SoreOrDryMouthOrUlcers] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SoreOrDryMouthOrUlcers_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_NauseaOrVomiting_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_NauseaOrVomiting_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_NauseaOrVomiting] [VARCHAR](MAX) NULL,
[PhysicalConcerns_NauseaOrVomiting_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TiredExhaustedOrFatigued_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TiredExhaustedOrFatigued_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TiredExhaustedOrFatigued] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TiredExhaustedOrFatigued_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swelling_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swelling_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swelling] [VARCHAR](MAX) NULL,
[PhysicalConcerns_Swelling_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HighTemperatureOrFever_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HighTemperatureOrFever_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HighTemperatureOrFever] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HighTemperatureOrFever_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MovingAroundwalking_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MovingAroundwalking_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MovingAroundwalking] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MovingAroundwalking_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TinglingInHandsOrFeet_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TinglingInHandsOrFeet_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TinglingInHandsOrFeet] [VARCHAR](MAX) NULL,
[PhysicalConcerns_TinglingInHandsOrFeet_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PainOrDiscomfort_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PainOrDiscomfort_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PainOrDiscomfort] [VARCHAR](MAX) NULL,
[PhysicalConcerns_PainOrDiscomfort_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HotFlushesOrSweating_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HotFlushesOrSweating_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HotFlushesOrSweating] [VARCHAR](MAX) NULL,
[PhysicalConcerns_HotFlushesOrSweating_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_DryItchyOrSoreSkin_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_DryItchyOrSoreSkin_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_DryItchyOrSoreSkin] [VARCHAR](MAX) NULL,
[PhysicalConcerns_DryItchyOrSoreSkin_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_ChangesInWeight_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_ChangesInWeight_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_ChangesInWeight] [VARCHAR](MAX) NULL,
[PhysicalConcerns_ChangesInWeight_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_WoundCare_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_WoundCare_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_WoundCare] [VARCHAR](MAX) NULL,
[PhysicalConcerns_WoundCare_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MemoryOrConcentration_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MemoryOrConcentration_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MemoryOrConcentration] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MemoryOrConcentration_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SightOrHearing_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SightOrHearing_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SightOrHearing] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SightOrHearing_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SpeechOrVoiceProblems_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SpeechOrVoiceProblems_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SpeechOrVoiceProblems] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SpeechOrVoiceProblems_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MyAppearance_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MyAppearance_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MyAppearance] [VARCHAR](MAX) NULL,
[PhysicalConcerns_MyAppearance_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_1_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_1_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_1] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_1_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_1_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_1_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_1] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_1_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SleepProblems_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SleepProblems_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SleepProblems] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SleepProblems_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_2_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_2_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_2] [VARCHAR](MAX) NULL,
[PhysicalConcerns_SexIntimacyOrFertility_2_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_2_Mnemonic] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_2_SNOMED] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_2] [VARCHAR](MAX) NULL,
[PhysicalConcerns_OtherMedicalConditions_2_WithComments] [VARCHAR](MAX) NULL,
[PhysicalConcerns] [VARCHAR](MAX) NULL,
[PracticalConcerns_TakingCareOfOthers_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_TakingCareOfOthers_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_TakingCareOfOthers] [VARCHAR](MAX) NULL,
[PracticalConcerns_TakingCareOfOthers_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_WorkOrEducation_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_WorkOrEducation_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_WorkOrEducation] [VARCHAR](MAX) NULL,
[PracticalConcerns_WorkOrEducation_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_MoneyOrFinance_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_MoneyOrFinance_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_MoneyOrFinance] [VARCHAR](MAX) NULL,
[PracticalConcerns_MoneyOrFinance_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_Travel_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_Travel_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_Travel] [VARCHAR](MAX) NULL,
[PracticalConcerns_Travel_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_Housing_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_Housing_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_Housing] [VARCHAR](MAX) NULL,
[PracticalConcerns_Housing_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_TransportOrParking_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_TransportOrParking_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_TransportOrParking] [VARCHAR](MAX) NULL,
[PracticalConcerns_TransportOrParking_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_TalkingOrBeingUnderstood_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_TalkingOrBeingUnderstood_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_TalkingOrBeingUnderstood] [VARCHAR](MAX) NULL,
[PracticalConcerns_TalkingOrBeingUnderstood_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_LaundryOrHousework_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_LaundryOrHousework_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_LaundryOrHousework] [VARCHAR](MAX) NULL,
[PracticalConcerns_LaundryOrHousework_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_GroceryShopping_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_GroceryShopping_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_GroceryShopping] [VARCHAR](MAX) NULL,
[PracticalConcerns_GroceryShopping_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_WashingAndDressing_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_WashingAndDressing_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_WashingAndDressing] [VARCHAR](MAX) NULL,
[PracticalConcerns_WashingAndDressing_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_PreparingMealsOrDrinks_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_PreparingMealsOrDrinks_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_PreparingMealsOrDrinks] [VARCHAR](MAX) NULL,
[PracticalConcerns_PreparingMealsOrDrinks_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_1_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_1_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_1] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_1_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_1_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_1_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_1] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_1_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_Pets_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_Pets_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_Pets] [VARCHAR](MAX) NULL,
[PracticalConcerns_Pets_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_2_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_2_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_2] [VARCHAR](MAX) NULL,
[PracticalConcerns_DifficultyMakingPlans_2_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_1_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_1_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_1] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_1_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_1_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_1_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_1] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_1_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_2_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_2_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_2] [VARCHAR](MAX) NULL,
[PracticalConcerns_SmokingCessation_2_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_2_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_2_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_2] [VARCHAR](MAX) NULL,
[PracticalConcerns_ProblemsWithAlcoholAndDrugs_2_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_2_Mnemonic] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_2_SNOMED] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_2] [VARCHAR](MAX) NULL,
[PracticalConcerns_MyMedication_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_1_WithComments] [VARCHAR](MAX) NULL,
[PracticalConcerns] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Uncertainty_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LossOfInterestInActivities_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LossOfInterestInActivities_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LossOfInterestInActivities] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LossOfInterestInActivities_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_UnableToExpressFeelings_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_ThinkingAboutTheFuture_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_RegretAboutThePast_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_AngerOrFrustration_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_LonelinessOrIsolation_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_SadnessOrDepression_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Hopelessness_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_1_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_1_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_1] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Guilt_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_WorryFearOrAnxiety_2_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_2_Mnemonic] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_2_SNOMED] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_2] [VARCHAR](MAX) NULL,
[EmotionalConcerns_Independence_2_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_1_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_1_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_1] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_1_WithComments] [VARCHAR](MAX) NULL,
[EmotionalConcerns] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_1_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_1_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_1] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_1_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Partner_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Partner_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Partner] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Partner_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_2_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_2_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_2] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_Children_2_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_1_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_1_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_1] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_1_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_1_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_1_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_1] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_1_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_2_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_2_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_2] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_OtherRelativesOrFriends_2_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_2_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_2_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_2] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoLooksAfterMe_2_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_2_Mnemonic] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_2_SNOMED] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_2] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns_PersonWhoILookAfter_2_WithComments] [VARCHAR](MAX) NULL,
[FamilyOrRelationshipConcerns] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_1_Mnemonic] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_1_SNOMED] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_1] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_1_WithComments] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_1_Mnemonic] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_1_SNOMED] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_1] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_1_WithComments] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FaithOrSpirituality_Mnemonic] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FaithOrSpirituality_SNOMED] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FaithOrSpirituality] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FaithOrSpirituality_WithComments] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_2_Mnemonic] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_2_SNOMED] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_2] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_MeaningOrPurposeOfLife_2_WithComments] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_2_Mnemonic] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_2_SNOMED] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_2] [VARCHAR](MAX) NULL,
[SpirtitualConcerns_FeelingAtOddsWithCultureBeliefsOrValues_2_WithComments] [VARCHAR](MAX) NULL,
[SpirtitualConcerns] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_ExceriseAndActivity_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_ExceriseAndActivity_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_ExceriseAndActivity] [VARCHAR](MAX) NULL,
[InformationOrSupport_ExceriseAndActivity_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_DietAndNutrition_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_ComplementaryTherapies_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_PlanningForMyFuturePriorities_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_MakingAWillOrLegalAdvice_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_HealthAndWellbeing_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_1_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_1_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_1_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_PatientOrCarersSupportGroup_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_ManagingMySymptoms_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_1] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_2_Mnemonic] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_2_SNOMED] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_2] [VARCHAR](MAX) NULL,
[InformationOrSupport_SunProtection_2_WithComments] [VARCHAR](MAX) NULL,
[InformationOrSupport_2] [VARCHAR](MAX) NULL,
[DiagnosisTreatmentsOrEffects_IHaveQuestionsAboutMyDiagnosisTreatmentsOrEffects_1] [VARCHAR](3) NULL,
[NoConcernsIdentified__Mnemonic] [VARCHAR](MAX) NULL,
[NoConcernsIdentified__SNOMED] [VARCHAR](MAX) NULL,
[NoConcernsIdentified_] [VARCHAR](MAX) NULL,
[NoConcernsIdentified__WithComments] [VARCHAR](MAX) NULL,
[DiagnosisTreatmentsOrEffects_IHaveQuestionsAboutMyDiagnosisTreatmentsOrEffects_2] [VARCHAR](3) NULL,
[IsDeleted] [BIT] NOT NULL,
[SYSDateLastUpdated] [DATETIME] NOT NULL,
[SYSDateLoaded] [DATETIME] NOT NULL,
PRIMARY KEY CLUSTERED
(
[pkDocumentID] ASC,
[pkEditedByStaffID] ASC,
[pkEditedDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_MT_EMR_Document_Holistic_Needs_Assessment_GENEHNA] ADD DEFAULT (GETDATE()) FOR [SYSDateLastUpdated]
GO
ALTER TABLE [dbo].[tbl_MT_EMR_Document_Holistic_Needs_Assessment_GENEHNA] ADD DEFAULT (GETDATE()) FOR [SYSDateLoaded]
GO
Yes, it's a big table but you can thank our Application Specialists for that one(!)
Anyone any ideas why I'm still getting the error?
I've worked out the bytes (correctly, I hope) and I get 7058 (assuming the varchar(max) columns are using a 16-byte pointer so I'm now confused as to why the data won't fit.
Anyone any ideas or you can someone please point out the obvious I'm missing?
TIA
Richard
June 23, 2020 at 12:47 pm
Don't forget all the overhead bytes that SQL requires internally to manage the row.
You might have to move some of the values to a second table, with a 1-1 relationship to the original table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 23, 2020 at 1:29 pm
That's what I was trying to avoid but it might be necessary in this case.
Should keep things interesting with the code if nothing else as we build the tables automagically.
June 24, 2020 at 3:48 am
Have you checked seriously to see if you need a table with than many NVARCHAR(MAX) columns? If so, perhaps you're using the wrong kind of database. Perhaps a "document" database would be better for such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply