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...
>

64bit Primary with 32bit Secondary SQL 2005

Currently I have a standby server which is kept in synch from the primary via
log-shipping. Both are running SQL 2005 Standard sp1, on Windows 2003 Server
w/sp1. My primary is 64bit capable, but my secondary is not. Are there any
concerns running the primary as x64bit with log-shipping to a 32-bit machine
(other than performance and memory differences). Occasionaly I have to
switch production apps between the two servers. The log-shipping is a simple
tlog backup/restore, not Microsoft's log-shipping.That should not be a problem.
--
Andrew J. Kelly SQL MVP
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:CD1113A6-4937-49E4-8963-B2B8FC392456@.microsoft.com...
> Currently I have a standby server which is kept in synch from the primary
> via
> log-shipping. Both are running SQL 2005 Standard sp1, on Windows 2003
> Server
> w/sp1. My primary is 64bit capable, but my secondary is not. Are there
> any
> concerns running the primary as x64bit with log-shipping to a 32-bit
> machine
> (other than performance and memory differences). Occasionaly I have to
> switch production apps between the two servers. The log-shipping is a
> simple
> tlog backup/restore, not Microsoft's log-shipping.
>

64bit Primary with 32bit Secondary SQL 2005

Currently I have a standby server which is kept in synch from the primary vi
a
log-shipping. Both are running SQL 2005 Standard sp1, on Windows 2003 Serve
r
w/sp1. My primary is 64bit capable, but my secondary is not. Are there any
concerns running the primary as x64bit with log-shipping to a 32-bit machine
(other than performance and memory differences). Occasionaly I have to
switch production apps between the two servers. The log-shipping is a simpl
e
tlog backup/restore, not Microsoft's log-shipping.That should not be a problem.
Andrew J. Kelly SQL MVP
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:CD1113A6-4937-49E4-8963-B2B8FC392456@.microsoft.com...
> Currently I have a standby server which is kept in synch from the primary
> via
> log-shipping. Both are running SQL 2005 Standard sp1, on Windows 2003
> Server
> w/sp1. My primary is 64bit capable, but my secondary is not. Are there
> any
> concerns running the primary as x64bit with log-shipping to a 32-bit
> machine
> (other than performance and memory differences). Occasionaly I have to
> switch production apps between the two servers. The log-shipping is a
> simple
> tlog backup/restore, not Microsoft's log-shipping.
>

4 key primary key vs 1 key artificial primary key

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Mattno the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?|||Originally posted by r937
no the subject of surrogate and natural keys, we already have many threads (http://www.dbforums.com/search.php?action=showresults&searchid=479872&sortby=lastpost&sortorder=descending) -- not sure if that will work, it's a search for surrogate and natural

one thing troubles me, and that is how you have both owner_id and owner_type_id in your candidate key

doesn't that violate some normal form or another? isn't owner_type_id totall dependent on which owner it is?

Thanks for the search keyword tips. I did indeed find a number of helpful threads.

Matt|||I believe the general resolution was that Plain-Bellied Sneetches prefer natural keys, while Star-Bellied Sneetches see the advantages of surrogate keys.

Experts opinions varried:
Moe: Surrogate Keys
Larry: Natural Keys
Curly: Nyuck nyuck nyuck

We are still awaiting final Papal Dispensation on the subject.|||hilarious

i'm afraid that rome will tell you that it can only be a natural primary key, as surrogates are the work of the devil

one of curly's best lines is "i'm tryna think but nothin happens!" which is how i feel sometimes when asked to list the pros and cons of surrogate keys versus natural

maybe i should just say the words "surrogate key" again, and perhaps also the words "natural key" -- so it shouldn't be a total loss, that way they will be salted quite heavily in this post, and therefore this thread, for the benefit of search engines

moe and larry: "niagara falls!! slowly i turn... step by step... inch by inch..."
[moe and larry advance on curly, who backs up into the bathroom, and falls into the bathtub]
moe: "what are you doing in there?!"
curly: "so it shouldn't be a total loss, i'm takin a bath"
moe: "a bath?!"
curly: "yeah, a bath"
moe (after a slight pause): "move over"|||For what it's worth:

it depends upon the scale of the data, the query composition and whether or not you need to sub index on a primary's sub key.

given

application_id, owner_id, and property_id

being the key, the whole key and nothing but the key, do you ever need to index to owner_id alone? if yes then:

owner_id, application_id, and property_id

and so on.

Do you join across tables, i.e. owner_id -> owner details and property_id -> property details? If so, which ordering of the composite key gives fastest access? Who the hell knows without knowledge of the data definition? Nobody, that's who.

There's no generic solution to this question, it depends entirely on your data, and you should normalize to a correctly third normal multiple key and then procedurally denormalize to a compound key if your unique scenario requires it.