Friday, March 9, 2012

Errors adding a column in 64-bit version

I have run into an interesting problem. I have some code that adds a column to an existing table. The column is set as primary key, identity and clustered. On the 32-bit version of SQL 2005 it works fine. It fails on the 64-bit version of 2005 intermittently with a "could not create unique index because duplicate values were found". Kind of odd, considering as an identity field it's creating the values. I was able to recreate the problem with the following schema:

Create table test1

(col1 varchar(20),

col2 varchar(20),

col3 uniqueidentifier default newid())

-- insert data

Declare @.counter int

set @.counter=1

While @.counter < 1000000

BEGIN

insert into test1

values ('Joe','Smith',default)

Set @.counter=@.counter+1

END

-- add column

Alter table test1 add col4 int constraint PK_test1 primary key clustered identity

After Running this, I get this error:

CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.Test1' and index name 'PK_test1'. The duplicate key value is (28).

Anybody else run into this? Why would this be happening, and is there any way to fix it? I'm running the 64-bit version of Windows 2003 and the the 64-bit version of SQL Server 2005.

Thanks in advance,

Mark

Could you please file a bug with these details / repro steps at the MSDN Product Feedback Center? I do not have a 64-bit m/c to try this out. Also, please mention the editions of Windows and SQL Server (x64, IA, EMT). Thanks.

No comments:

Post a Comment