asked
Row size issue in SQL Server 2012 but not in 2014 version onwards
Hello Team, I am facing an interesting 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 try 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- 1. Table doesn't have any row inserted. 2. One clustered index with primary key present. 3. No rowguidcol column defined. 4. There are no computed columns. 5. No foreign key. 6. Total columns are 282 by count. 7. Sum of fixed length datatype is 590 bytes. 8. Sum of variable length datatype ((n)varchar, (n)varbinary) is 62732 (Plz do not ask why so much :)) 9. Wasted Space = 424 bytes 10. 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. f 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('ags_mmdeal') 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. 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 The statement that I'm using to alter column- alter table XYZ alter column isforanalyticsupdate tinyint 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. Thanks, Umarfarukh
@umarfarukh I realise that your question seems to be "Why does alteration of this table work in SQL 2012 but not in later versions?" but I have a feeling that sooner or later you're going to have to address a more fundamental issue. You touch on it in this statement: *"...Plz do not ask why so much..."* The reality is that you have to address that very issue if you want something that is going to work efficiently and scale well into the future. SQL Server is a Relational Database Management System. It's exceedingly unlikely that the table you're working with is a well-designed, normalised relational table. You're asking SQL Server to do things it's not designed to do. As just one example, why do you need the last fifty or so columns to be nullable and precisely 500 characters in size. What would happen if they were say 490 or 510. Without seeing the underlying data, the number 500 just seems a bit arbitrary to me. One thing you might want to consider (bearing in mind that there will be about a million different improvements you can make once the nature of the data becomes apparent) is "vertical partitioning". If you can identify the most used, most important columns (they won't all be equally used and important) you might want to split them into one table for the commonly used important stuff and another table (with exactly the same pk) for the less used and less important data. That gets you over the 8060 "limitation" and you'll likely get much better performance too.
@GPO thanks for the reply. Yes I agree to your point, and even I'm also not all convinced with table structure that has been built. Basically this is staging table where data is parsed from files, so that's why you see columns with 100-500 length. Coming back to question, I resolved the issue by rebuilding the index. I tried DBCC cleanup and other options but only Index Rebuild worked for this issue. Post which, waste space was reduced and dropped columns were deleted.
18 People are following this question.
