HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Apparantly errorneous Exceeds 8K row length message

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
lengthmessageapparantlyexceedserrorneousrow

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#74198, answer score: 8

Revisions (0)

No revisions yet.