I am facing an issue while altering a column datatype. This issue is related to maximum allowable table row size. I have gone through lot of posts around this and tried to understand the row size concept and related things. As I understand, RowOverflow issue is fixed post SQL 2005 for variable length datatypes. But I'm seeing discrepancy with SQL version 2012 and 2014 and also not able to calculate the required 8060 byte row size to exceed for my table, I am posting in this forum to understand the real reason behind it from experts.
So, I have one database..I restore same bak file on SQL Server version 2012 and 2014. I am altering one column of a table from bit to tinyint..I am able to do it on 2014 instance but on 2012 I get below error-
Alter table 'xyz' failed because the added fixed column might cause existing data to go beyond the maximum allowable table row size of 8060 bytes.
Below are some stats (which obviously applies for both the instance since same bak has been restored) which might aid you to tell me what might be reason-
Table doesn't have any row inserted.
One clustered index with primary key present.
No rowguidcol column defined.
There are no computed columns.
No foreign key.
Total columns are 282 by count.
Sum of fixed length datatype is 590 bytes.
Sum of variable length datatype ((n)varchar, (n)varbinary) is 62732.(Plz do not ask why so much :))
Wasted Space = 424 bytes
Sum of inrow length of dropped columns = 5033 bytes (I could not figure out what was datatype of these; also apart from this there are two columns with 8000 bytes which are dropped). Below query i triggered to get these details.
WITH T AS
(
SELECT ISNULL(LEFT(MAX(name), 30), 'Dropped') AS column_name,
MAX(column_id) AS column_id,
ISNULL(MAX(CASE WHEN column_id IS NOT NULL THEN max_inrow_length END),
MAX(max_inrow_length)) AS max_inrow_length,
leaf_offset,
CASE WHEN leaf_offset < 0
THEN SUM(CASE WHEN column_id IS NULL THEN 2
ELSE 0
END)
ELSE MAX(max_inrow_length)
- MAX(CASE WHEN column_id IS NULL THEN 0
ELSE max_inrow_length
END)
END AS wasted_space
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p ON p.partition_id = pc.partition_id
LEFT JOIN sys.columns c ON column_id = partition_column_id
AND c.object_id = p.object_id
WHERE p.object_id = OBJECT_ID('XYZ')
GROUP BY leaf_offset
)
SELECT CASE WHEN GROUPING(column_name) = 0
THEN column_name
ELSE 'Total'
END AS column_name,
column_id,
max_inrow_length,
leaf_offset,
SUM(wasted_space) AS wasted_space
FROM T
GROUP BY ROLLUP (column_name,
column_id,
max_inrow_length,
leaf_offset)
ORDER BY GROUPING(column_name),
CASE WHEN leaf_offset > 0
THEN leaf_offset
ELSE 10000 - leaf_offset
END
Another couple of observations are, I was able to add new column to the table (though there is warning), but alter statement on same was failing with the error mentioned above. I was able to alter the datatype when I ran ALTER INDEX ON _ REBUILD; command on 2012 instance.
So, I would like to understand what is calculation behind its failure, I was not able to sum up to 8060 bytes which causes overflow. If it somehow, then why only with 2012 version and not on 2014.
Table Definition-
CREATE TABLE [dbo].[XYZ](
[ID] int IDENTITY(1,1) NOT NULL,
[ISFORANALYTICSUPDATE] bit NULL,
col1 nvarchar(60) NULL,
col2 nchar(25) NULL,
col3 nchar(5) NULL,
col4 nchar(10) NULL,
col5 nvarchar(35) NULL,
col6 nvarchar(25) NULL,
col7 nvarchar(40) NULL,
col8 nvarchar(25) NULL,
col9 nvarchar(25) NULL,
col10 nvarchar(50) NULL,
col11 nvarchar(25) NULL,
col12 nchar(3) NULL,
col13 float NULL,
col14 nvarchar(15) NULL,
col15 float NULL,
col16 nvarchar(15) NULL,
col17 money NULL,
col18 float NULL,
col19 float NULL,
col20 nvarchar(15) NULL,
col21 money NULL,
col22 datetime NULL,
col23 datetime NULL,
col24 datetime NULL,
col25 int NULL,
col26 datetime NULL,
col27 datetime NULL,
col28 nvarchar(15) NULL,
col29 nchar(20) NULL,
col30 nvarchar(20) NULL,
col31 datetime NULL,
col32 int NULL,
col33 nvarchar(25) NULL,
col34 nvarchar(20) NULL,
col35 datetime NULL,
col36 nvarchar(25) NULL,
col37 nchar(12) NULL,
col38 int NULL,
col39 nchar(3) NULL,
col40 nvarchar(15) NULL,
col41 nvarchar(15) NULL,
col42 nvarchar(50) NULL,
col43 datetime NULL,
col44 nvarchar(25) NULL,
col45 nvarchar(16) NULL,
col46 nvarchar(25) NULL,
col47 nvarchar(25) NULL,
col48 nvarchar(30) NULL,
col49 nvarchar(30) NULL,
col50 nvarchar(30) NULL,
col51 nvarchar(30) NULL,
col52 nvarchar(30) NULL,
col53 nvarchar(254) NULL,
col54 nvarchar(50) NULL,
col55 nvarchar(30) NULL,
col56 datetime NULL,
col57 nvarchar(30) NULL,
col58 nvarchar(30) NULL,
col59 nvarchar(30) NULL,
col60 nvarchar(50) NULL,
col61 nvarchar(50) NULL,
col62 nvarchar(50) NULL,
col63 nvarchar(50) NULL,
col64 nvarchar(50) NULL,
col65 nvarchar(30) NULL,
col66 nvarchar(30) NULL,
col67 nvarchar(20) NULL,
col68 nvarchar(20) NULL,
col69 nvarchar(20) NULL,
col70 nvarchar(20) NULL,
col71 nvarchar(20) NULL,
col72 nvarchar(20) NULL,
col73 nvarchar(20) NULL,
col74 nvarchar(30) NULL,
col75 [tinyint] NULL,
col76 nvarchar(15) NULL,
col77 nvarchar(15) NULL,
col78 datetime NULL,
col79 datetime NULL,
col80 datetime NULL,
col81 nchar(3) NULL,
col82 nchar(3) NULL,
col83 nchar(3) NULL,
col84 nchar(3) NULL,
col85 nchar(1) NULL,
col86 nvarchar(15) NULL,
col87 nvarchar(20) NULL,
col88 datetime NULL,
col89 datetime NULL,
col90 nchar(3) NULL,
col91 nvarchar(20) NULL,
col92 nvarchar(20) NULL,
col93 nvarchar(20) NULL,
col94 nvarchar(20) NULL,
col95 nvarchar(20) NULL,
col96 nchar(3) NULL,
col97 nvarchar(20) NULL,
col98 nvarchar(20) NULL,
col99 nvarchar(20) NULL,
col100 nvarchar(500) NULL,
col101 nvarchar(20) NULL,
col102 nvarchar(25) NULL,
col103 nchar(3) NULL,
col104 nchar(3) NULL,
col105 nchar(3) NULL,
col106 nvarchar(25) NULL,
col107 nvarchar(60) NULL,
col108 nvarchar(60) NULL,
col109 nvarchar(60) NULL,
col110 nvarchar(60) NULL,
col111 nvarchar(60) NULL,
col112 nvarchar(60) NULL,
col113 nvarchar(60) NULL,
col114 nvarchar(60) NULL,
col115 nvarchar(60) NULL,
col116 nvarchar(60) NULL,
col117 nvarchar(60) NULL,
col118 nvarchar(60) NULL,
col119 nvarchar(60) NULL,
col120 nvarchar(60) NULL,
col121 nvarchar(60) NULL,
col122 nvarchar(60) NULL,
col123 nvarchar(60) NULL,
col124 nvarchar(60) NULL,
col125 nvarchar(60) NULL,
col126 nvarchar(60) NULL,
col127 int NULL,
col128 nvarchar(60) NULL,
col129 nvarchar(100) NULL,
col130 nvarchar(254) NULL,
col131 datetime NULL,
col132 nvarchar(20) NULL,
col133 nvarchar(15) NULL,
col134 nvarchar(15) NULL,
col135 nvarchar(15) NULL,
col136 nvarchar(25) NULL,
col137 nvarchar(15) NULL,
col138 nvarchar(15) NULL,
col139 nvarchar(15) NULL,
col140 nvarchar(15) NULL,
col141 nvarchar(15) NULL,
col142 nvarchar(15) NULL,
col143 nchar(3) NULL,
col144 nchar(3) NULL,
col145 nchar(3) NULL,
col146 nvarchar(25) NULL,
col147 nvarchar(25) NULL,
col148 nvarchar(15) NULL,
col149 datetime NULL,
col150 nchar(3) NULL,
col151 nvarchar(15) NULL,
col152 nvarchar(15) NULL,
col153 nvarchar(15) NULL,
col154 nvarchar(20) NULL,
col155 nvarchar(20) NULL,
col156 nvarchar(20) NULL,
col157 nvarchar(15) NULL,
col158 nvarchar(15) NULL,
col159 datetime NULL,
col160 datetime NULL,
col161 nchar(1) NULL,
col162 nvarchar(50) NULL,
col163 nvarchar(50) NULL,
col164 nvarchar(50) NULL,
col165 nvarchar(50) NULL,
col166 nvarchar(50) NULL,
col167 nvarchar(50) NULL,
col168 nvarchar(50) NULL,
col169 nvarchar(50) NULL,
col170 nvarchar(50) NULL,
col171 nvarchar(50) NULL,
col172 nvarchar(50) NULL,
col173 nvarchar(250) NULL,
col174 nvarchar(100) NULL,
col175 nvarchar(max) NULL,
col176 datetime NULL,
col177 int NULL,
col178 tinyint NULL,
col179 nvarchar(max) NULL,
col180 nvarchar(5) NULL,
col181 int NULL,
col182 nvarchar(30) NULL,
col183 nvarchar(20) NULL,
col184 nvarchar(40) NULL,
col185 nvarchar(25) NULL,
col186 nvarchar(10) NULL,
col187 nvarchar(254) NULL,
col188 nvarchar(25) NULL,
col189 tinyint NULL,
col190 nvarchar(50) NULL,
col191 nvarchar(100) NULL,
col192 datetime NULL,
col193 nvarchar(1) NULL,
col194 nvarchar(35) NULL,
col195 nvarchar(25) NULL,
col196 nvarchar(50) NULL,
col197 nvarchar(3) NULL,
col198 nvarchar(6) NULL,
col199 nvarchar(25) NULL,
col200 nvarchar(25) NULL,
col201 nvarchar(50) NULL,
col202 nvarchar(25) NULL,
col203 datetime NULL,
col204 datetime NULL,
col205 decimal(15, 8) NULL,
col206 nvarchar(4) NULL,
col207 datetime NULL,
col208 tinyint NULL,
col209 nvarchar(25) NULL,
col210 datetime NULL,
col211 money NULL,
col212 nvarchar(25) NULL,
col213 datetime NULL,
col214 nvarchar(20) NULL,
col215 float NULL,
col216 float NULL,
col217 nvarchar(20) NULL,
col218 nvarchar(20) NULL,
col219 nvarchar(60) NULL,
col220 nvarchar(60) NULL,
col221 nvarchar(60) NULL,
col222 nvarchar(60) NULL,
col223 nvarchar(60) NULL,
col224 nvarchar(60) NULL,
col225 nvarchar(60) NULL,
col226 nvarchar(60) NULL,
col227 nvarchar(60) NULL,
col228 nvarchar(60) NULL,
col229 int NULL,
col230 nchar(11) NULL,
col231 nchar(6) NULL,
col232 nvarchar(500) NULL,
col233 nvarchar(500) NULL,
col234 nvarchar(500) NULL,
col235 nvarchar(500) NULL,
col236 nvarchar(500) NULL,
col237 nvarchar(500) NULL,
col238 nvarchar(500) NULL,
col239 nvarchar(500) NULL,
col240 nvarchar(500) NULL,
col241 nvarchar(500) NULL,
col242 nvarchar(500) NULL,
col243 nvarchar(500) NULL,
col244 nvarchar(500) NULL,
col245 nvarchar(500) NULL,
col246 nvarchar(500) NULL,
col247 nvarchar(500) NULL,
col248 nvarchar(500) NULL,
col249 nvarchar(500) NULL,
col250 nvarchar(500) NULL,
col251 nvarchar(500) NULL,
col252 nvarchar(500) NULL,
col253 nvarchar(500) NULL,
col254 nvarchar(500) NULL,
col255 nvarchar(500) NULL,
col256 nvarchar(500) NULL,
col257 nvarchar(500) NULL,
col258 nvarchar(500) NULL,
col259 nvarchar(500) NULL,
col260 nvarchar(500) NULL,
col261 nvarchar(500) NULL,
col262 nvarchar(500) NULL,
col263 nvarchar(500) NULL,
col264 nvarchar(500) NULL,
col265 nvarchar(500) NULL,
col266 nvarchar(500) NULL,
col267 nvarchar(500) NULL,
col268 nvarchar(500) NULL,
col269 nvarchar(500) NULL,
col270 nvarchar(500) NULL,
col271 nvarchar(500) NULL,
col272 nvarchar(500) NULL,
col273 nvarchar(500) NULL,
col274 nvarchar(500) NULL,
col275 nvarchar(500) NULL,
col276 nvarchar(500) NULL,
col277 nvarchar(500) NULL,
col278 nvarchar(500) NULL,
col279 nvarchar(500) NULL,
col280 nvarchar(500) NULL,
CONSTRAINT [PK_XYZ] PRIMARY KEY CLUSTERED
(
[ID] 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
