64-bit SQL

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 Server 2005 it works fine. It
fails on the 64-bit version of SQL Server 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,
MarkI don't have 64-bit system handy to test this on but you might see if you
can recreate the problem with parallelism off.
ALTER TABLE test1 ADD col4 int
CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
WITH (MAXDOP = 1)
IDENTITY
Hope this helps.
Dan Guzman
SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:efXagtwBGHA.2356@.tk2msftngp13.phx.gbl...
>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 Server 2005 it works fine. It
>fails on the 64-bit version of SQL Server 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
>
>|||I appreciate the help Dan. I had the same thought, (that it may be a
parellelism problem), but I hadn't tried your syntax. Unfortunately, I got
the same error after using your suggestion.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>I don't have 64-bit system handy to test this on but you might see if you
>can recreate the problem with parallelism off.
> ALTER TABLE test1 ADD col4 int
> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
> WITH (MAXDOP = 1)
> IDENTITY
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||The problem is intermittent. The first run of your syntax failed, the
second succeeded, the third failed.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>I don't have 64-bit system handy to test this on but you might see if you
>can recreate the problem with parallelism off.
> ALTER TABLE test1 ADD col4 int
> CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED
> WITH (MAXDOP = 1)
> IDENTITY
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:efXagtwBGHA.2356@.tk2msftngp13.phx.gbl...
>|||Even though the MAXDOP 1 didn't work around the problem, this information is
useful. I suggest you report this apparent bug using the feedback center at
http://lab.msdn.microsoft.com/productfeedback/. Include your repro script
and be sure to specify that the problem is intermittent, occurs only on
64-bit and that the MAXDOP 1 hint didn't help.
If you need a workaround before the problem is resolved, you might try
adding the identity column and primary key as separate statements.
Hope this helps.
Dan Guzman
SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:eAlN3MxBGHA.984@.tk2msftngp13.phx.gbl...
> The problem is intermittent. The first run of your syntax failed, the
> second succeeded, the third failed.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23ys8E5wBGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Dan,
I'll report the bug. By the way, I replaced the alter table component with
the following code:
Alter table test1 add col4 int identity
go
Alter table test1 add constraint pk_test1 primary key(col4)
And I still get the same error message.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oz98RYxBGHA.1092@.TK2MSFTNGP09.phx.gbl...

> Even though the MAXDOP 1 didn't work around the problem, this information
> is useful. I suggest you report this apparent bug using the feedback
> center at http://lab.msdn.microsoft.com/productfeedback/. Include your
> repro script and be sure to specify that the problem is intermittent,
> occurs only on 64-bit and that the MAXDOP 1 hint didn't help.
> If you need a workaround before the problem is resolved, you might try
> adding the identity column and primary key as separate statements.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:eAlN3MxBGHA.984@.tk2msftngp13.phx.gbl...
>