Hello,
I've a problem setting up a constraint on a table. There are two primary
keys in that table and one of them will be filled in through an
access application, wether it be nothing or some integer value.
What i want to do is to set a zero-value in a primary key column, how
can i do this? I've tried coalesce([columnname],'',0), but that gives me
an error. I think the syntax for the constraint is wrong, but i don't
know how to set it correctly.
Can anyone help me?Jason
You cannot do that . It seems you need create an UNIQUE CONTRAINT which will
accept a NULL value
Look at this example written by Steve Kass
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)
INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?|||There can only be one designated primary key for a table; any additional
unique keys are called candidate keys. Column(s) with primary key constraint
cannot have NULL value and all values must be unique.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?sql
Showing posts with label constraint. Show all posts
Showing posts with label constraint. Show all posts
Friday, March 23, 2012
Primary Key Vs. Not NULL Unique Key
Hi,
Can anybody tell me the difference between a
primary key constraint
and
Not NULL Unique Key constraint with clustered index
Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance.
One more question that's running in my mind is, if a clustered index is created on Unique key column, what will be the index key of a NULL value
Thanks very much in advanc
GYKOne difference is that the primary key constraint is not necessarily a
clustered index. As a matter of fact, I would suggest that in most cases
the clustered index should not be a unique constraint as it will typically
cause for slow inserts. It might be better to cluster by run time criteria,
such as what are the rows that need to be referenced at the same time (more
often than not), that way when the data page is brought into RAM it is all
time well spent. Other than that, I am unaware of any difference. Anybody
else?
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||Ata
First of all I have already answered this question in programming group but
going back to your suggestions
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
I always like to say it depends so
When you create a clustered index, try to create it as a UNIQUE clustered
index, not a non-unique clustered index. The reason for this is that while
SQL Server will allow you to create a non-unique clustered index, under the
surface, SQL Server will make it unique for you by adding a 4-byte
"uniqueifer" to the index key to guarantee uniqueness. This only serves to
increase the size of the key, which increases disk I/O, which reduces
performance. If you specify that your clustered index is UNIQUE when it is
created, you will prevent this unnecessary overhead.
"Ata" <NO_SPAMar_alias001@.NO_SPAMparvan.net> wrote in message
news:pOPKb.10951$JQ1.8335@.pd7tw1no...
> One difference is that the primary key constraint is not necessarily a
> clustered index. As a matter of fact, I would suggest that in most cases
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
>
> --
> Ata R
> Parvan Consulting Inc
> NO_SPAMar_alias001@.NO_SPAMparvan.net
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> > Hi,
> >
> > Can anybody tell me the difference between a
> >
> > primary key constraint
> > and
> > Not NULL Unique Key constraint with clustered index.
> >
> > Theoritically both looks the same, but am interested to know their
> differences in terms of their storage and performance.
> >
> > One more question that's running in my mind is, if a clustered index is
> created on Unique key column, what will be the index key of a NULL value?
> >
> > Thanks very much in advance
> > GYK
>|||Ata,
>It might be better to cluster by run time criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
As a side note, this behavior is often undesirable in high-volume OLTP
environments, creating very contentious pages at the 'bottom' of the table.
This phenomenon is known as 'hot-spotting'
In that case, a more intelligent choice of clustering key is needed.
James Hokes|||The primary key is a constraint held against the table
whereas the unique index is a separate object linked to
the table (not very important just changes the way they
are handled).
Conceptually the PK identifies a record whereas a unique
index just prevents duplicate values. So theoretically the
PK should never be updated (delete + insert if required)
but this is not enforced.
Some things that need to identify records will use the PK
and will not work unless one is defined.
>--Original Message--
>Hi,
>Can anybody tell me the difference between a
>primary key constraint
>and
>Not NULL Unique Key constraint with clustered index.
>Theoritically both looks the same, but am interested to
know their differences in terms of their storage and
performance.
>One more question that's running in my mind is, if a
clustered index is created on Unique key column, what will
be the index key of a NULL value?
>Thanks very much in advance
>GYK
>.
>|||Another difference is that you may ONLY have ONE PK constraint on a table,
but you may have MANY unique constraints.;
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||James,
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
Which behaviour is "this behavior". Is hot-spotting having a unique
clustered index or is it the approach I was suggesting. Pls explain?
Ata.
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:OQ86tvS1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Ata,
> >It might be better to cluster by run time criteria,
> > such as what are the rows that need to be referenced at the same time
> (more
> > often than not), that way when the data page is brought into RAM it is
all
> > time well spent. Other than that, I am unaware of any difference.
> Anybody
> > else?
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
> In that case, a more intelligent choice of clustering key is needed.
> James Hokes
>|||Another problem of non-unique clustered indexes is when
non-clustered indexes are also on the table. Another
respondent pointed out the additional overhead storing the
keys. Another issue is where you want to reindex either by
BDReindex or by Creating Index with the Drop existing
option. Doing either will require the non-clustered
indexes also be recreated affecting downtime/usability of
the table during the operation.sql
Can anybody tell me the difference between a
primary key constraint
and
Not NULL Unique Key constraint with clustered index
Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance.
One more question that's running in my mind is, if a clustered index is created on Unique key column, what will be the index key of a NULL value
Thanks very much in advanc
GYKOne difference is that the primary key constraint is not necessarily a
clustered index. As a matter of fact, I would suggest that in most cases
the clustered index should not be a unique constraint as it will typically
cause for slow inserts. It might be better to cluster by run time criteria,
such as what are the rows that need to be referenced at the same time (more
often than not), that way when the data page is brought into RAM it is all
time well spent. Other than that, I am unaware of any difference. Anybody
else?
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||Ata
First of all I have already answered this question in programming group but
going back to your suggestions
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
I always like to say it depends so
When you create a clustered index, try to create it as a UNIQUE clustered
index, not a non-unique clustered index. The reason for this is that while
SQL Server will allow you to create a non-unique clustered index, under the
surface, SQL Server will make it unique for you by adding a 4-byte
"uniqueifer" to the index key to guarantee uniqueness. This only serves to
increase the size of the key, which increases disk I/O, which reduces
performance. If you specify that your clustered index is UNIQUE when it is
created, you will prevent this unnecessary overhead.
"Ata" <NO_SPAMar_alias001@.NO_SPAMparvan.net> wrote in message
news:pOPKb.10951$JQ1.8335@.pd7tw1no...
> One difference is that the primary key constraint is not necessarily a
> clustered index. As a matter of fact, I would suggest that in most cases
> the clustered index should not be a unique constraint as it will typically
> cause for slow inserts. It might be better to cluster by run time
criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
>
> --
> Ata R
> Parvan Consulting Inc
> NO_SPAMar_alias001@.NO_SPAMparvan.net
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> > Hi,
> >
> > Can anybody tell me the difference between a
> >
> > primary key constraint
> > and
> > Not NULL Unique Key constraint with clustered index.
> >
> > Theoritically both looks the same, but am interested to know their
> differences in terms of their storage and performance.
> >
> > One more question that's running in my mind is, if a clustered index is
> created on Unique key column, what will be the index key of a NULL value?
> >
> > Thanks very much in advance
> > GYK
>|||Ata,
>It might be better to cluster by run time criteria,
> such as what are the rows that need to be referenced at the same time
(more
> often than not), that way when the data page is brought into RAM it is all
> time well spent. Other than that, I am unaware of any difference.
Anybody
> else?
As a side note, this behavior is often undesirable in high-volume OLTP
environments, creating very contentious pages at the 'bottom' of the table.
This phenomenon is known as 'hot-spotting'
In that case, a more intelligent choice of clustering key is needed.
James Hokes|||The primary key is a constraint held against the table
whereas the unique index is a separate object linked to
the table (not very important just changes the way they
are handled).
Conceptually the PK identifies a record whereas a unique
index just prevents duplicate values. So theoretically the
PK should never be updated (delete + insert if required)
but this is not enforced.
Some things that need to identify records will use the PK
and will not work unless one is defined.
>--Original Message--
>Hi,
>Can anybody tell me the difference between a
>primary key constraint
>and
>Not NULL Unique Key constraint with clustered index.
>Theoritically both looks the same, but am interested to
know their differences in terms of their storage and
performance.
>One more question that's running in my mind is, if a
clustered index is created on Unique key column, what will
be the index key of a NULL value?
>Thanks very much in advance
>GYK
>.
>|||Another difference is that you may ONLY have ONE PK constraint on a table,
but you may have MANY unique constraints.;
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:B5C78836-B9F8-454E-BDC9-FB5B8105019A@.microsoft.com...
> Hi,
> Can anybody tell me the difference between a
> primary key constraint
> and
> Not NULL Unique Key constraint with clustered index.
> Theoritically both looks the same, but am interested to know their
differences in terms of their storage and performance.
> One more question that's running in my mind is, if a clustered index is
created on Unique key column, what will be the index key of a NULL value?
> Thanks very much in advance
> GYK|||James,
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
Which behaviour is "this behavior". Is hot-spotting having a unique
clustered index or is it the approach I was suggesting. Pls explain?
Ata.
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:OQ86tvS1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Ata,
> >It might be better to cluster by run time criteria,
> > such as what are the rows that need to be referenced at the same time
> (more
> > often than not), that way when the data page is brought into RAM it is
all
> > time well spent. Other than that, I am unaware of any difference.
> Anybody
> > else?
> As a side note, this behavior is often undesirable in high-volume OLTP
> environments, creating very contentious pages at the 'bottom' of the
table.
> This phenomenon is known as 'hot-spotting'
> In that case, a more intelligent choice of clustering key is needed.
> James Hokes
>|||Another problem of non-unique clustered indexes is when
non-clustered indexes are also on the table. Another
respondent pointed out the additional overhead storing the
keys. Another issue is where you want to reindex either by
BDReindex or by Creating Index with the Drop existing
option. Doing either will require the non-clustered
indexes also be recreated affecting downtime/usability of
the table during the operation.sql
primary key vs unique key, and indexing
Hi,
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>|||More discussion about this question.
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=17b7877f-6978-4038-bc16-0c426b6cf7eb&sloc=en-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
> frederic.nourry@.gmail.com,
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
> > Hi,
> >
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> >
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> > if so do i need to create an index on the 3 fields too or the unique
> > constraint is being indexed anyway ?
> >
> > Thanks
> >
> >|||Yes, I stand corrected again :) Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...
> frederic.nourry@.gmail.com,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>|||More discussion about this question.
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=17b7877f-6978-4038-bc16-0c426b6cf7eb&sloc=en-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
> frederic.nourry@.gmail.com,
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
> > Hi,
> >
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> >
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> > if so do i need to create an index on the 3 fields too or the unique
> > constraint is being indexed anyway ?
> >
> > Thanks
> >
> >|||Yes, I stand corrected again :) Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...
> frederic.nourry@.gmail.com,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things
primary key vs unique key, and indexing
Hi,
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>|||More discussion about this question.
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Yes, I stand corrected again
Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>|||More discussion about this question.
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Yes, I stand corrected again
Thank you. Brain went to Primary Key insteadoriginally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things
Primary Key Violation Constraint, how to debug.....
Hi all,
I have a stored procedure (from a vendor) that attempts to insert some
records.. Unfortunately, its a very buggy early version, and tech support is
sketchy at best, so I'm trying to figure out the problem myself..
This is the error I'm getting:
Server: Msg 2627, Level 14, State 1, Procedure
usp_SAIncShipToDeliveryLocation, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
object 'SA_ShipToDeliveryLocation'.
This is the code fragment that is causing the problem. I'd like to identify
the specific record that is causing primary key violation.. Is this
possible?
insert into SA_ShipToDeliveryLocation
(ShipToDeliveryLocationID
,ShipToDeliveryLocationName
,ShipToDeliveryLocationState
,ShipToDeliveryLocationZip
,ShipToDeliveryLocationCountry
,RegionKey
,CustomerKey
,ShipToDeliveryLocationKey
)
select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
when 0
then r.RegionKey
else null
end RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
from #SASTemp_ShipToDeliveryLocation sd
left join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A'
order by c.CustomerKey
,sd.ShipToDeliveryLocationID
set @.nError = @.@.error
if (@.nError <> 0)
begin
rollback tran;
return @.nError;
endTake your select statment, remove all columns but those in the PK column (or
columns), group by these columns and add a having clause (having count(*) >
1). Off hand, I think the use of a cross join is suspicious (unless there
is only one row in the table). Another thing to check is a poorly written
insert trigger (but that should generate an error with the trigger name in
it).|||There are 2 possible causes for this error. Either a row with the PK value
already exists in the target table or the select statement is returning more
that one row with the same key.
Assuming ShipToDeliveryLocationKey is the primary key of
SA_ShipToDeliveryLocation, you can include your source query as a derived
table to easily identify problem data. See untested examples below.
The CROSS JOIN looks suspect here since this will effectively multiply the
number or rows returned. You'll get the PK error if the
SA_ControlParameters table contains more than one row.
--keys that already exist
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
WHERE EXISTS
(
SELECT *
FROM SA_ShipToDeliveryLocation target
WHERE target.ShipToDeliveryLocationKey =
source.ShipToDeliveryLocationKey
)
--keys that duplicated in source query
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
JOIN
(SELECT sd.ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r ON sd.RegionID = r.RegionID
ANDr.RegionType = 'R'
JOIN SA_Customer c ON c.CustomerKey = sd.CustomerKey
CROSS JOIN SA_ControlParameters cp
WHERE sd.AddChangeDelete = 'A'
GROUP BY sd.ShipToDeliveryLocationKey
HAVING COUNT(*) > 1) dups ON
dups.ShipToDeliveryLocationKey = source.ShipToDeliveryLocationKey
Hope this helps.
Dan Guzman
SQL Server MVP
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>|||Thanks for the advice guys. Dan I'll give the derived table a shot and get
back to
Thanks very much
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>
I have a stored procedure (from a vendor) that attempts to insert some
records.. Unfortunately, its a very buggy early version, and tech support is
sketchy at best, so I'm trying to figure out the problem myself..
This is the error I'm getting:
Server: Msg 2627, Level 14, State 1, Procedure
usp_SAIncShipToDeliveryLocation, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
object 'SA_ShipToDeliveryLocation'.
This is the code fragment that is causing the problem. I'd like to identify
the specific record that is causing primary key violation.. Is this
possible?
insert into SA_ShipToDeliveryLocation
(ShipToDeliveryLocationID
,ShipToDeliveryLocationName
,ShipToDeliveryLocationState
,ShipToDeliveryLocationZip
,ShipToDeliveryLocationCountry
,RegionKey
,CustomerKey
,ShipToDeliveryLocationKey
)
select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
when 0
then r.RegionKey
else null
end RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
from #SASTemp_ShipToDeliveryLocation sd
left join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A'
order by c.CustomerKey
,sd.ShipToDeliveryLocationID
set @.nError = @.@.error
if (@.nError <> 0)
begin
rollback tran;
return @.nError;
endTake your select statment, remove all columns but those in the PK column (or
columns), group by these columns and add a having clause (having count(*) >
1). Off hand, I think the use of a cross join is suspicious (unless there
is only one row in the table). Another thing to check is a poorly written
insert trigger (but that should generate an error with the trigger name in
it).|||There are 2 possible causes for this error. Either a row with the PK value
already exists in the target table or the select statement is returning more
that one row with the same key.
Assuming ShipToDeliveryLocationKey is the primary key of
SA_ShipToDeliveryLocation, you can include your source query as a derived
table to easily identify problem data. See untested examples below.
The CROSS JOIN looks suspect here since this will effectively multiply the
number or rows returned. You'll get the PK error if the
SA_ControlParameters table contains more than one row.
--keys that already exist
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
WHERE EXISTS
(
SELECT *
FROM SA_ShipToDeliveryLocation target
WHERE target.ShipToDeliveryLocationKey =
source.ShipToDeliveryLocationKey
)
--keys that duplicated in source query
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
JOIN
(SELECT sd.ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r ON sd.RegionID = r.RegionID
ANDr.RegionType = 'R'
JOIN SA_Customer c ON c.CustomerKey = sd.CustomerKey
CROSS JOIN SA_ControlParameters cp
WHERE sd.AddChangeDelete = 'A'
GROUP BY sd.ShipToDeliveryLocationKey
HAVING COUNT(*) > 1) dups ON
dups.ShipToDeliveryLocationKey = source.ShipToDeliveryLocationKey
Hope this helps.
Dan Guzman
SQL Server MVP
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>|||Thanks for the advice guys. Dan I'll give the derived table a shot and get
back to
Thanks very much
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>
Primary Key Violation
Hi Everyone
I am occasionally getting the following error ......
Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplicat
e key in object 'TABLE1'
The sProc segment that is causing this error is ....
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field2 = field2 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
----
Where field1 and field2 is the composite primary key.
Any ideas how to modify my sProc to stop the primary key violation happening
Cheers
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try,
IF EXISTS (SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
if exists(SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2
+ 1)
print 'tell us what to do in this case.'
else
UPDATE
TABLE1
SET
field2 = field2 + 1
WHERE
field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
AMB
"Peter" wrote:
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplic
ate key in object 'TABLE1'
> The sProc segment that is causing this error is ....
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field
2)
> UPDATE TABLE1
> SET field2 = field2 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
> ----
-
> Where field1 and field2 is the composite primary key.
> Any ideas how to modify my sProc to stop the primary key violation happeni
ng
> Cheers
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||"Peter" <peter@.dwstech.com> wrote in message news:427c1f0e$1_2@.127.0.0.1...
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert
> duplicate key in object 'TABLE1'
You need to check if the row referenced by (@.field1, @.field2 + 1) exists
before you try to UPDATE. For instance, consider the following:
Field1 | Field2
100 | 100
100 | 101
In this instance if @.field1 = 100 and @.field2 = 100, the following will
cause a PK violation:
UPDATE TABLE1
SET field2 = field2 + 1
WHERE field1 = @.field1
AND field2 = @.field2
What exactly are you trying to accomplish? Maybe someone can help with the
logic, if you can supply more info...|||Oooops, my bad
The sProc code shouls have read ...
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
----
The object of the table is to act as a simple counter. If the primary key al
ready exists in the table then update the counter column (field3) by increme
nting by 1. If the primary key does not exist then insert the primary key an
d set the counter column to
1.
Sorry for the confusion.
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Are you still getting the error? If so then I would guess this to be a
concurrency issue. How busy is this database? Is it likely that two
processes would cause this to occur? If so, there are two things you can
do:
--easy, no other changes to you system possiblilty
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--we don't want anyone to touch the row:
BEGIN TRANSACTION
--lock it exclusively so no one else can read it
IF EXISTS (SELECT 1 FROM TABLE1 (xlock) WHERE field1 = @.field1 AND field2 =
@.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--alternately, change this from a counter into a very thin table:
create table counter
(
field1 int --needs a different name
counter bigint identity,
actiontime datetime,
primary key (field1, counter)
)
Then just insert rows into this table and use aggregates. You can store
more information about each activity, if you want to get a richer set of
information. This method remove all contention on insert other than the
picking of the next counter value, and that is extremely fast.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427c3742$1_2@.127.0.0.1...
> Oooops, my bad
> The sProc code shouls have read ...
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 =
> @.field2)
> UPDATE TABLE1
> SET field3 = field3 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
> ----
-
> The object of the table is to act as a simple counter. If the primary key
> already exists in the table then update the counter column (field3) by
> incrementing by 1. If the primary key does not exist then insert the
> primary key and set the counter column to 1.
> Sorry for the confusion.
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Louis & Michael
Thank you both for your replies.
I will expand a little further on the purpose of this table.
The table is for counting how many times any particular url on a website is
clicked. The primary key fields of the table are click_hour and url_id. url_
id is passed to the sProc and click_hour is calculated within the sProc as d
atediff(hour,0,getutcdate()
). If an entry exists within the table for the particular click_hour and url
_id, then the click_count field is incremented by 1, else an entry is added
to the table for that click_hour and url_id with the click_count field set w
ith an initial value of 1.
I have chosen this method as only one row needs to exist in the table for ea
ch url for each hour regardless of the number of clicks ... thus if one url
is clicked 1000 times each hour for 24 hours there is only 24 rows in the ta
ble as opposed to 24,000.
This sProc can be called anywhere up to 50,000 times a day so the database i
s reasonably busy.
Michael, the suggestion you gave reverts back to the one entry per click sce
nario which I want to avoid as does the second suggestion offered by Louis.
Therefore, unless anyone can come up with a better suggestion, I am looking
at implementing Louis' first suggestion. Louis, I am just wondering what per
formance impact XLOCK will have on the sProc. Also, is XLOCK a better option
than TABLOCK and if so cou
ld you please explain why.
Thanks again to both of you for taking the time to reply.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||sorry for the slow reply. I have been way busy of late:
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first
>suggestion. Louis, I am just wondering what performance impact XLOCK will
>have on the sProc. Also, is >XLOCK a better option than TABLOCK and if so
>could you please explain why.
>
The important thing difference between xlock and tablock is that one takes a
type of lock, the other locks a certain type of resource. You want SQL
Server to take a lock that means no one else can even look at it., but we
only want it to lock the row we are concerned with, not the entire table.
The one entry per click solution is the best solution because no contention.
Make a view of the data to give you your count, and even update your column
once a day and add these rows to count of the newly inserted ones, but as
long as you don't have too many people trying to update the same row, the
fact that you are single threading acess to the row in TABLE1 should not be
too concerning.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427df11b$1_1@.127.0.0.1...
> Louis & Michael
> Thank you both for your replies.
> I will expand a little further on the purpose of this table.
> The table is for counting how many times any particular url on a website
> is clicked. The primary key fields of the table are click_hour and url_id.
> url_id is passed to the sProc and click_hour is calculated within the
> sProc as datediff(hour,0,getutcdate()). If an entry exists within the
> table for the particular click_hour and url_id, then the click_count field
> is incremented by 1, else an entry is added to the table for that
> click_hour and url_id with the click_count field set with an initial value
> of 1.
> I have chosen this method as only one row needs to exist in the table for
> each url for each hour regardless of the number of clicks ... thus if one
> url is clicked 1000 times each hour for 24 hours there is only 24 rows in
> the table as opposed to 24,000.
> This sProc can be called anywhere up to 50,000 times a day so the database
> is reasonably busy.
> Michael, the suggestion you gave reverts back to the one entry per click
> scenario which I want to avoid as does the second suggestion offered by
> Louis.
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first suggestion. Louis, I am just
> wondering what performance impact XLOCK will have on the sProc. Also, is
> XLOCK a better option than TABLOCK and if so could you please explain why.
> Thanks again to both of you for taking the time to reply.
> Regards
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
I am occasionally getting the following error ......
Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplicat
e key in object 'TABLE1'
The sProc segment that is causing this error is ....
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field2 = field2 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
----
Where field1 and field2 is the composite primary key.
Any ideas how to modify my sProc to stop the primary key violation happening
Cheers
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--Try,
IF EXISTS (SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
if exists(SELECT * FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2
+ 1)
print 'tell us what to do in this case.'
else
UPDATE
TABLE1
SET
field2 = field2 + 1
WHERE
field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
AMB
"Peter" wrote:
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert duplic
ate key in object 'TABLE1'
> The sProc segment that is causing this error is ....
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field
2)
> UPDATE TABLE1
> SET field2 = field2 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2) VALUES (@.field1, @.field2)
> ----
-
> Where field1 and field2 is the composite primary key.
> Any ideas how to modify my sProc to stop the primary key violation happeni
ng
> Cheers
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News=
=--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ N
ewsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption =--
-
>|||"Peter" <peter@.dwstech.com> wrote in message news:427c1f0e$1_2@.127.0.0.1...
> Hi Everyone
> I am occasionally getting the following error ......
> Violation of PRIMARY KEY constraint 'PK_PRIMARY_KEY'. Cannot insert
> duplicate key in object 'TABLE1'
You need to check if the row referenced by (@.field1, @.field2 + 1) exists
before you try to UPDATE. For instance, consider the following:
Field1 | Field2
100 | 100
100 | 101
In this instance if @.field1 = 100 and @.field2 = 100, the following will
cause a PK violation:
UPDATE TABLE1
SET field2 = field2 + 1
WHERE field1 = @.field1
AND field2 = @.field2
What exactly are you trying to accomplish? Maybe someone can help with the
logic, if you can supply more info...|||Oooops, my bad
The sProc code shouls have read ...
----
IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 = @.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
----
The object of the table is to act as a simple counter. If the primary key al
ready exists in the table then update the counter column (field3) by increme
nting by 1. If the primary key does not exist then insert the primary key an
d set the counter column to
1.
Sorry for the confusion.
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Are you still getting the error? If so then I would guess this to be a
concurrency issue. How busy is this database? Is it likely that two
processes would cause this to occur? If so, there are two things you can
do:
--easy, no other changes to you system possiblilty
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--we don't want anyone to touch the row:
BEGIN TRANSACTION
--lock it exclusively so no one else can read it
IF EXISTS (SELECT 1 FROM TABLE1 (xlock) WHERE field1 = @.field1 AND field2 =
@.field2)
UPDATE TABLE1
SET field3 = field3 + 1,
WHERE field1 = @.field1
AND field2 = @.field2
ELSE
INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
COMMIT TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--alternately, change this from a counter into a very thin table:
create table counter
(
field1 int --needs a different name
counter bigint identity,
actiontime datetime,
primary key (field1, counter)
)
Then just insert rows into this table and use aggregates. You can store
more information about each activity, if you want to get a richer set of
information. This method remove all contention on insert other than the
picking of the next counter value, and that is extremely fast.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427c3742$1_2@.127.0.0.1...
> Oooops, my bad
> The sProc code shouls have read ...
> ----
-
> IF EXISTS (SELECT 1 FROM TABLE1 WHERE field1 = @.field1 AND field2 =
> @.field2)
> UPDATE TABLE1
> SET field3 = field3 + 1,
> WHERE field1 = @.field1
> AND field2 = @.field2
> ELSE
> INSERT INTO TABLE1 (field1, field2, field3) VALUES (@.field1, @.field2, 1)
> ----
-
> The object of the table is to act as a simple counter. If the primary key
> already exists in the table then update the counter column (field3) by
> incrementing by 1. If the primary key does not exist then insert the
> primary key and set the counter column to 1.
> Sorry for the confusion.
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--|||Louis & Michael
Thank you both for your replies.
I will expand a little further on the purpose of this table.
The table is for counting how many times any particular url on a website is
clicked. The primary key fields of the table are click_hour and url_id. url_
id is passed to the sProc and click_hour is calculated within the sProc as d
atediff(hour,0,getutcdate()
). If an entry exists within the table for the particular click_hour and url
_id, then the click_count field is incremented by 1, else an entry is added
to the table for that click_hour and url_id with the click_count field set w
ith an initial value of 1.
I have chosen this method as only one row needs to exist in the table for ea
ch url for each hour regardless of the number of clicks ... thus if one url
is clicked 1000 times each hour for 24 hours there is only 24 rows in the ta
ble as opposed to 24,000.
This sProc can be called anywhere up to 50,000 times a day so the database i
s reasonably busy.
Michael, the suggestion you gave reverts back to the one entry per click sce
nario which I want to avoid as does the second suggestion offered by Louis.
Therefore, unless anyone can come up with a better suggestion, I am looking
at implementing Louis' first suggestion. Louis, I am just wondering what per
formance impact XLOCK will have on the sProc. Also, is XLOCK a better option
than TABLOCK and if so cou
ld you please explain why.
Thanks again to both of you for taking the time to reply.
Regards
Peter
--== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet News==-
--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||sorry for the slow reply. I have been way busy of late:
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first
>suggestion. Louis, I am just wondering what performance impact XLOCK will
>have on the sProc. Also, is >XLOCK a better option than TABLOCK and if so
>could you please explain why.
>
The important thing difference between xlock and tablock is that one takes a
type of lock, the other locks a certain type of resource. You want SQL
Server to take a lock that means no one else can even look at it., but we
only want it to lock the row we are concerned with, not the entire table.
The one entry per click solution is the best solution because no contention.
Make a view of the data to give you your count, and even update your column
once a day and add these rows to count of the newly inserted ones, but as
long as you don't have too many people trying to update the same row, the
fact that you are single threading acess to the row in TABLE1 should not be
too concerning.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Peter" <peter@.dwstech.com> wrote in message news:427df11b$1_1@.127.0.0.1...
> Louis & Michael
> Thank you both for your replies.
> I will expand a little further on the purpose of this table.
> The table is for counting how many times any particular url on a website
> is clicked. The primary key fields of the table are click_hour and url_id.
> url_id is passed to the sProc and click_hour is calculated within the
> sProc as datediff(hour,0,getutcdate()). If an entry exists within the
> table for the particular click_hour and url_id, then the click_count field
> is incremented by 1, else an entry is added to the table for that
> click_hour and url_id with the click_count field set with an initial value
> of 1.
> I have chosen this method as only one row needs to exist in the table for
> each url for each hour regardless of the number of clicks ... thus if one
> url is clicked 1000 times each hour for 24 hours there is only 24 rows in
> the table as opposed to 24,000.
> This sProc can be called anywhere up to 50,000 times a day so the database
> is reasonably busy.
> Michael, the suggestion you gave reverts back to the one entry per click
> scenario which I want to avoid as does the second suggestion offered by
> Louis.
> Therefore, unless anyone can come up with a better suggestion, I am
> looking at implementing Louis' first suggestion. Louis, I am just
> wondering what performance impact XLOCK will have on the sProc. Also, is
> XLOCK a better option than TABLOCK and if so could you please explain why.
> Thanks again to both of you for taking the time to reply.
> Regards
> Peter
> --== Posted via mcse.ms - Unlimited-Uncensored-Secure Usenet
> News==--
> http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+
> Newsgroups
> --= East and West-Coast Server Farms - Total Privacy via Encryption
> =--
Wednesday, March 21, 2012
Primary key error Uppercase/lowercase
Hello,
I've got a problem with 2 tables, the first one has not constraint and is used as a data source, the second one as primary and foreign key constraints and is the destination.
Thank you !
sql
I've got a problem with 2 tables, the first one has not constraint and is used as a data source, the second one as primary and foreign key constraints and is the destination.
In the first one the columns are :
TABLE PROMOTION :
PROMOTION_ID; PROMOTION_DESC;
16a;-20% discount;
16A;-30% discount;
AbC;no discount;
aBc; -90% discount;
The second table, TD_PROMOTION, has the same structure, but the column PROMOTION_ID column has a primary key constraint.
When i want to insert data from PROMOTION to TD_PROMOTION, i've got a primary_key constraint error, because it seems that the database makes no difference between the codes 16a and 16A and between AbC and aBc.
Is there a way to fix this problem ?
Thank you in advance,
Eric.
You can specify if SQL Server should be case sensitive or not using collation. In this instance the column must have a case sensitive collation in order for you to be able to specify any type of unique constraint on it. For example, the first example will fail whereas the second will work, notice the CI and CS for case insensitive and sensitive.
CREATE TABLE test1 (
col1 varchar(20) COLLATE Latin1_General_CI_AS PRIMARY KEY
)
INSERT INTO test1 VALUES ('ASD')
INSERT INTO test1 VALUES ('asd')
CREATE TABLE test2 (
col1 varchar(20) COLLATE Latin1_General_CS_AS PRIMARY KEY
)
INSERT INTO test2 VALUES ('ASD')
INSERT INTO test2 VALUES ('asd')
DROP TABLE test1
DROP TABLE test2
Collation can be set at the column or database level. If set at database level then all character columns without a collation specified adopt the database collation.
There are many collations available to you, search Books Online for a list of these.
Nick
DBA http://www.comoni.co.uk
Thank you !
sql
Tuesday, March 20, 2012
Primary Key Constraint Violation
I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.I would load trhe text file in to a table of the correct structure, but =with no PK constraint. Then use SELECT DISTINCT -- to retrieve the =data you need and insert into final table. (INSERT INTO -- SELECT =DISTINCT -- FROM can by helpful for that part).
Mike John
"Matt" <tkiansoon@.yahoo.com> wrote in message =news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
> I need to insert a text file where might contains duplicate data into
> a table with primary keys using a DTS package. is there anyone out
> there could help me? I have tried couple way to get around, but still
> doesn't work.|||You could load it into a staging table first and then de-dupe it or have a
index with ignore duplicate key on the staging table (but this would slow
the load unless the text file is ordered the same as the index which would
need to be unique clustered) and assumes that the entire row is a duplicate
if the key is.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Matt" <tkiansoon@.yahoo.com> wrote in message
news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.I would load trhe text file in to a table of the correct structure, but =with no PK constraint. Then use SELECT DISTINCT -- to retrieve the =data you need and insert into final table. (INSERT INTO -- SELECT =DISTINCT -- FROM can by helpful for that part).
Mike John
"Matt" <tkiansoon@.yahoo.com> wrote in message =news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
> I need to insert a text file where might contains duplicate data into
> a table with primary keys using a DTS package. is there anyone out
> there could help me? I have tried couple way to get around, but still
> doesn't work.|||You could load it into a staging table first and then de-dupe it or have a
index with ignore duplicate key on the staging table (but this would slow
the load unless the text file is ordered the same as the index which would
need to be unique clustered) and assumes that the entire row is a duplicate
if the key is.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Matt" <tkiansoon@.yahoo.com> wrote in message
news:7a4ed84d.0308270840.25fe7496@.posting.google.com...
I need to insert a text file where might contains duplicate data into
a table with primary keys using a DTS package. is there anyone out
there could help me? I have tried couple way to get around, but still
doesn't work.
PRIMARY KEY constraint problem
I have an odd problem on something that used to work fine.
I have an SP that inserts a record into a table (Contract) with two keyed fields.
The keys are as follows:
ContractID and SeqID (Sequence)
These two keys make the records unique.
Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc...
Several weeks of using this procedure have been fine. Suddenly I started getting this error:
Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.
I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.
Has anybody experienced this before?
Any help would be apprciated!
Here is the code in the SP;
CREATE PROCEDURE bcipNewContractSeq @.ContractID Char(10 )AS
DECLARE @.MaxSeqID int
DECLARE @.NewSeqID int
SELECT @.MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @.ContractID
SET @.NewSeqID = @.MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @.ContractID
GOFirst...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here|||Originally posted by Brett Kaiser
First...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
No trigger changes or constraint chnages (I checked them) . I am the only person modifying this database. Script is attached.|||What does the sproc bcipCreateCommitment do?
It's in the insert trigger...|||Originally posted by Brett Kaiser
What does the sproc bcipCreateCommitment do?
It's in the insert trigger...
Inserts a record into a table (tblCommitment) based in the INSERTED Contract record. Inserts the ContractID and SeqID. Commitment level defaults to 0 and CommitLevelID is the IDENTITY - incremental by 1:
SCRIPT...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCommitment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCommitment]
GO
CREATE TABLE [dbo].[tblCommitment] (
[CommitLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqID] [int] NOT NULL ,
[CommitLevel] [int] NOT NULL ,
[SysDateEntered] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [PK_tblCommitment] PRIMARY KEY CLUSTERED
(
[CommitLevelID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [DF_tblCommitment_CommitLevel] DEFAULT (0) FOR [CommitLevel],
CONSTRAINT [DF_tblCommitment_SysDateEntered] DEFAULT (getdate()) FOR [SysDateEntered]
GO|||I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...|||Originally posted by Brett Kaiser
I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...
>>>>>>>>>>>>>>>>>>>
I see it now! You pegged it. "After further review of the play..."
In test it worked fine and I may not have done more than one additional and now in the production where there is more than one record being created it is going to grab more than one. The answer to my problem is to use the previous SeqID in the where clause to pull ONE record only for the copy.
I need to get more sleep...
Thanks for your time to help the SQL'y impaired!
RLM|||Don't mention it...but why SELECT FROM the table at all...except to get the name...
Seems like your table is 2nd normal form though...
You should try to avoid repetitive data...should probably be in a separate table...
Try this...
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT TOP 1 @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
And why aren't you using IDENTITY?|||Originally posted by rmetz
>>>>>>>>>>>>>>>>>>>
I need to get more sleep...
RLM
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.|||Originally posted by smasanam
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||The reason for the redundant data is due to "Inherited Database application". Under normal circumstances (meaning my design) I would not have had this. The table should be broken in two with the Contract table being the "Header" record and the sequence entries in another table as "Contract Details" therefore eliminating the need to cary over the extra baggage...in a"perfect world".
As for testing...I do my best with the amount of time I am given. The app was in a beta test mode when the problem occured so we didn't damage anything too badly. The fix however only took this addition " AND SeqID = @.MaxSeqID" to pull the last unique record for the copy.
BTW, this app is going to be re-written and you can rest assured that proper normalization will be exercised.
Thanks for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
Cheers!
>>>>>>>>>>>>>>>>>>>>>>>>>
Originally posted by Brett Kaiser
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||Originally posted by rmetz
for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
You telling me?
Hell, There been times...don't get me started...
I have an SP that inserts a record into a table (Contract) with two keyed fields.
The keys are as follows:
ContractID and SeqID (Sequence)
These two keys make the records unique.
Ex:
ContractID SeqID
12345 1
12345 2
12345 3
etc...
Several weeks of using this procedure have been fine. Suddenly I started getting this error:
Violation of PRIMARY KEY constraint 'PK_contract'. Cannot insert duplicate key in object 'Contract'.
The statement has been terminated.
I verified that the values do not violate the constraints. In fact, I can type the exact information into the table directly without a problem.
Has anybody experienced this before?
Any help would be apprciated!
Here is the code in the SP;
CREATE PROCEDURE bcipNewContractSeq @.ContractID Char(10 )AS
DECLARE @.MaxSeqID int
DECLARE @.NewSeqID int
SELECT @.MaxSeqID = Max(SeqID) from Contract_Live..Contract WHERE ContractID = @.ContractID
SET @.NewSeqID = @.MaxSeqID + 1
--Copy Contract info for new seq with new seqid- record has default start and end dates
INSERT INTO [Contract_Live].[dbo].[Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract_Live].[dbo].[Contract]
WHERE [Contract_Live].[dbo].[Contract] .ContractID = @.ContractID
GOFirst...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here|||Originally posted by Brett Kaiser
First...if it was working and now it's not...
Something changed...there are no mracles..
Did some one add a trigger?
Change the constraint?
Go to EM, right click on the table and script EVERYTHING and post it here
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
No trigger changes or constraint chnages (I checked them) . I am the only person modifying this database. Script is attached.|||What does the sproc bcipCreateCommitment do?
It's in the insert trigger...|||Originally posted by Brett Kaiser
What does the sproc bcipCreateCommitment do?
It's in the insert trigger...
Inserts a record into a table (tblCommitment) based in the INSERTED Contract record. Inserts the ContractID and SeqID. Commitment level defaults to 0 and CommitLevelID is the IDENTITY - incremental by 1:
SCRIPT...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCommitment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCommitment]
GO
CREATE TABLE [dbo].[tblCommitment] (
[CommitLevelID] [int] IDENTITY (1, 1) NOT NULL ,
[ContractID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SeqID] [int] NOT NULL ,
[CommitLevel] [int] NOT NULL ,
[SysDateEntered] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [PK_tblCommitment] PRIMARY KEY CLUSTERED
(
[CommitLevelID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCommitment] WITH NOCHECK ADD
CONSTRAINT [DF_tblCommitment_CommitLevel] DEFAULT (0) FOR [CommitLevel],
CONSTRAINT [DF_tblCommitment_SysDateEntered] DEFAULT (getdate()) FOR [SysDateEntered]
GO|||I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...|||Originally posted by Brett Kaiser
I got it...
No way was this the way it was before....Unless all you ever did was add 1 additional
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
GO
That code will try and insert n number of rows...all with the same dup key...
It's not trying to insert one that already exists...it's trying to insert many rows at the same time all with dup key...
just before the insert, take the select and add it before, and recompile it...you'll see what I'm saying...
It's a cheesy way, but you could say SELECT DISTINCT to eliminate your woes...
>>>>>>>>>>>>>>>>>>>
I see it now! You pegged it. "After further review of the play..."
In test it worked fine and I may not have done more than one additional and now in the production where there is more than one record being created it is going to grab more than one. The answer to my problem is to use the previous SeqID in the where clause to pull ONE record only for the copy.
I need to get more sleep...
Thanks for your time to help the SQL'y impaired!
RLM|||Don't mention it...but why SELECT FROM the table at all...except to get the name...
Seems like your table is 2nd normal form though...
You should try to avoid repetitive data...should probably be in a separate table...
Try this...
INSERT INTO [Contract] ([ContractID], [seqID], [Status], [ContractName])
SELECT TOP 1 @.ContractID, @.NewSeqID, 'In Process', ContractName
FROM [Contract]
WHERE ContractID = @.ContractID
And why aren't you using IDENTITY?|||Originally posted by rmetz
>>>>>>>>>>>>>>>>>>>
I need to get more sleep...
RLM
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.|||Originally posted by smasanam
Hi rmetz,
I am curious to know how come it was working in the first place. The same problem might have happened to you before.
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||The reason for the redundant data is due to "Inherited Database application". Under normal circumstances (meaning my design) I would not have had this. The table should be broken in two with the Contract table being the "Header" record and the sequence entries in another table as "Contract Details" therefore eliminating the need to cary over the extra baggage...in a"perfect world".
As for testing...I do my best with the amount of time I am given. The app was in a beta test mode when the problem occured so we didn't damage anything too badly. The fix however only took this addition " AND SeqID = @.MaxSeqID" to pull the last unique record for the copy.
BTW, this app is going to be re-written and you can rest assured that proper normalization will be exercised.
Thanks for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
Cheers!
>>>>>>>>>>>>>>>>>>>>>>>>>
Originally posted by Brett Kaiser
It worked in the first place because there was only 1 row...
That's the only case scenario it would have worked under...
This underscores the need for extensive testing...
Also, a lot of times I'll put SELECTs in the code so I can step through the results I'm suppose to be expecting...that's how I found out what was up..
(Should've just jumped out at me though...what a scub I am)|||Originally posted by rmetz
for the eye opener Brett! I should have seen it too. But sometimes you just wind up in a tail chasing rut until someone throws a stick at ya.
You telling me?
Hell, There been times...don't get me started...
Primary key constraint issues with merge replication
I have merge replication set up with a publisher and two subscribers. All of my primary key fields are set for autoincrement (not for replication). The articles in the publication are set to force the subscriber to use a range for generating unique prim
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. Any suggestions?
Yep. Disable the auto-identity range and do it yourself manually.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. Any suggestions?
Yep. Disable the auto-identity range and do it yourself manually.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Primary Key Constraint errors when replicating
I have merge replication set up with a publisher and two subscribers. All of my primary key fields are set for autoincrement (not for replication). The articles in the publication are set to force the subscriber to use a range for generating unique prim
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. I was under the impression that the range option was designed to solve this type of issue. If you have a suggestion, please
be as specific as possible. THANKS!
Aaron,
the check constraints should preclude duplicate primary key values being
entered. When you say Primary Key constraint errors, are these from
duplicates, or from the range having been used up? Could you check the
publisher and each subscriber's check constraints and post up what you find,
as well as the text of the error message.
TIA,
Paul Ibison
|||Turn off the auto identity management and manage the ranges yourself.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||How does one go about managing auto identity ranges without using sql
server?
Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The errors are from duplicates. The errors occur on the clients with
subscriptions. It seems that it runs fine for a while then the
duplicate key errors start popping up. It seems like the ranges quit
getting assigned. I'm really not sure. Several people have mentioned
that I should assign the ranges myself, How would I go about doing that?
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||http://www.mssqlserver.com/replication
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
Thanks for the article. I read the article and you mention that with
SQL Server 2000 you can set these ranges when setting up replication,
which I am doing but still seem to run into problems. How,
specifically, do I manually set a range for each subscriber.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Go back to that section. There is a specific article in there which details
one approach to manually managing ranges.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
I understand the method in which you recommend assigning ranges. In
that article, you mention that if you are using sql server 2000 this can
be done automatically through the article properties, which I am doing.
My problem is that we periodically have updates to our database which
forces us to drop replication, make the changes, and setup replication
again. In those instances we start experiencing primary key
errors(duplicates being assigned) and I believe it stems from the auto
identity range assignment being reseeded. What can I do to solve this.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. I was under the impression that the range option was designed to solve this type of issue. If you have a suggestion, please
be as specific as possible. THANKS!
Aaron,
the check constraints should preclude duplicate primary key values being
entered. When you say Primary Key constraint errors, are these from
duplicates, or from the range having been used up? Could you check the
publisher and each subscriber's check constraints and post up what you find,
as well as the text of the error message.
TIA,
Paul Ibison
|||Turn off the auto identity management and manage the ranges yourself.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||How does one go about managing auto identity ranges without using sql
server?
Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The errors are from duplicates. The errors occur on the clients with
subscriptions. It seems that it runs fine for a while then the
duplicate key errors start popping up. It seems like the ranges quit
getting assigned. I'm really not sure. Several people have mentioned
that I should assign the ranges myself, How would I go about doing that?
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||http://www.mssqlserver.com/replication
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
Thanks for the article. I read the article and you mention that with
SQL Server 2000 you can set these ranges when setting up replication,
which I am doing but still seem to run into problems. How,
specifically, do I manually set a range for each subscriber.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Go back to that section. There is a specific article in there which details
one approach to manually managing ranges.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
I understand the method in which you recommend assigning ranges. In
that article, you mention that if you are using sql server 2000 this can
be done automatically through the article properties, which I am doing.
My problem is that we periodically have updates to our database which
forces us to drop replication, make the changes, and setup replication
again. In those instances we start experiencing primary key
errors(duplicates being assigned) and I believe it stems from the auto
identity range assignment being reseeded. What can I do to solve this.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Primary key constraint error
We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna Turku
Hi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku
|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna Turku
Hi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku
|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>
Primary key constraint error
We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primar
y
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>|||I have got same error.
Have you guys been able to fix.
been updated and if it has been removed and added again with the same primar
y
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
>
>|||I have got same error.
Have you guys been able to fix.
Primary key constraint error
We have an SQL Server database that has one damaged data row. The row can't
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> > We have an SQL Server database that has one damaged data row. The row
> > can't
> > been updated and if it has been removed and added again with the same
> > primary
> > key, the row won't still work.
> > When we try to modify the row, we get on error:
> > 'Another user har modified the contents of this table or view; the
> > database
> > row you are modifying no longer exists in the database.'
> >
> > We have installed the SQL Server SP4.
> >
> > Is there a way fix the table?
> >
> > BR
> > Johanna Turku
>
>|||I have got same error.
Have you guys been able to fix.
been updated and if it has been removed and added again with the same primary
key, the row won't still work.
When we try to modify the row, we get on error:
'Another user har modified the contents of this table or view; the database
row you are modifying no longer exists in the database.'
We have installed the SQL Server SP4.
Is there a way fix the table?
BR
Johanna TurkuHi
Do you modify it by using EM?
"Sebitti" <sebittionline@.companysebitti.fi> wrote in message
news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> We have an SQL Server database that has one damaged data row. The row
> can't
> been updated and if it has been removed and added again with the same
> primary
> key, the row won't still work.
> When we try to modify the row, we get on error:
> 'Another user har modified the contents of this table or view; the
> database
> row you are modifying no longer exists in the database.'
> We have installed the SQL Server SP4.
> Is there a way fix the table?
> BR
> Johanna Turku|||Yes, I try to it by using EM, query analyzer and ado. None of them worked.
"Uri Dimant" wrote:
> Hi
> Do you modify it by using EM?
> "Sebitti" <sebittionline@.companysebitti.fi> wrote in message
> news:A70D025E-B2F6-49E2-B9FA-568E3D7750AE@.microsoft.com...
> > We have an SQL Server database that has one damaged data row. The row
> > can't
> > been updated and if it has been removed and added again with the same
> > primary
> > key, the row won't still work.
> > When we try to modify the row, we get on error:
> > 'Another user har modified the contents of this table or view; the
> > database
> > row you are modifying no longer exists in the database.'
> >
> > We have installed the SQL Server SP4.
> >
> > Is there a way fix the table?
> >
> > BR
> > Johanna Turku
>
>|||I have got same error.
Have you guys been able to fix.
Primary key clustered with / without constraint (question)
HI,
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
Danny
Hi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
Danny
Hi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
Primary key clustered with / without constraint (question)
HI,
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
----
--
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
DannyHi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
----
--
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
DannyHi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
Primary key clustered with / without constraint (question)
HI,
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
----
--
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
DannyHi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
I have some difficulty to understand the difference bettween those to
statements.
When do we specify "constraint" and when we don't need to ?
What is the pro and con of specifying "contraint"
Does anyone can guide me ?
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD
PRIMARY KEY CLUSTERED
----
--
ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
ADD CONSTRAINT [PK__AB000_field__07C12930]
PRIMARY KEY CLUSTERED
Thank you
DannyHi,
When do we specify "constraint" and when we don't need to ?
If you specifiy CONSTRAINT we can give our own contrain names, otherwise
system will generate its own contrain name starting with (PK_TABLENAME...)
What is the pro and con of specifying "contraint"?
There is no majour diffrence apart form name definition. (NO Pros and cons-
both are same)
Thanks
Hari
MCDBA
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:eAq$ZEGOEHA.3380@.TK2MSFTNGP11.phx.gbl...
> HI,
> I have some difficulty to understand the difference bettween those to
> statements.
> When do we specify "constraint" and when we don't need to ?
> What is the pro and con of specifying "contraint"
> Does anyone can guide me ?
>
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD
> PRIMARY KEY CLUSTERED
>
> ----
--
> --
> ALTER TABLE [dbo].[AB000_table_name] WITH NOCHECK
> ADD CONSTRAINT [PK__AB000_field__07C12930]
> PRIMARY KEY CLUSTERED
>
> Thank you
>
> Danny
>
primary key and indexes
Using SS2000 and EM. I saw a table designed by someone else. They had put a
primary key on a column and a unique constraint and made it clustered but not
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
Dan D.
Dan
http://www.sql-server-performance.co...ed_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.
|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.co...ed_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>
>
|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...[vbcol=seagreen]
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
|||I understand it now. Thanks Uri.
Dan D.
"Uri Dimant" wrote:
> Dan
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> Correct
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
>
>
primary key on a column and a unique constraint and made it clustered but not
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
Dan D.
Dan
http://www.sql-server-performance.co...ed_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.
|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.co...ed_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>
>
|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...[vbcol=seagreen]
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
|||I understand it now. Thanks Uri.
Dan D.
"Uri Dimant" wrote:
> Dan
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> Correct
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
>
>
primary key and indexes
Using SS2000 and EM. I saw a table designed by someone else. They had put a
primary key on a column and a unique constraint and made it clustered but not
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
--
Dan D.Dan
http://www.sql-server-performance.com/gv_clustered_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
--
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> > Using SS2000 and EM. I saw a table designed by someone else. They had put
> > a
> > primary key on a column and a unique constraint and made it clustered but
> > not
> > an index. What effect does clustering a constraint have? Then they put a
> > unique index on the same column.
> >
> > Is that better or worse than just creating one unique clustered index on
> > the
> > column? Doesn't creating a unique index also serve the same purpose as
> > creating the unique constraint?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
>> Dan
>> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>>
>>
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>> > Using SS2000 and EM. I saw a table designed by someone else. They had
>> > put
>> > a
>> > primary key on a column and a unique constraint and made it clustered
>> > but
>> > not
>> > an index. What effect does clustering a constraint have? Then they put
>> > a
>> > unique index on the same column.
>> >
>> > Is that better or worse than just creating one unique clustered index
>> > on
>> > the
>> > column? Doesn't creating a unique index also serve the same purpose as
>> > creating the unique constraint?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||I understand it now. Thanks Uri.
--
Dan D.
"Uri Dimant" wrote:
> Dan
> > My first questioin was what effect does clustering a unique, primary key
> > constraint have? I've since read that it seems that whether I specify in
> > EM
> > to make the primary key an index or not, the system does create an index.
> > If
> > that is the case the clustering part makes sense.
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> > But if the system does create an index for the primary key then having a
> > second non-clustered index on the same column seems to be redundant. Is
> > that
> > correct?
> Correct
> > And is it true that creating a unique index also serves as a unique
> > constraint?
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
> > Thanks but that really doesn't answer my questions.
> >
> > My first questioin was what effect does clustering a unique, primary key
> > constraint have? I've since read that it seems that whether I specify in
> > EM
> > to make the primary key an index or not, the system does create an index.
> > If
> > that is the case the clustering part makes sense.
> >
> > But if the system does create an index for the primary key then having a
> > second non-clustered index on the same column seems to be redundant. Is
> > that
> > correct?
> >
> > And is it true that creating a unique index also serves as a unique
> > constraint?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Dan
> >> http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> >> > Using SS2000 and EM. I saw a table designed by someone else. They had
> >> > put
> >> > a
> >> > primary key on a column and a unique constraint and made it clustered
> >> > but
> >> > not
> >> > an index. What effect does clustering a constraint have? Then they put
> >> > a
> >> > unique index on the same column.
> >> >
> >> > Is that better or worse than just creating one unique clustered index
> >> > on
> >> > the
> >> > column? Doesn't creating a unique index also serve the same purpose as
> >> > creating the unique constraint?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>
primary key on a column and a unique constraint and made it clustered but not
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
--
Dan D.Dan
http://www.sql-server-performance.com/gv_clustered_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
--
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> > Using SS2000 and EM. I saw a table designed by someone else. They had put
> > a
> > primary key on a column and a unique constraint and made it clustered but
> > not
> > an index. What effect does clustering a constraint have? Then they put a
> > unique index on the same column.
> >
> > Is that better or worse than just creating one unique clustered index on
> > the
> > column? Doesn't creating a unique index also serve the same purpose as
> > creating the unique constraint?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
>> Dan
>> http://www.sql-server-performance.com/gv_clustered_indexes.asp
>>
>>
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>> > Using SS2000 and EM. I saw a table designed by someone else. They had
>> > put
>> > a
>> > primary key on a column and a unique constraint and made it clustered
>> > but
>> > not
>> > an index. What effect does clustering a constraint have? Then they put
>> > a
>> > unique index on the same column.
>> >
>> > Is that better or worse than just creating one unique clustered index
>> > on
>> > the
>> > column? Doesn't creating a unique index also serve the same purpose as
>> > creating the unique constraint?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||I understand it now. Thanks Uri.
--
Dan D.
"Uri Dimant" wrote:
> Dan
> > My first questioin was what effect does clustering a unique, primary key
> > constraint have? I've since read that it seems that whether I specify in
> > EM
> > to make the primary key an index or not, the system does create an index.
> > If
> > that is the case the clustering part makes sense.
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> > But if the system does create an index for the primary key then having a
> > second non-clustered index on the same column seems to be redundant. Is
> > that
> > correct?
> Correct
> > And is it true that creating a unique index also serves as a unique
> > constraint?
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
> > Thanks but that really doesn't answer my questions.
> >
> > My first questioin was what effect does clustering a unique, primary key
> > constraint have? I've since read that it seems that whether I specify in
> > EM
> > to make the primary key an index or not, the system does create an index.
> > If
> > that is the case the clustering part makes sense.
> >
> > But if the system does create an index for the primary key then having a
> > second non-clustered index on the same column seems to be redundant. Is
> > that
> > correct?
> >
> > And is it true that creating a unique index also serves as a unique
> > constraint?
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Dan
> >> http://www.sql-server-performance.com/gv_clustered_indexes.asp
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> >> > Using SS2000 and EM. I saw a table designed by someone else. They had
> >> > put
> >> > a
> >> > primary key on a column and a unique constraint and made it clustered
> >> > but
> >> > not
> >> > an index. What effect does clustering a constraint have? Then they put
> >> > a
> >> > unique index on the same column.
> >> >
> >> > Is that better or worse than just creating one unique clustered index
> >> > on
> >> > the
> >> > column? Doesn't creating a unique index also serve the same purpose as
> >> > creating the unique constraint?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>
primary key and indexes
Using SS2000 and EM. I saw a table designed by someone else. They had put a
primary key on a column and a unique constraint and made it clustered but no
t
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
--
Dan D.Dan
http://www.sql-server-performance.c...red_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
--
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.c...red_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>
>|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...[vbcol=seagreen]
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||I understand it now. Thanks Uri.
--
Dan D.
"Uri Dimant" wrote:
> Dan
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> Correct
>
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
>
>
primary key on a column and a unique constraint and made it clustered but no
t
an index. What effect does clustering a constraint have? Then they put a
unique index on the same column.
Is that better or worse than just creating one unique clustered index on the
column? Doesn't creating a unique index also serve the same purpose as
creating the unique constraint?
Thanks,
--
Dan D.Dan
http://www.sql-server-performance.c...red_indexes.asp
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
> Using SS2000 and EM. I saw a table designed by someone else. They had put
> a
> primary key on a column and a unique constraint and made it clustered but
> not
> an index. What effect does clustering a constraint have? Then they put a
> unique index on the same column.
> Is that better or worse than just creating one unique clustered index on
> the
> column? Doesn't creating a unique index also serve the same purpose as
> creating the unique constraint?
> Thanks,
> --
> Dan D.|||Thanks but that really doesn't answer my questions.
My first questioin was what effect does clustering a unique, primary key
constraint have? I've since read that it seems that whether I specify in EM
to make the primary key an index or not, the system does create an index. If
that is the case the clustering part makes sense.
But if the system does create an index for the primary key then having a
second non-clustered index on the same column seems to be redundant. Is that
correct?
And is it true that creating a unique index also serves as a unique
constraint?
Thanks,
--
Dan D.
"Uri Dimant" wrote:
> Dan
> http://www.sql-server-performance.c...red_indexes.asp
>
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:8CBFC110-5862-4D75-9FDB-ABCBF03BC266@.microsoft.com...
>
>|||Dan
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
Look, there is a difference between CONTSTARINT and INDEX.
The first one is a 'logical' implementation , on othe other hand the
second one is a physical (CREATE B-TREE of the index ,sort the data)
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
Correct
> And is it true that creating a unique index also serves as a unique
> constraint?
No
When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
index to enforce the uniqueness
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...[vbcol=seagreen]
> Thanks but that really doesn't answer my questions.
> My first questioin was what effect does clustering a unique, primary key
> constraint have? I've since read that it seems that whether I specify in
> EM
> to make the primary key an index or not, the system does create an index.
> If
> that is the case the clustering part makes sense.
> But if the system does create an index for the primary key then having a
> second non-clustered index on the same column seems to be redundant. Is
> that
> correct?
> And is it true that creating a unique index also serves as a unique
> constraint?
> Thanks,
> --
> Dan D.
>
> "Uri Dimant" wrote:
>|||I understand it now. Thanks Uri.
--
Dan D.
"Uri Dimant" wrote:
> Dan
> Look, there is a difference between CONTSTARINT and INDEX.
> The first one is a 'logical' implementation , on othe other hand the
> second one is a physical (CREATE B-TREE of the index ,sort the data)
>
> Correct
>
> No
> When you create a UNIQUE CONSTRAINT SQL Server will create a non-clustered
> index to enforce the uniqueness
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:3B3489A2-BA84-448C-BE38-6BD2EB8E1634@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)