3

In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message:

Warning: The table "CustomerTransactions" has been created, 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.

I'm aware of the 8K limit, I'm stumped however, because the table does NOT exceed 8K.

The DDL and breakdowns are pasted below. Not sure where to look further. Any suggestion?

ALTER Table:

            ALTER TABLE [dbo].[CustomerTransactions] 
            ADD 
                NumericDate FLOAT,
                FirstDayOfWeek DATE,
                LastDayOfWeek DATE,
                NFirstDayOfWeek FLOAT,
                NLastDayOfWeek FLOAT,
                FirstDayOfMonth DATE,
                LastDayOfMonth DATE,
                NFirstDayOfMonth FLOAT,
                NLastDayOfMonth FLOAT,
                HalfMonthStart DATE,
                HalfMonthEnd DATE,
                NHalfMonthStart FLOAT,
                NHalfMonthEnd FLOAT,
                HalfOfMonth INT,
                FirstDayOfQuarter DATE,
                LastDayOfQuarter DATE,
                NFirstDayOfQuarter FLOAT,
                NLastDayOfQuarter FLOAT,
                FirstDayOfYear DATE,
                LastDayOfYear DATE,
                NFirstDayOfYear FLOAT,
                NLastDayOfYear FLOAT

syscolumns total

select count(*) NumCols, SUM(sc.length) SumLength
from syscolumns sc  
inner join systypes st on sc.xtype = st.xtype  
where id = object_id('CustomerTransactions')

NumCols SumLength
------- ---------
64      4463

syscolumns breakdown

select sc.name, st.name, sc.length
from syscolumns sc
left join systypes st
on sc.xtype = st.xtype
where id = object_id('CustomerTransactions')

name                      name                  length
------------------------  --------------------  -------
RowType                   varchar                   40
ReportCategory            varchar                   255
Date                      date                      3
TxnId                     varchar                   26
TxnType                   varchar                   255
AccountId                 varchar                   36
AccountType               varchar                   21
Account                   varchar                   255
AccountNameLong           varchar                   159
Amount                    decimal                   9
Reference                 varchar                   255
AmountPastDue             decimal                   9
AmountDue                 decimal                   9
Rep                       varchar                   255
RepRegion                 varchar                   255
RepLevel1                 varchar                   255
Memo                      varchar                   -1
Phone                     varchar                   20
CustomerName              varchar                   255
CustomerAccountNumber     varchar                   255
PaidStatus                varchar                   255
LastPaymentDate           date                      3
LastPaymentType           varchar                   255
LastPaymentAmount         decimal                   9
ReferenceNumber           varchar                   20
Referal                   varchar                   255
ReferalNotes              varchar                   255
PurchaseNumber            int                       4
TotalPurchase             decimal                   9
AccountHierarchyL1        varchar                   50
AccountHierarchyL2        varchar                   50
AccountHierarchyL3        varchar                   50
AccountHierarchyL4        varchar                   50
AccountHierarchyL5        varchar                   50
AccountOtherDisplayValue  varchar                   50
ReportHeader1             varchar                   50
ReportHeader2             varchar                   50
ReportHeader3             varchar                   50
ReportHeader4             varchar                   50
ReportHeader5             varchar                   50
ReportHeader6             varchar                   50
ReportHeader7             varchar                   50
NumericDate               float                     8
FirstDayOfWeek            date                      3
LastDayOfWeek             date                      3
NFirstDayOfWeek           float                     8
NLastDayOfWeek            float                     8
FirstDayOfMonth           date                      3
LastDayOfMonth            date                      3
NFirstDayOfMonth          float                     8
NLastDayOfMonth           float                     8
HalfMonthStart            date                      3
HalfMonthEnd              date                      3
NHalfMonthStart           float                     8
NHalfMonthEnd             float                     8
HalfOfMonth               int                       4
FirstDayOfQuarter         date                      3
LastDayOfQuarter          date                      3
NFirstDayOfQuarter        float                     8
NLastDayOfQuarter         float                     8
FirstDayOfYear            date                      3
LastDayOfYear             date                      3
NFirstDayOfYear           float                     8
NLastDayOfYear            float                     8
Improve this question
4
  • 2
    From my perspective it doesn't make any sense at all to store date values in a numeric data type in a table. Any calculation with dates should be performed using date-related functions (e.g. DATEADD/DATEDIFF). Beside of that, each and every value you're trying to add should be part of a separate calendar table, if needed, and joined to the CustomerTransactions table.
    – Lmu92
    Aug 17, 2014 at 17:29
  • 1
    I also see no reason to use FLOAT for any of these types. Not related to the problem, of course, but worth mentioning.
    – Aaron Bertrand
    Aug 17, 2014 at 17:41
  • Generally I agree with both of you. And yes, we do have separate date tables, this is for compare purposes only. If you are interested in why we are not using functions or why we use FLOAT see my other post for the details: stackoverflow.com/questions/25315014/… (PS. The table has both dates and floats, dates are for display, the floats are for comparison).
    – Yossi
    Aug 18, 2014 at 21:29
  • Be aware the query you have for "syscolumns total" has a bug in the join. It will give you a much larger result than is correct if you have nvarchar columns in your table. The correct join is on xusertype instead of xtype. Also, good practise to use the newer sys.columns and sys.types instead of the deprecated syscolumns and systypes. select count(*) NumCols, SUM(sc.max_length) SumLength from sys.columns sc inner join sys.types st on sc.user_type_id = st.user_type_id where sc.object_id = object_id('dbo.MyTableName')
    – Mike
    Aug 4, 2017 at 10:52

1 Answer 1

Reset to default
8

This is likely due to previous alters (particularly of existing column widths) that are still reflected in the underlying page structure. Try rebuilding the table or dropping/re-creating the clustered index to reclaim that space.

Improve this answer
1
  • This was it, Created the table with _backup suffix, dropped original, renamed, and all works. thank you!
    – Yossi
    Aug 17, 2014 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.