Drop-Add-Drop-Add .. Column of SQL Server table

This error was encountered by one of our team mates. His task was to simply add three columns to the table and populate data on them. So he has written a rollback script to drop this column. While testing he executed these scripts few times and he noticed that it was failing at the 6th instance. He was able to recover this by doing a Index Rebuild.

I was trying to figure out by writing an sample script for this but took little while.

SET NOCOUNT ON
IF OBJECT_ID('dbo.InitTable') IS NOT NULL
DROP TABLE InitTable
CREATE
TABLE InitTable
(
ID Int identity (1,1) PRIMARY KEY CLUSTERED,
Data1 varchar(1000) NOT NULL,
Data2 varchar(1000) NOT NULL)

INSERT INTO InitTable
(Data1,Data2)
VALUES
( REPLICATE('A' ,1000),
REPLICATE('B' ,1000))

DECLARE @LOOP_COUNT INT
DECLARE @MAX_LOOPS INT

DECLARE @DYNAMIC_SQL VARCHAR(8000)
SET @LOOP_COUNT = 1
SET @MAX_LOOPS = 1500

WHILE @LOOP_COUNT <= @MAX_LOOPS BEGIN SET @DYNAMIC_SQL = 'ALTER TABLE dbo.InitTable ADD ' + 'Data3 [varchar](400) NULL, ' + 'Data4 [varchar](400) NULL, ' + 'Data5 [varchar](400) NULL ' EXECUTE (@DYNAMIC_SQL) SET @DYNAMIC_SQL = 'Update InitTable' + ' SET Data3 = Replicate(''A'',400) ' + ' ,Data4 = Replicate(''B'',400)' + ' ,data5 = Replicate(''C'',400)' EXECUTE (@DYNAMIC_SQL) Alter table InitTable Alter Column Data3 varchar(400) NOT NULL Alter table InitTable Alter Column Data4 varchar(400) NOT NULL Alter table InitTable Alter Column Data5 varchar(400) NOT NULL Alter table InitTable drop column Data3 Alter table InitTable drop column Data4 Alter table InitTable drop column Data5 SET @LOOP_COUNT = @LOOP_COUNT + 1 END

So in the sixth loop this script is failing and also I notice if I commentet out following lines, this script is not failing at 6th instance but at the 11th instance.

Alter table InitTable
Alter Column Data3 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data4 varchar(400) NOT NULL

Alter table InitTable
Alter Column Data5 varchar(400) NOT NULL


Things not are not finished my friends.
For updating you can see I have used 400 charactors, if I increased it to 1000 it is failing at different point. Guess what, if it is 1000 it is not failing at 6th but at 9th. Why why why? know whos, I gave it up.