Sql-server – Apparantly errorneous Exceeds 8K row length message

alter-tabledatabase-designddlindexsql server

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

Best Answer

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.

Related Solutions

What happens when you modify (reduce) a column’s length

The Oracle Administrators Guide says the following:

Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns) and column encryption.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

If you are modifying a table to increase the length of a column of data type CHAR, realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

The Oracle SQL Language Reference has much more detail including the following:

You can change the data type of any column if all rows of the column contain nulls. However, if you change the data type of a column in a materialized view container table, then Oracle Database invalidates the corresponding materialized view.

You can always increase the size of a character or raw column or the precision of a numeric column, whether or not all the rows contain nulls. You can reduce the size of a data type of a column as long as the change does not require data to be modified.The database scans existing data and returns an error if data exists that exceeds the new length limit.

You can modify a DATE column to TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE. You can modify any TIMESTAMP WITH LOCAL TIME ZONE to a DATE column.

If the table is empty, then you can increase or decrease the leading field or the fractional second value of a datetime or interval column. If the table is not empty, then you can only increase the leading field or fractional second of a datetime or interval column.

For CHAR and VARCHAR2 columns, you can change the length semantics by specifying CHAR (to indicate character semantics for a column that was originally specified in bytes) or BYTE (to indicate byte semantics for a column that was originally specified in characters). To learn the length semantics of existing columns, query the CHAR_USED column of the ALL_, USER_, or DBA_TAB_COLUMNS data dictionary view.

There is additional information and restrictions in the above documentation. Here is a demonstration of attempting to reduce the precision of a Number column and reduce the length of a Varchar2. You can try other changes so you will know what will happen.

--Setup.
DROP TABLE FOO;
CREATE TABLE FOO (BAR Number, BAR2 VARCHAR2(300));
INSERT INTO FOO (SELECT Level, RPAD(to_char(Level),10*Level,to_char(Level)) 
   FROM DUAL CONNECT BY Level <=20);
COMMIT;
SELECT * FROM FOO;

--Reduce Number to Number(10).
ALTER TABLE FOO MODIFY (BAR NUMBER (10));

--Reduce Varchar2(300) to Varchar2(100) (data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100));

--Reduce Varchar2(300) to Varchar2(200) (no data would be truncated).
ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(200));

The alter statements have the following output:

ALTER TABLE FOO MODIFY (BAR NUMBER (10))
Error report:
SQL Error: ORA-01440: column to be modified must be empty to decrease precision or scale
01440. 00000 -  "column to be modified must be empty to decrease precision or scale"

ALTER TABLE FOO MODIFY (BAR2 VARCHAR2(100))
Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"

table FOO altered.

Reduce precision by creating a new column.

ALTER TABLE FOO ADD (BAR3 NUMBER(10));
UPDATE FOO SET Bar3 = Bar;
ALTER TABLE FOO DROP COLUMN BAR;
ALTER TABLE FOO RENAME COLUMN BAR3 TO BAR;

Sql-server – Length of LOB data (%1) to be replicated exceeds configured maximum %2

The max text repl size options exists to limit how much LOB data can be added to a replicated column. If you turn off the LOB size limit and a user inserts a large amount of LOB data, latency will increase and replication will come to a crawl. The max text repl size option can be used to prevent this.

Best Answer

Related Solutions

What happens when you modify (reduce) a column’s length

Sql-server – Length of LOB data (%1) to be replicated exceeds configured maximum %2

Related Question