but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

Question

Hi Experts,

I am facing this issue of
"but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit."
in production enviornment. Previous developers decided to kepe the design in away to have 641 columns in this table. Moving columns and normalizin is alot more work and needs more time. But since this is a production job, I am looking for some workaround. I have 247 varchar columns and there are lots of columsn with varchar(n). Reading some posts online, is it okay to change some of the varch(n) to varchar(max). is there any other way to fix this issue wthout changing the table design. I would really appreciate any help.

Thanks,
Ro

Answer 1

Here is the table definition: USE [XM_Data3]
GO

/****** Object: Table [dbo].[CorporateAction] Script Date: 12/28/2021 11:41:32 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CorporateAction](
[CorporateActionId] [int] IDENTITY(1,1) NOT NULL,
[MasterCorporateActionId] [int] NULL,
[MasterEntityId] [int] NULL,
[CorporateActionTypeId] [int] NOT NULL,
[SourceActionId] [int] NULL,
[IsCurrent] [bit] NULL,
[BloombergCompanyId] [int] NULL,
[BloombergSecurityId] [int] NULL,
[BloombergIdentifier] varchar NOT NULL,
[Secid] varchar NULL,
[Currency] varchar NULL,
[MarketSectorDescription] varchar NULL,
[BloombergUniqueId] varchar NULL,
[AnnouncementDate] [datetime] NULL,
[EffectiveDate] [datetime] NULL,
[AmdDate] [datetime] NULL,
[CpAcquiredBy] varchar NULL,
[CpAcquisTyp] [int] NULL,
[CpAddedAmt] [numeric](20, 6) NULL,
[CpAddress] varchar NULL,
[CpAdj] [numeric](20, 6) NULL,
[CpAdjDt] [datetime] NULL,
[CpAdvisors] varchar NULL,
[CpAdvisorsNum] [int] NULL,
[CpAgent] varchar NULL,
[CpAmt] [numeric](20, 6) NULL,
[CpAmtComp] [numeric](20, 6) NULL,
[CpAmtNonComp] [numeric](20, 6) NULL,
[CpAmtTender] [numeric](20, 6) NULL,
[CpAmtTyp] [int] NULL,
[CpAmt1] [numeric](20, 6) NULL,
[CpAmt2] [numeric](20, 6) NULL,
[CpAmt3] [numeric](20, 6) NULL,
[CpArbitrageProf] [numeric](20, 6) NULL,
[CpBancrTyp] [int] NULL,
[CpCash] [numeric](20, 6) NULL,
[CpCashFlag] [int] NULL,
[CpCashVal] [numeric](20, 6) NULL,
[CpCompletedDt] [datetime] NULL,
[CpConvDt] [datetime] NULL,
[CpConvPxReason] [int] NULL,
[CpCost] [numeric](20, 6) NULL,
[CpCounterParty] varchar NULL,
[CpCpnTyp] [int] NULL,
[CpCrncy] varchar NULL,
[CpCurPremium] [numeric](20, 6) NULL,
[CpCurTotVal] [numeric](20, 6) NULL,
[CpDbtRdmpCallReason] [int] NULL,
[CpDbtRdmpCallTyp] [int] NULL,
[CpDbtRdmpPutTyp] [int] NULL,
[CpDebt] [numeric](20, 6) NULL,
[CpDebtFlag] [int] NULL,
[CpDeleteReason] [int] NULL,
[CpDelistReason] [int] NULL,
[CpDespatchDt] [datetime] NULL,
[CpDetailFlag] [int] NULL,
[CpDtFlag] [int] NULL,
[CpDueBillRedDt] [datetime] NULL,
[CpDvdCrncy] varchar NULL,
[CpDvdStockTyp] [int] NULL,
[CpDvdTyp] [int] NULL,
[CpEqyOfferTyp] [int] NULL,
[CpExch] varchar NULL,
[CpExtenIssReason] [int] NULL,
[CpFactor] [numeric](20, 6) NULL,
[CpFilingAmtOffer] [numeric](20, 6) NULL,
[CpFilingShOffer] [numeric](20, 6) NULL,
[CpFinalDt] [datetime] NULL,
[CpFlag] [int] NULL,
[CpForeignAmt] [numeric](20, 6) NULL,
[CpFrankedAmt] [numeric](20, 6) NULL,
[CpFreq] [int] NULL,
[CpIdBbComp] [int] NULL,
[CpIdBbSec] [int] NULL,
[CpIndicator] varchar NULL,
[CpInitAmt] [numeric](20, 6) NULL,
[CpInitOfferPremium] [numeric](20, 6) NULL,
[CpInstallAmt] [numeric](20, 6) NULL,
[CpInstallDt] [datetime] NULL,
[CpInstallTyp] [int] NULL,
[CpIntlAmtOffer] [numeric](20, 6) NULL,
[CpIntlShOffer] [numeric](20, 6) NULL,
[CpMarketSectorDes] varchar NULL,
[CpMinBlock] [int] NULL,
[CpMeetTyp] [int] NULL,
[CpMtyDt] [datetime] NULL,
[CpNewBelgian] varchar NULL,
[CpNewClass] [int] NULL,
[CpNewCommon] varchar NULL,
[CpNewCountry] varchar NULL,
[CpNewCrncy] varchar NULL,
[CpNewCusip] varchar NULL,
[CpNewExch] varchar NULL,
[CpNewExchRt] [numeric](20, 6) NULL,
[CpNewFonds] varchar NULL,
[CpNewIsin] varchar NULL,
[CpNewLot] [numeric](20, 6) NULL,
[CpNewName] varchar NULL,
[CpNewParVal] [numeric](20, 6) NULL,
[CpNewPx] [numeric](20, 6) NULL,
[CpNewRatio] [numeric](20, 6) NULL,
[CpNewSedol] varchar NULL,
[CpNewSicovam] varchar NULL,
[CpNewWpk] varchar NULL,
[CpNotes] varchar NULL,
[CpNxtMtyDt] [datetime] NULL,
[CpOffer] [numeric](20, 6) NULL,
[CpOldBelgian] varchar NULL,
[CpOldClass] [int] NULL,
[CpOldCommon] varchar NULL,
[CpOldCountry] varchar NULL,
[CpOldCrncy] varchar NULL,
[CpOldCusip] varchar NULL,
[CpOldExch] varchar NULL,
[CpOldExchRt] [numeric](20, 6) NULL,
[CpOldFonds] varchar NULL,
[CpOldIsin] varchar NULL,
[CpOldLot] [numeric](20, 6) NULL,
[CpOldName] varchar NULL,
[CpOldParVal] [numeric](20, 6) NULL,
[CpOldPx] [numeric](20, 6) NULL,
[CpOldRatio] [numeric](20, 6) NULL,
[CpOldSedol] varchar NULL,
[CpOldSicovam] varchar NULL,
[CpOldTkr] varchar NULL,
[CpOldWpk] varchar NULL,
[CpOptNum] [numeric](20, 6) NULL,
[CpOptPx] [numeric](20, 6) NULL,
[CpOptTkr] varchar NULL,
[CpOptlAmt] [numeric](20, 6) NULL,
[CpParPct] [numeric](20, 6) NULL,
[CpPayDt] [datetime] NULL,
[CpPctOwned] [numeric](20, 6) NULL,
[CpPctSought] [numeric](20, 6) NULL,
[CpPostEuroAmtOutstanding] [numeric](20, 6) NULL,
[CpPostEuroCalendarCode] varchar NULL,
[CpPostEuroIdIsin] varchar NULL,
[CpPostEuroMinIncrement] [numeric](20, 6) NULL,
[CpPostEuroMinPiece] [numeric](20, 6) NULL,
[CpPostEuroParAmt] [numeric](20, 6) NULL,
[CpPreEuroAmtOutstanding] [numeric](20, 6) NULL,
[CpPreEuroCalendarCode] varchar NULL,
[CpPreEuroCrncy] varchar NULL,
[CpPreEuroMinIncrement] [numeric](20, 6) NULL,
[CpPreEuroMinPiece] [numeric](20, 6) NULL,
[CpPreEuroParAmt] [numeric](20, 6) NULL,
[CpPricingAmtOffer] [numeric](20, 6) NULL,
[CpPricingShOffer] [numeric](20, 6) NULL,
[CpProvisional] [int] NULL,
[CpPrvMtyDt] [datetime] NULL,
[CpPx] [numeric](20, 6) NULL,
[CpRatio] [numeric](20, 6) NULL,
[CpReclassTyp] [int] NULL,
[CpRecordDt] [datetime] NULL,
[CpRedenomMethod] [int] NULL,
[CpRedenomRoundMethod] [int] NULL,
[CpReinvestRatio] [numeric](20, 6) NULL,
[CpRightsOfferTyp] [int] NULL,
[CpSchAmt] [numeric](20, 6) NULL,
[CpSecTyp] [int] NULL,
[CpSecid] varchar NULL,
[CpSecurityTyp] [int] NULL,
[CpSettleDt] [datetime] NULL,
[CpSh] [numeric](20, 6) NULL,
[CpShFlag] [int] NULL,
[CpShFractional] [int] NULL,
[CpShNum] [numeric](20, 6) NULL,
[CpSpplAmt] [numeric](20, 6) NULL,
[CpStat] [int] NULL,
[CpStockBuyTyp] [int] NULL,
[CpStockSpltTyp] [int] NULL,
[CpSubStartDt] [datetime] NULL,
[CpSubEndDt] [datetime] NULL,
[CpTaxAmt] [numeric](20, 6) NULL,
[CpTaxRt] [numeric](20, 6) NULL,
[CpTenderTyp] [int] NULL,
[CpTerms] varchar NULL,
[CpTermsFlag] [int] NULL,
[CpTkr] varchar NULL,
[CpTkr1] varchar NULL,
[CpTkr2] varchar NULL,
[CpTkr3] varchar NULL,
[CpTotAmt] [numeric](20, 6) NULL,
[CpTradeStartDt] [datetime] NULL,
[CpTradeEndDt] [datetime] NULL,
[CpUndlCrncy] varchar NULL,
[CpUnit] varchar NULL,
[CpUnsolicited] [int] NULL,
[CpValPurchased] [numeric](20, 6) NULL,
[IdBbGlobal] varchar NULL,
[IdBbGlobalCompany] varchar NULL,
[IdBbSecNumDes] varchar NULL,
[FeedSource] varchar NULL,
[CpAcqTkr] varchar NULL,
[CpAcqIdBbGlobal] varchar NULL,
[CpAcqIdBbGlobalCompany] varchar NULL,
[CpAcqIdBbSecNumDes] varchar NULL,
[CpAcqFeedSource] varchar NULL,
[CpTargetTkr] varchar NULL,
[CpTargetIdBbGlobal] varchar NULL,
[CpTargetIdBbGlobalCompany] varchar NULL,
[CpTargetIdBbSecNumDes] varchar NULL,
[CpTargetFeedSource] varchar NULL,
[CpSellerTkr] varchar NULL,
[CpSellerIdBbGlobal] varchar NULL,
[CpSellerIdBbGlobalCompany] varchar NULL,
[CpSellerIdBbSecNumDes] varchar NULL,
[CpSellerFeedSource] varchar NULL,
[CpExchIdBbGlobal] varchar NULL,
[CpExchIdBbGlobalCompany] varchar NULL,
[CpExchIdBbSecNumDes] varchar NULL,
[CpExchFeedSource] varchar NULL,
[CpTkr1IdBbGlobal] varchar NULL,
[CpTkr1IdBbGlobalCompany] varchar NULL,
[CpTkr1IdBbSecNumDes] varchar NULL,
[CpTkr1FeedSource] varchar NULL,
[CpTkr2IdBbGlobal] varchar NULL,
[CpTkr2IdBbGlobalCompany] varchar NULL,
[CpTkr2IdBbSecNumDes] varchar NULL,
[CpTkr2FeedSource] varchar NULL,
[CpTkr3IdBbGlobal] varchar NULL,
[CpTkr3IdBbGlobalCompany] varchar NULL,
[CpTkr3IdBbSecNumDes] varchar NULL,
[CpTkr3FeedSource] varchar NULL,
[CpSpinoffIdBbGlobal] varchar NULL,
[CpSpinoffIdBbGlobalCompany] varchar NULL,
[CpSpinoffIdBbSecNumDes] varchar NULL,
[CpSpinoffFeedSource] varchar NULL,
[SourceRegionId] [int] NULL,
[ValidFromDate] [date] NOT NULL,
[ValidToDate] [date] NULL,
[LoadDate] [date] NOT NULL,
[LoadedBy] [tinyint] NOT NULL,
[UpdateDate] [date] NULL,
[UpdatedBy] [tinyint] NULL,
[CorpActionHash] varchar NULL,
[AdvisersList] varchar NULL,
[AmendedTerms] varchar NULL,
[AnnouncementMarketHours] varchar NULL,
[BookBuildingPeriod] varchar NULL,
[CapitalPercentage] [numeric](20, 6) NULL,
[CaAnnounceDt] [datetime] NULL,
[CaDealCurrency] varchar NULL,
[CaMaAccountingMethod] varchar NULL,
[CaMaAcquirerCountry] varchar NULL,
[CaMaAcquirerFinancialAdviser] varchar NULL,
[CaMaAcquirerIndustry] varchar NULL,
[CaMaAcquirerLegalAdviser] varchar NULL,
[CaMaAcquirerName] varchar NULL,
[CaMaAcquirerPeopleLink] varchar NULL,
[CaMaAcquirerTicker] varchar NULL,
[CaMaAcqFairnessOpinionAdvis] varchar NULL,
[CaMaAcqOwnershipInNewCoPct] [numeric](20, 2) NULL,
[CaMaAcqToTargetTermFees] [numeric](20, 2) NULL,
[CaMaAdditionalAcqAdvisers] varchar NULL,
[CaMaAdditionalSellerAdvisers] varchar NULL,
[CaMaAdditionalTargetAdvisers] varchar NULL,
[CaMaAmendmentDt] [datetime] NULL,
[CaMaAnnounceValue] [numeric](20, 6) NULL,
[CaMaApprovedApprovals] varchar NULL,
[CaMaBlockedApprovals] varchar NULL,
[CaMaCashTerms] varchar NULL,
[CaMaCashValue] [numeric](20, 6) NULL,
[CaMaCompleteDt] [datetime] NULL,
[CaMaContingencyPayments] char NULL,
[CaMaContingencyPaymentsAmt] [numeric](12, 2) NULL,
[CaMaCurPremium] [numeric](20, 6) NULL,
[CaMaDealDescription] varchar NULL,
[CaMaDealPurpose] varchar NULL,
[CaMaDealStatus] varchar NULL,
[CaMaDealType] varchar NULL,
[CaMaDebtAssumption] [numeric](20, 2) NULL,
[CaMaDebtIssuance] [numeric](20, 2) NULL,
[CaMaDissenterCutOffDate] [datetime] NULL,
[CaMaDissenterRights] char NULL,
[CaMaDropDeadDate] [datetime] NULL,
[CaMaEarlyTermApprovals] varchar NULL,
[CaMaElectionExpirationDate] [datetime] NULL,
[CaMaExchange] char NULL,
[CaMaExercisableOptions] [numeric](20, 3) NULL,
[CaMaExpectedCompletionDate] [datetime] NULL,
[CaMaExpirationTime] varchar NULL,
[CaMaExpirationTimeZone] varchar NULL,
[CaMaExpiredApprovals] varchar NULL,
[CaMaExtendedApprovals] varchar NULL,
[CaMaFinancingCondition] char NULL,
[CaMaFractionalShares] varchar NULL,
[CaMaGoverningLawState] varchar NULL,
[CaMaGoShopPeriodDays] [int] NULL,
[CaMaGoShopPeriodEndDate] [datetime] NULL,
[CaMaGoShopPeriodStartDate] [datetime] NULL,
[CaMaLegalAdviserToAcqFinl] varchar NULL,
[CaMaLetterOfTransmittal] char NULL,
[CaMaLglAdviserToSellerFinl] varchar NULL,
[CaMaLglAdviserToTargetFinl] varchar NULL,
[CaMaMaterialAdverseEffCond] char NULL,
[CaMaMergersAgreementDate] [datetime] NULL,
[CaMaNatureOfBid] varchar NULL,
[CaMaNetDebt] [numeric](20, 6) NULL,
[CaMaNonEquityPayments] char NULL,
[CaMaOptionsExValue] [numeric](20, 2) NULL,
[CaMaPaymentTyp] varchar NULL,
[CaMaPctOwned] [numeric](8, 4) NULL,
[CaMaPctSought] [numeric](8, 4) NULL,
[CaMaPendingApprovals] varchar NULL,
[CaMaPremium] [numeric](20, 6) NULL,
[CaMaProposedDealDate] [datetime] NULL,
[CaMaProposedDealType] varchar NULL,
[CaMaProposedInfoSource] varchar NULL,
[CaMaSellerCountry] varchar NULL,
[CaMaSellerFairnessOpinionAd] varchar NULL,
[CaMaSellerFinancialAdviser] varchar NULL,
[CaMaSellerIndustry] varchar NULL,
[CaMaSellerLegalAdviser] varchar NULL,
[CaMaSellerName] varchar NULL,
[CaMaSellerPeopleLink] varchar NULL,
[CaMaSellerTicker] varchar NULL,
[CaMaSpecialStockPayment] varchar NULL,
[CaMaSpecialStockTicker] varchar NULL,
[CaMaStateSection1] varchar NULL,
[CaMaStateSection2] varchar NULL,
[CaMaStockTerms] varchar NULL,
[CaMaTargetCountry] varchar NULL,
[CaMaTargetFinancialAdviser] varchar NULL,
[CaMaTargetIndustry] varchar NULL,
[CaMaTargetLegalAdviser] varchar NULL,
[CaMaTargetName] varchar NULL,
[CaMaTargetOwnerInNewCoPct] [numeric](20, 2) NULL,
[CaMaTargetPeopleLink] varchar NULL,
[CaMaTargetTicker] varchar NULL,
[CaMaTargetToAcqTermFees] [numeric](20, 2) NULL,
[CaMaTaxable] char NULL,
[CaMaTgtFairnessOpinionAdvis] varchar NULL,
[CaMaTotalVal] [numeric](20, 6) NULL,
[CaTransactionType] varchar NULL,
[CntryOfIncorporation] varchar NULL,
[CompanyAddress] varchar NULL,
[CompanyFaxNumber] varchar NULL,
[CompanyTelNumber] varchar NULL,
[CpAcquirerConsortium] varchar NULL,
[CpActionStatus] varchar NULL,
[CpAgentRights] char NULL,
[CpAttendanceBonus] [numeric](12, 2) NULL,
[CpBancrReqShCash] [numeric](10, 2) NULL,
[CpBancrReqShDebt] [numeric](10, 2) NULL,
[CpBancrReqShStock] [numeric](10, 2) NULL,
[CpBancrReqShWrt] [numeric](10, 2) NULL,
[CpBancrStatus] [int] NULL,
[CpBankConv] char NULL,
[CpBlueSky] char NULL,
[CpBreakdwnLetter] char NULL,
[CpBrokerProvision] char NULL,
[CpBuybackTyp] [int] NULL,
[CpCase] varchar NULL,
[CpCashAmt] [numeric](20, 6) NULL,
[CpCashInLieu] [numeric](20, 6) NULL,
[CpCashProrationCashFlag] [int] NULL,
[CpCashProrationCashVal] [numeric](20, 6) NULL,
[CpCashProrationPayTyp] [int] NULL,
[CpCashProrationStockFlag] [int] NULL,
[CpCashProrationStockVal] [numeric](20, 6) NULL,
[CpChgIdReason] [int] NULL,
[CpChgWrtReason] [int] NULL,
[CpClassClaim] [numeric](20, 6) NULL,
[CpCollar] varchar NULL,
[CpCompanyAsset] [numeric](12, 2) NULL,
[CpCompanyLiab] [numeric](12, 2) NULL,
[CpCompBidDt] [datetime] NULL,
[CpCompBidPremium] [numeric](20, 4) NULL,
[CpCompDt] [datetime] NULL,
[CpContingPay] char NULL,
[CpDbtAmt] [numeric](20, 6) NULL,
[CpDbtIdBbComp] [int] NULL,
[CpDbtIdBbSec] [int] NULL,
[CpDealerAgree] char NULL,
[CpDefaultCashFlag] [int] NULL,
[CpDefaultDbtFlag] [int] NULL,
[CpDefaultStockFlag] [int] NULL,
[CpDepositary] [int] NULL,
[CpDepositPx] [numeric](20, 6) NULL,
[CpDipAmt] [numeric](20, 6) NULL,
[CpDipFinancier] [int] NULL,
[CpDissent] char NULL,
[CpDissentDt] [datetime] NULL,
[CpDissentState1] varchar NULL,
[CpDissentState2] varchar NULL,
[CpDistCourt] varchar NULL,
[CpDutchAuctionMaxPx] [numeric](20, 6) NULL,
[CpDutchAuctionMinPx] [numeric](20, 6) NULL,
[CpElectionDt] [datetime] NULL,
[CpElectExpDt] [datetime] NULL,
[CpExchCerts] char NULL,
[CpExpireTime] varchar NULL,
[CpExpireTimeZone] [int] NULL,
[CpExpireWorthless] char NULL,
[CpExpPricingMsg] varchar NULL,
[CpExDt] [datetime] NULL,
[CpFeeMgr] [numeric](33, 4) NULL,
[CpFeeReallowance] [numeric](33, 4) NULL,
[CpFeeSellingConcessn] [numeric](33, 4) NULL,
[CpFeeTotalGross] [numeric](33, 4) NULL,
[CpFeeUnderwriter] [numeric](33, 4) NULL,
[CpFilingDt] [datetime] NULL,
[CpFundamentalSharesAdjustOp] varchar NULL,
[CpFungIssue] varchar NULL,
[CpFutDist] char NULL,
[CpHearingTyp] varchar NULL,
[CpInfoSource] [int] NULL,
[CpIntlSh] [numeric](33, 2) NULL,
[CpIssuanceFee] [numeric](20, 6) NULL,
[CpLetterTransmittal] char NULL,
[CpLockupDays] [int] NULL,
[CpLockupShrs] [numeric](33, 2) NULL,
[CpMandOptStatus] varchar NULL,
[CpMaxFee] [numeric](20, 6) NULL,
[CpMaxFilingPx] [numeric](33, 2) NULL,
[CpMeetConf] varchar NULL,
[CpMeetExDate] [datetime] NULL,
[CpMeetLocation] varchar NULL,
[CpMinFilingPx] [numeric](33, 2) NULL,
[CpNewExpireDt] [datetime] NULL,
[CpNewFiscalEndDt] [datetime] NULL,
[CpNewIrishSedol] varchar NULL,
[CpNewSection] varchar NULL,
[CpNewSingaporeId] varchar NULL,
[CpNewStrike] [numeric](20, 6) NULL,
[CpNewTicker] varchar NULL,
[CpNewTime] varchar NULL,
[CpNewTimeZone] [int] NULL,
[CpNewTradeStatus] [int] NULL,
[CpNewUndlIdBbComp] [int] NULL,
[CpNewUndlIdBbSec] [int] NULL,
[CpNonEqyPay] char NULL,
[CpNoActionPayTyp] [int] NULL,
[CpNoActionPayTypCashFlag] [int] NULL,
[CpNoActionPayTypCashVal] [numeric](20, 6) NULL,
[CpNoActionPayTypStockFlag] [int] NULL,
[CpNoActionPayTypStockVal] [numeric](20, 6) NULL,
[CpNumSubscribeRights] [int] NULL,
[CpObjectiveNew] [int] NULL,
[CpObjectivePrev] [int] NULL,
[CpOddLot] char NULL,
[CpOfferPx] [numeric](33, 4) NULL,
[CpOldExpireDt] [datetime] NULL,
[CpOldIrishSedol] varchar NULL,
[CpOldSection] varchar NULL,
[CpOldSingaporeId] varchar NULL,
[CpOldStrike] [numeric](20, 6) NULL,
[CpOldTicker] varchar NULL,
[CpOldTime] varchar NULL,
[CpOldTimeZone] [int] NULL,
[CpOldUndlIdBbComp] [int] NULL,
[CpOldUndlIdBbSec] [int] NULL,
[CpOverallotment] [numeric](33, 2) NULL,
[CpOverSub] char NULL,
[CpPayingAgentId] [int] NULL,
[CpPerShrFundamentalAdjOp] varchar NULL,
[CpPetitionTyp] [int] NULL,
[CpPhysForm] char NULL,
[CpPoisonPill] [numeric](20, 6) NULL,
[CpPrevFiscalEndDt] [datetime] NULL,
[CpPriceAdjustmentOp] varchar NULL,
[CpProcessingFee] [numeric](20, 6) NULL,
[CpProgramMgr] [int] NULL,
[CpProposedSettleEquity] [int] NULL,
[CpProration] [numeric](20, 6) NULL,
[CpProrationCash] [numeric](33, 2) NULL,
[CpProrationStock] [numeric](20, 6) NULL,
[CpPxDt] [datetime] NULL,
[CpQuietPeriod] [int] NULL,
[CpRegCourt] [int] NULL,
[CpReorgAge] varchar NULL,
[CpReorgConfirmDt] [datetime] NULL,
[CpReorgPlan] varchar NULL,
[CpReorgPlanDt] [datetime] NULL,
[CpRights1FeedSource] varchar NULL,
[CpRights1IdBbGlobal] varchar NULL,
[CpRights1IdBbGlobalCompany] varchar NULL,
[CpRights1IdBbSecNumDes] varchar NULL,
[CpRightsAmtCcy] varchar NULL,
[CpRightsAmtRaised] [numeric](20, 2) NULL,
[CpRightsPerShare] [numeric](20, 6) NULL,
[CpRightsShares] [numeric](20, 4) NULL,
[CpRightsTkr1] varchar NULL,
[CpRightsTkr2] varchar NULL,
[CpSecondSh] [numeric](33, 4) NULL,
[CpSecFilingDt] [datetime] NULL,
[CpSecFilingTyp] [int] NULL,
[CpSellerConsortium] varchar NULL,
[CpSelling] char NULL,
[CpSeqNumber] [int] NULL,
[CpSettle] [int] NULL,
[CpSettleTyp] [int] NULL,
[CpSettleVal] [numeric](20, 6) NULL,
[CpSharesOutAfterOffer] [numeric](33, 2) NULL,
[CpSharesPerRight] [numeric](20, 6) NULL,
[CpSpinoffExchCode] varchar NULL,
[CpSpinoffTicker] varchar NULL,
[CpSpinoffTyp] [int] NULL,
[CpSpltNilPaidDt] [datetime] NULL,
[CpState1Law] varchar NULL,
[CpState2Law] varchar NULL,
[CpStockAmt] [numeric](20, 6) NULL,
[CpStockIdBbComp] [int] NULL,
[CpStockIdBbSec] [int] NULL,
[CpStockProrationCashFlag] [int] NULL,
[CpStockProrationCashVal] [numeric](20, 6) NULL,
[CpStockProrationPayTyp] [int] NULL,
[CpStockProrationStockFlag] [int] NULL,
[CpStockProrationStockVal] [numeric](20, 6) NULL,
[CpTargetConsortium] varchar NULL,
[CpTaxable] char NULL,
[CpTertiaryFilingDt] [datetime] NULL,
[CpTertiaryFilingTyp] [int] NULL,
[CpTime] varchar NULL,
[CpTimeZone] [int] NULL,
[CpTkrChgReason] [int] NULL,
[CpTradeStatusCoExch] [int] NULL,
[CpTransPeriodEndDt] [datetime] NULL,
[CpTransPeriodStartDt] [datetime] NULL,
[CpTrusteeId] [int] NULL,
[CpUnitBbids] varchar NULL,
[CpVolumeAdjustmentOp] varchar NULL,
[CpVoteDt] [datetime] NULL,
[CpWrtAmt] [numeric](20, 6) NULL,
[CpWrtFlag] [int] NULL,
[CpWrtIdBbComp] [int] NULL,
[CpWrtIdBbSec] [int] NULL,
[DealDescription] varchar NULL,
[DealNtes] varchar NULL,
[DealValueFilingTerm] [numeric](20, 6) NULL,
[DealValueFinalPostShoe] [numeric](20, 6) NULL,
[DealValueFinalPreShoe] [numeric](20, 6) NULL,
[EndSubscriptionPeriod] [datetime] NULL,
[EqySplitAdjInitPoPx] [numeric](20, 6) NULL,
[EqySplitRatio] varchar NULL,
[ExchCode] varchar NULL,
[FeeAccounting] [numeric](20, 6) NULL,
[FeeBlueSky] [numeric](20, 6) NULL,
[FeeContingentFeePerShare] [numeric](20, 6) NULL,
[FeeFinra] [numeric](20, 6) NULL,
[FeeGrossSpreadPercentage] [numeric](20, 6) NULL,
[FeeLegal] [numeric](20, 6) NULL,
[FeeMarketListing] [numeric](20, 6) NULL,
[FeeNasdaqFiling] [numeric](20, 6) NULL,
[FeePrinting] [numeric](20, 6) NULL,
[FeeSecRegistration] [numeric](20, 6) NULL,
[FeeTransferRegistrar] [numeric](20, 6) NULL,
[FundIncentiveFee] [numeric](8, 3) NULL,
[GreenshoeExpirationDate] [datetime] NULL,
[GreenshoeFacility] [numeric](20, 6) NULL,
[GreenshoeFacilityPercentage] [numeric](20, 6) NULL,
[GreenshoePrimaryShares] [numeric](20, 6) NULL,
[GreenshoePrimSharesExercised] [numeric](20, 6) NULL,
[GreenshoeSecondaryShares] [numeric](20, 6) NULL,
[GreenshoeSecShsExercised] [numeric](20, 6) NULL,
[InitialPriceRange] varchar NULL,
[InstitutionalSubscribedRatio] [numeric](20, 6) NULL,
[InternationalShares] [numeric](20, 6) NULL,
[LaunchDate] [datetime] NULL,
[LaunchMarketHours] varchar NULL,
[ListingDate] [datetime] NULL,
[LockupDays] varchar NULL,
[LockupNotes] varchar NULL,
[LockUpAmounts] varchar NULL,
[LockUpDates] varchar NULL,
[MarketCapitalization] [numeric](30, 6) NULL,
[NamesOfAdvisers] varchar NULL,
[NameOfUnderwriters] varchar NULL,
[NumSharesOffrdFilingTerm] [numeric](20, 6) NULL,
[NumShareOffrdFinalPostShoe] [numeric](20, 6) NULL,
[NumShareOffrdFinalPreShoe] [numeric](20, 6) NULL,
[OfferingGreenshoeExDate] [datetime] NULL,
[OfferingGreenshoeSharesEx] [numeric](20, 6) NULL,
[OfferingGrossSpread] [numeric](20, 6) NULL,
[OfferingOfferCurrency] varchar NULL,
[OfferingPlacingPrice] [numeric](20, 6) NULL,
[OfferingPlacingShares] [numeric](20, 4) NULL,
[OfferingSecurityType] varchar NULL,
[OfferingStage] varchar NULL,
[OfferingSubscriptionRatio] [numeric](12, 3) NULL,
[OfferingTypeIpoOrAdditional] varchar NULL,
[OtherShares] [numeric](20, 6) NULL,
[PctGreenshoePrimExercised] [numeric](20, 6) NULL,
[PctGreenshoeSecondaryExercised] [numeric](20, 6) NULL,
[PctSharesSoldByAdvisers] varchar NULL,
[PctSharesSoldByUnderwriters] varchar NULL,
[PercentageGreenshoePrimary] [numeric](20, 6) NULL,
[PercentageGreenshoeSecondary] [numeric](20, 6) NULL,
[PlacingSubscriptionFreezeAmt] [numeric](20, 6) NULL,
[PlacingValidSubscriptionShare] [numeric](20, 6) NULL,
[PricingMarketHours] varchar NULL,
[PrimaryExchangeName] varchar NULL,
[PrimaryShares] [numeric](20, 6) NULL,
[QuantitySharesSoldByAdvisers] varchar NULL,
[QuietPeriodEnd] [datetime] NULL,
[RegistrationAmount] [numeric](20, 6) NULL,
[RetailShares] [numeric](20, 6) NULL,
[RetailSubscribedRatio] [numeric](20, 6) NULL,
[RetailSubscriptionFreezeAmt] [numeric](20, 6) NULL,
[RetailValidSubscriptionShare] [numeric](20, 6) NULL,
[RolesOfAdvisers] varchar NULL,
[RolesOfUnderwriters] varchar NULL,
[SecurityName] varchar NULL,
[SettleDt] [datetime] NULL,
[SharesOutBeforeOffering] [numeric](20, 6) NULL,
[SharesSoldByUnderwriters] varchar NULL,
[ShortNamesOfAdvisers] varchar NULL,
[ShortNamesOfUnderwriters] varchar NULL,
[StabilizationPeriodEndDate] [datetime] NULL,
[StabilizationPeriodStartDate] [datetime] NULL,
[StartSubscriptionPeriod] [datetime] NULL,
[StateOfIncorporation] varchar NULL,
[Ticker] varchar NULL,
[UnderwritersList] varchar NULL,
[UnderwriterPrice] [numeric](20, 6) NULL,
[UseOfProceed] varchar NULL,
[CpTargetIdBbComp] [int] NULL,
[CpAcquirerIdBbComp] [int] NULL,
[CpSellerIdBbComp] [int] NULL,
[CpCpn] [numeric](20, 6) NULL,
[CpEusdTid] [numeric](20, 6) NULL,
[CpEusdTidSw] [numeric](20, 6) NULL,
[CpGrossAmt] [numeric](26, 12) NULL,
[CpNetAmt] [numeric](26, 12) NULL,
[CpDistAmtStatus] char NULL,
[CpNewState] varchar NULL,
[CpOldState] varchar NULL,
[CpNewTkr] varchar NULL,
[CpPariPasu] char NULL,
[CpOldValor] varchar NULL,
[CpNewValor] varchar NULL,
[CpPartPaid] varchar NULL,
[CpPostDayCntdes] varchar NULL,
[CpPreDayCntdes] varchar NULL,
[CpRenounceable] char NULL,
[CpSecidTyp] char NULL,
[CpStillExtend] char NULL,
[CpStockOpt] varchar NULL,
[CpIntl] char NULL,
[CpOptional] char NULL,
[CpTermFlag] char NULL,
[CpTotAmtCrncy] varchar NULL,
[CpDvdDrpDiscount] [numeric](20, 6) NULL,
[CpName] varchar NULL,
[CpSpinoffName] varchar NULL,
[SecidType] varchar NULL,
CONSTRAINT [PKCorporateAction_CorporateActionId] PRIMARY KEY CLUSTERED
(
[CorporateActionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[CorporateAction] WITH NOCHECK ADD CONSTRAINT [FKCorporateAction_CorpActionType] FOREIGN KEY([CorporateActionTypeId])
REFERENCES [dbo].[CorporateActionType] ([CorporateActionTypeId])
GO

ALTER TABLE [dbo].[CorporateAction] CHECK CONSTRAINT [FKCorporateAction_CorpActionType]
GO

ALTER TABLE [dbo].[CorporateAction] WITH CHECK ADD CONSTRAINT [FKCorporateAction_MasterCorpActionId] FOREIGN KEY([MasterCorporateActionId])
REFERENCES [dbo].[CorporateAction] ([CorporateActionId])
GO

ALTER TABLE [dbo].[CorporateAction] CHECK CONSTRAINT [FKCorporateAction_MasterCorpActionId]
GO

ALTER TABLE [dbo].[CorporateAction] WITH NOCHECK ADD CONSTRAINT [FKCorporateAction_MasterEntityId] FOREIGN KEY([MasterEntityId])
REFERENCES [dbo].[Entity] ([EntityId])
GO

ALTER TABLE [dbo].[CorporateAction] CHECK CONSTRAINT [FKCorporateAction_MasterEntityId]
GO

Answer 2

There is no "work around". SQL Server uses 8K pages for rows. Therefore, a single data row can never exceed 8060.

This would only come into play if the combination of variable length fields (varchar), plus the fixed size fields exceed 8060 bytes.

Answer 3

it is some varchar(n) column but it is difficult to find. Does changing varchar(n) to varchar(max) temporary fixes this problem. Does 8060 rule not apply to varchar(max)?

Answer 4

The error message when I run your monster CREATE TABLE I get is slightly different:

Warning: The maximum length of the row exceeds the permissible limit of 8060 bytes. For some combination of large values, the insert/update operation will fail.

That is, there is some leeway, because SQL Server can put data on overflow pages. I don't recall exactly under which circumstances, but with all these varchar columns, I think there are decent chances that there will be no accidents.

Yes, the table is horrible. The total max size of columns is more than 23000 bytes! Over 600 columns and only six that are not nullable! What a junkyard. How can a column called QuantitySharesSoldByAdvisers be a varchar(300)? Or is that a comma-separated list of something? There are certainly columns in the table which seems to be something like. Bad, bad, bad table design.

Anyway, I don't think changing columns to varchar(MAX) is a good idea, save for a single varchar(5000).

A better alternative may be to split the table into several, and the create a view with the same name as the current table. You would also create an INSTEAD OF trigger on the view to insert into 4-5 tables.

Answer 5

Agreed it is a bad design. I am not getting error creating this table. I get this error when I try to update/insert in this table. I have the file data in stage table and I did a count and none of the row in stage table exceeds 8000 bytes but error happends when we insert from stage to main table.