Nov 17, 2010

Bug? - Multiple drop and add columns can cause error

This issue was first reported by my friend and colleague at eCollege Tim Sesst. We did a lot of research on this. Dinesh Asanka, Susantha and I really got into this.  Initially we thought this as a feature, but it took some time for us to understand the issue. Now we feel that it is a bug.

Before going any further, check this code:

IF OBJECT_ID('TestAlterTable') IS NOT NULL
    DROP TABLE TestAlterTable

create table dbo.TestAlterTable
(
     Id int identity not null CONSTRAINT PK_TestAlterTable PRIMARY KEY CLUSTERED
     ,col1 char(8000) not null
     ,col2 char(49) not null
)
go
Declare @i int =1
While @i <100
begin
print @i
IF OBJECT_ID('DF_Col2') IS NOT NULL
    ALTER TABLE dbo.TestAlterTable DROP CONSTRAINT DF_COl2

Alter table dbo.TestAlterTable Drop Column Col2
Alter table dbo.TestAlterTable add Col2 char(49) NOT NULL CONSTRAINT DF_Col2 DEFAULT('a')
set @i+=1
end


Based on the number of columns and the data types you have, at one point,  you may receive an error similar too this:

.Net SqlClient Data Provider: Msg 1701, Level 16, State 1, Line 9
Creating or altering table 'TestAlterTable' failed because the minimum row size would be 8061, including 8 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
 

 The number of times you need to drop and add columns depend totally on number of columns and data types used. 
Initially we thought that this was due to cleaning of data of the dropped columns from data pages.  Like the ghost entries, (where the row is deleted logically, but the data is still there in the data page) I was even prepared to name this action as ghost columns but, sorry, I was wrong. This happens at the meta data level.

Still we haven;t figured out the reason behind this.  But based on our research, it has all the behaviors of a bug. It does not happen at the first time. 

Interestingly, if you rebuild the primary key, you can continue on the same path for another fixed number of times.

If anyone knows a good reason to say, why this is not a bug, please let me know.
Thanks to all the guys at eCollege who worked on this. 

2 comments:

indika saminda kannangara said...

add and remove fixed
size (real) columns. It
appears that if all of the
added columns are
dropped, the subsequently
added columns are added at
their previous byte
offsets into the row. If even
one of the columns is not
dropped, none of the
row space before the
remaining column will be
reused, so the available
space
in the row for new columns
decreases over time.

indika saminda kannangara said...

msdn.microsoft.com/en-us/library/ms190969.aspx

Post a Comment