Hi Experts, Is it possible to create a primary key without Clustered index?
If yes, where exactly would you use it?
Regards
Reshma> Hi Experts, Is it possible to create a primary key without Clustered
> index?
Sure,
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
dt SMALLDATETIME
);
GO
CREATE CLUSTERED INDEX i_dt ON dbo.foo(dt);
GO
> If yes, where exactly would you use it?
Hundreds of ways. What is your question really?
A|||Yes it is possible to create a primary key without clustered index. For your
better understanding find below a sample script I have attached.
--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int null
Constraint pk_tblTest primary key nonclustered (Field1)
)
Go
--Check the constraint type, key, name etc., (Just for cross checking)
sp_helpconstraint tblTest
1. We can have only one clustered index per table.
2. Normally all tables would have a primary key.
3. Clustered index would be created automatially if we create a PK field on
a table.
That said, based on your query pattern if you feel that another field marked
as clustered index would help the performance then this is the way to go.
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
"Reshma" wrote:
> Hi Experts, Is it possible to create a primary key without Clustered index
?
> If yes, where exactly would you use it?
> Regards
> Reshma|||Yes Aaron is right. We can directly say "primary key nonclustered" while
creating that field as shown in his example. But if at all you want to creat
e
a primary key based on more than one field (composite primary key) then my
script would be of help.
i.e.,
--Creates a sample table with non-clustered primary key field.
Create table tblTest
(
Field1 int identity not null,
Field2 varchar(30),
Field3 int not null
Constraint pk_tblTest primary key nonclustered (Field1, Field3)
)
Go
Best Regards
Vadivel
http://vadivel.blogspot.com
"Aaron Bertrand [SQL Server MVP]" wrote:
> Sure,
> CREATE TABLE dbo.foo
> (
> id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
> dt SMALLDATETIME
> );
> GO
> CREATE CLUSTERED INDEX i_dt ON dbo.foo(dt);
> GO
>
> Hundreds of ways. What is your question really?
> A
>
>|||Thanks for the explanation vadivel. Your second response indeed was really
helpful.
Regards
Reshma
"Reshma" wrote:
> Hi Experts, Is it possible to create a primary key without Clustered index
?
> If yes, where exactly would you use it?
> Regards
> Reshma
Showing posts with label clustered. Show all posts
Showing posts with label clustered. 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 Clustered Key
Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that is
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
Jamie
That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than have
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey is
constrained by that primary key, is there truly any use for it?
Regards,
Jamie
"MC" wrote:
> What do you mean?
> When creating PK you by default get clustered index on same columns. In some
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>
|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with an
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck at
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustered
index as unique as opposed to a primary key that references the cluster allow
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is completely
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a clustered
> index as unique as opposed to a primary key that references the cluster allow
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working with
> a view that must be faster and more efficient. If I can eliminate a primary
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being more
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. With
the primary key, every row could have a clustered field as null.
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I see.
The primary key will always be unique, will it not? A unique cluster can
be the only unique key in a table. There can't be both a primary key and a
unique cluster. So not having a primary key would actually result in one
less column. It would mean not having many records that could contain all
null values for that cluster. I think it answers the question. The unique
cluster is a better choice than a primary key if there are to be no foreign
keys for the table. I hope I understand it correctly.
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Narrow indexes have nothing to do with whether an index is a PRIMARY
> KEY or not.
> Maybe what you really mean is that you want to drop a *column* or
> remove a *column* from an index. But that has nothing to do with having
> a primary key or not - obviously you could just as easily put the
> PRIMARY KEY constraint on the other unique column(s) you have.
> Remember that a PRIMARY KEY is automatically indexed. There is no need
> to create another index.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
sql
sure, there is a primary key in a table with a unique clustered index that is
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
Jamie
That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than have
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey is
constrained by that primary key, is there truly any use for it?
Regards,
Jamie
"MC" wrote:
> What do you mean?
> When creating PK you by default get clustered index on same columns. In some
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>
|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with an
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck at
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a unique
> index, it would seem to me better to have the unique cluster rather than have
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing a
> column and making the table smaller and more efficient. If no foreignkey is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustered
index as unique as opposed to a primary key that references the cluster allow
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is completely
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a clustered
> index as unique as opposed to a primary key that references the cluster allow
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working with
> a view that must be faster and more efficient. If I can eliminate a primary
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being more
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. With
the primary key, every row could have a clustered field as null.
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||I see.
The primary key will always be unique, will it not? A unique cluster can
be the only unique key in a table. There can't be both a primary key and a
unique cluster. So not having a primary key would actually result in one
less column. It would mean not having many records that could contain all
null values for that cluster. I think it answers the question. The unique
cluster is a better choice than a primary key if there are to be no foreign
keys for the table. I hope I understand it correctly.
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Narrow indexes have nothing to do with whether an index is a PRIMARY
> KEY or not.
> Maybe what you really mean is that you want to drop a *column* or
> remove a *column* from an index. But that has nothing to do with having
> a primary key or not - obviously you could just as easily put the
> PRIMARY KEY constraint on the other unique column(s) you have.
> Remember that a PRIMARY KEY is automatically indexed. There is no need
> to create another index.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
sql
Primary key vs Clustered Key
Is there an advantage to a unique clustered key over a primary key? To be
sure, there is a primary key in a table with a unique clustered index that i
s
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
JamieWhat do you mean?
When creating PK you by default get clustered index on same columns. In some
cases, it would be better to change that to nonclustered and then create
clustered index on diff column(s). It depends on how that table is used
(queries, fk and so on)
MC
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
> Is there an advantage to a unique clustered key over a primary key? To be
> sure, there is a primary key in a table with a unique clustered index that
> is
> being held as a clustered - non unique table [with a primary key as th
e
> unique index]. There are no foreign keys for this table and I wonder if
> it
> would improve performance to modify the table in this manner.
> --
> Regards,
> Jamie|||That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than hav
e
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey i
s
constrained by that primary key, is there truly any use for it?
--
Regards,
Jamie
"MC" wrote:
> What do you mean?
> When creating PK you by default get clustered index on same columns. In so
me
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with a
n
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck a
t
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
>|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustere
d
index as unique as opposed to a primary key that references the cluster allo
w
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
--
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
--
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is complete
ly
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with
an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck
at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
>|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a cluste
red
> index as unique as opposed to a primary key that references the cluster al
low
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working wi
th
> a view that must be faster and more efficient. If I can eliminate a prima
ry
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being mor
e
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. Wit
h
the primary key, every row could have a clustered field as null.
--
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
sure, there is a primary key in a table with a unique clustered index that i
s
being held as a clustered - non unique table [with a primary key as the
unique index]. There are no foreign keys for this table and I wonder if it
would improve performance to modify the table in this manner.
Regards,
JamieWhat do you mean?
When creating PK you by default get clustered index on same columns. In some
cases, it would be better to change that to nonclustered and then create
clustered index on diff column(s). It depends on how that table is used
(queries, fk and so on)
MC
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
> Is there an advantage to a unique clustered key over a primary key? To be
> sure, there is a primary key in a table with a unique clustered index that
> is
> being held as a clustered - non unique table [with a primary key as th
e
> unique index]. There are no foreign keys for this table and I wonder if
> it
> would improve performance to modify the table in this manner.
> --
> Regards,
> Jamie|||That is what I mean. If a clustered index exists in a table and for this
example say the clustered index exists of two columns that comprise a unique
index, it would seem to me better to have the unique cluster rather than hav
e
an extra column that has the potential to allow null values to be entered
into the unique cluster and then you have the additional benefit of losing a
column and making the table smaller and more efficient. If no foreignkey i
s
constrained by that primary key, is there truly any use for it?
--
Regards,
Jamie
"MC" wrote:
> What do you mean?
> When creating PK you by default get clustered index on same columns. In so
me
> cases, it would be better to change that to nonclustered and then create
> clustered index on diff column(s). It depends on how that table is used
> (queries, fk and so on)
>
> MC
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:FE18B5CF-013B-449D-AE1C-926D187B736C@.microsoft.com...
>
>|||"primary key, is there truly any use for it?"
SMACK!
If a table has a primary key and no foreign keys (child tables), then it
probably has a foreign key to its parent.
Now, when you recover from getting smacked for the notion that you might
actually want a table with an index, but no primary key - the answer is a
very narrow yes. And I do mean NARROW.
The big example would be in setting up a data warehouse, which is completely
off topic. The other would be some kind of log.
On a website database, for example, you could have a table that contained:
user, IP address, date/time, page, ... that would qualify for a table with a
n
index, but no primary key. Note: no other tables in the DB relate to this
table and it doesn't relate to any other table - hence the logic of not
keying it.
Other than that sort of thing, you are asking for nothing but trouble not
enforcing referiental integerity in the database itself. Applications suck a
t
it, probably because it isn't the responsibility of the app designer to
maintain a clean database.
Consider it cheap insurance.
"thejamie" wrote:
[vbcol=seagreen]
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
> "MC" wrote:
>|||thejamie wrote:
> That is what I mean. If a clustered index exists in a table and for this
> example say the clustered index exists of two columns that comprise a uniq
ue
> index, it would seem to me better to have the unique cluster rather than h
ave
> an extra column that has the potential to allow null values to be entered
> into the unique cluster and then you have the additional benefit of losing
a
> column and making the table smaller and more efficient. If no foreignkey
is
> constrained by that primary key, is there truly any use for it?
> --
> Regards,
> Jamie
>
Every table should have a key and therefore it makes sense to enforce
them. A nullable column by definition cannot and should not be any part
of a key.
Conventionally one of the keys is designated as "primary" key but as
far as SQL Server is concerned the choice of where you use a PRIMARY
KEY constraint versus a UNIQUE NOT NULL constraints is of practically
no importance at all.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Let me go back to the cluster argument here... When the query plan is
examined, the pivot point is the clustered index. Wouldn't using a clustere
d
index as unique as opposed to a primary key that references the cluster allo
w
for better performance? Certainly if it forced enough integrity on the
database to prevent null values from entering the picture, it would also
force the table to have a minimal size...
--
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> Every table should have a key and therefore it makes sense to enforce
> them. A nullable column by definition cannot and should not be any part
> of a key.
> Conventionally one of the keys is designated as "primary" key but as
> far as SQL Server is concerned the choice of where you use a PRIMARY
> KEY constraint versus a UNIQUE NOT NULL constraints is of practically
> no importance at all.
> Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I'm actually focussed on your statement of "narrow" here. I am working with
a view that must be faster and more efficient. If I can eliminate a primary
key reference in the view and replace it with a unique clustered table
reference, I think it may help to improve the performance of that view.
--
Regards,
Jamie
"JayKon" wrote:
[vbcol=seagreen]
> "primary key, is there truly any use for it?"
> SMACK!
> If a table has a primary key and no foreign keys (child tables), then it
> probably has a foreign key to its parent.
> Now, when you recover from getting smacked for the notion that you might
> actually want a table with an index, but no primary key - the answer is a
> very narrow yes. And I do mean NARROW.
> The big example would be in setting up a data warehouse, which is complete
ly
> off topic. The other would be some kind of log.
> On a website database, for example, you could have a table that contained:
> user, IP address, date/time, page, ... that would qualify for a table with
an
> index, but no primary key. Note: no other tables in the DB relate to this
> table and it doesn't relate to any other table - hence the logic of not
> keying it.
> Other than that sort of thing, you are asking for nothing but trouble not
> enforcing referiental integerity in the database itself. Applications suck
at
> it, probably because it isn't the responsibility of the app designer to
> maintain a clean database.
> Consider it cheap insurance.
> "thejamie" wrote:
>|||thejamie wrote:
> Let me go back to the cluster argument here... When the query plan is
> examined, the pivot point is the clustered index. Wouldn't using a cluste
red
> index as unique as opposed to a primary key that references the cluster al
low
> for better performance? Certainly if it forced enough integrity on the
> database to prevent null values from entering the picture, it would also
> force the table to have a minimal size...
> --
> Regards,
> Jamie
>
A unique clustered index does not prevent null values. For that you
would have to make the columns NOT NULL as well.
Whether you use a unique index with or without a PRIMARY KEY constraint
should make no difference at all to performance - at least not in any
case that I know of. That's assuming no other changes to the table (no
columns added or removed and identical foreign keys and check
constraints in each case).
If you still have a question then please post a CREATE TABLE statement
and include your keys, constraints and indexes. That way we can
understand you situation better.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||thejamie wrote:
> I'm actually focussed on your statement of "narrow" here. I am working wi
th
> a view that must be faster and more efficient. If I can eliminate a prima
ry
> key reference in the view and replace it with a unique clustered table
> reference, I think it may help to improve the performance of that view.
Narrow indexes have nothing to do with whether an index is a PRIMARY
KEY or not.
Maybe what you really mean is that you want to drop a *column* or
remove a *column* from an index. But that has nothing to do with having
a primary key or not - obviously you could just as easily put the
PRIMARY KEY constraint on the other unique column(s) you have.
Remember that a PRIMARY KEY is automatically indexed. There is no need
to create another index.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The answer is working itself out. I did have the question initially - is it
really necessary to have a primary key? It is if there is a foreign key
associated with it. I am also feeling more comfortable with the cluster...
The unique cluster won't prevent nulls, but it would prevent there being mor
e
than one item in the table with a null for each item in the cluster... you
wouldn't have 100 rows with all nulls or the value would not be unique. Wit
h
the primary key, every row could have a clustered field as null.
--
Regards,
Jamie
"David Portas" wrote:
> thejamie wrote:
> A unique clustered index does not prevent null values. For that you
> would have to make the columns NOT NULL as well.
> Whether you use a unique index with or without a PRIMARY KEY constraint
> should make no difference at all to performance - at least not in any
> case that I know of. That's assuming no other changes to the table (no
> columns added or removed and identical foreign keys and check
> constraints in each case).
> If you still have a question then please post a CREATE TABLE statement
> and include your keys, constraints and indexes. That way we can
> understand you situation better.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Primary key vs Clustered Index with respect to Replication.
Is the following statement is TRUE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:
> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
I want to double check the above statement is valid
I created two tables with primary and clustered key.
create table tab1
(
col1 int primary key
, col2 int
)
create table tab2
(
col1 int ,
col2 int
)
CREATE UNIQUE CLUSTERED INDEX tab2_ind
ON tab2 (col1)
sp_help tab1
sp_help tab2
Few Observations
NULLABLE
Primary Key NO
Clustered Index YES
Col Constraint.
Primary Key YES
Clustered Index NO
Index
Primary Key clustered, unique
Clustered Index clustered, unique, primary key
For the Primary Key, A Constraint is created with the following values
constraint_type PRIMARY KEY (clustered)
constraint_name PK__tab1__486E7AE7
delete_action (n/a)
update_action (n/a)
status_enabled (n/a)
status_for_replication (n/a)
constraint_keys col1
In the above status_for_replication column value is (N/A)
I think Primary Key does not have any impact on replication.
Since I dont have any constraint for the Clustered Index
I think Clustered Index does not have any impact on replication.
Therefore I think the following statement is FALSE.
Primary Key will allow tables to participate in replication
whereas Clustered Index will not allow tables to participate in replication.
Irrespective of Primary Key or Clustered Index both tables will
participate in replication. Is it correct
Please throw some light on this issue.
Thanks in Advance
Rajesh Peddireddyi dont think there is anything to do with replication.
but what i see is, this has something to do with Referential integrity.
column in parent table should be a primary key
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Rajesh" wrote:
> Is the following statement is TRUE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> I want to double check the above statement is valid
> I created two tables with primary and clustered key.
> create table tab1
> (
> col1 int primary key
> , col2 int
> )
>
> create table tab2
> (
> col1 int ,
> col2 int
> )
>
> CREATE UNIQUE CLUSTERED INDEX tab2_ind
> ON tab2 (col1)
> sp_help tab1
> sp_help tab2
> Few Observations
> NULLABLE
> Primary Key NO
> Clustered Index YES
> Col Constraint.
> Primary Key YES
> Clustered Index NO
>
> Index
> Primary Key clustered, unique
> Clustered Index clustered, unique, primary key
> For the Primary Key, A Constraint is created with the following values
> constraint_type PRIMARY KEY (clustered)
> constraint_name PK__tab1__486E7AE7
> delete_action (n/a)
> update_action (n/a)
> status_enabled (n/a)
> status_for_replication (n/a)
> constraint_keys col1
> In the above status_for_replication column value is (N/A)
> I think Primary Key does not have any impact on replication.
> Since I dont have any constraint for the Clustered Index
> I think Clustered Index does not have any impact on replication.
>
> Therefore I think the following statement is FALSE.
> Primary Key will allow tables to participate in replication
> whereas Clustered Index will not allow tables to participate in replicatio
n.
> Irrespective of Primary Key or Clustered Index both tables will
> participate in replication. Is it correct
> Please throw some light on this issue.
> Thanks in Advance
> Rajesh Peddireddy|||On Wed, 10 Aug 2005 11:49:03 -0700, Rajesh
<Rajesh@.discussions.microsoft.com> wrote:
>Is the following statement is TRUE.
>Primary Key will allow tables to participate in replication
Transactional, true.
For Merge, either the PK or another unique index are GUIDs.
>whereas Clustered Index will not allow tables to participate in replication.[/color
]
False. Replication doesn't care about cluster, just about PK and/or
GUID.
J.
Tuesday, March 20, 2012
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...
>
>
Primary key and clustered index
Do I need to define clustered index on the primary key of a table explicity?> Do I need to define clustered index on the primary key of a table
> explicity?
A primary key constraint always creates a unique index so there is no need
to define one explicitly. You have the choice of creating the primary key
index as either clustered or non-clustered when you create the constraint.
If neither CLUSTERED nor NONCLUSTERED is specified and no clustered index
exists on the table, the default is clustered. A non-clustered primary key
index will be created if a clustered index already exists. Examples below:
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyColumn)
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (MyColumn)
--clustered if no existing clustered index, otherwise non-clustered
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY (MyColumn)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Man T" <alan_nospam_pltse@.yahoo.com.au> wrote in message
news:uOSlnYUoIHA.2064@.TK2MSFTNGP05.phx.gbl...
> Do I need to define clustered index on the primary key of a table
> explicity?
>
> explicity?
A primary key constraint always creates a unique index so there is no need
to define one explicitly. You have the choice of creating the primary key
index as either clustered or non-clustered when you create the constraint.
If neither CLUSTERED nor NONCLUSTERED is specified and no clustered index
exists on the table, the default is clustered. A non-clustered primary key
index will be created if a clustered index already exists. Examples below:
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY CLUSTERED (MyColumn)
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (MyColumn)
--clustered if no existing clustered index, otherwise non-clustered
ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY (MyColumn)
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Man T" <alan_nospam_pltse@.yahoo.com.au> wrote in message
news:uOSlnYUoIHA.2064@.TK2MSFTNGP05.phx.gbl...
> Do I need to define clustered index on the primary key of a table
> explicity?
>
Monday, March 12, 2012
Primary key
I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
as Primary key and by default also clustered index.
I use this ID in my INNER JOINS with order items to connect them.
I have also orderDate column in my order table, which is datetime field..
A lot of my queries include search or order condition by date, for example,
simplified one:
SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
o.orderDate
Now I would like to speed up the execution of this query.
I have 3 options:
1: orderDate as Primary key (it will be clustered index) and ORDER_ID not in
any index
2: orderDate+ORDER_ID as Primary key
3:orderDate as Primary key and nonclustered index on ORDER_ID column
Now, date will be in clustered index and select will be much faster, because
date is usually in where and order parts of query.
Order_ID is usually only in join conditions, so, I think it's not so
important to be as clustered index - if, than it should be append to date
column and both will present clustered index.
What is yours opinion?
Any suggestions, expirience with that?
Thank you,
SimonLet's start with basics. An IDENTITY columns can not be a key by
definition. It is not an attribute in the data model, but an exposed
physical locator for the physical storage of the data. It cannot be
validated or verified.
Newbies use it because they don't know what a key is and this looks
like a pointer or record number. Next, rows are not records and columns
are not fields.
It looks like you use date *ranges*, so a clustered index on the date
column would help quite a bit.
But order_id sounds like the natural key for an Orders table (once you
make it a real data type, add a check digit or validation rule, etc.).
There are primary indexes -- those required to enforce business rules
(UNIQUE, PRIMARY KEY) and secondary indexes -- those added for
performance. You have one of each.
As an aside, other products like Sybase will see the PK-FK relationship
between Orders and OrderItems and build a pointer structure that will
"pre-join" them and things will much faster.
In SQL Server you currently have to add indexing to the referencing
table on your own. The original design of SQL Server was done by
people who mapped tables to single files rather than viewing the schema
as a whole. This is why I keep beating people up about confusing
files/records/fields with tables/rows/columns; a bad mental model leads
to bad code.|||Everything Joe Celko said. I'll add a suggestion for generating your order
number (which you *should* be using as a PK). Your order number should be a
"smart key" -- ie, it will actually convey info about your order, unlike
Order #1702. Here's an exmple technique.
Find the magnitude of average orders per day and add one. If you see 40-70
orders, then your magnitude will be three.
You have two options from here. I'd go by your customer/order ratio. If it
is above 0.1 (which I would guess), then don't cater to only a handful of
customers, so a date is better to embed in the order number. Otherwise,
you'll want to embed the customer number.
Normal Ratio: Y{1,2}DDD-S+
Low Ration: C+-S+
Let's explore ...
Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005). It
all depends on how long you need to remember orders.
DDD is day of year. By doing this instead of MMDD, you save a digit. Not
for disk space, for short term memory.
S+ is the daily sequence number. The extra magnitude is for growth.
C+ is your customer number
- helps to split the number (mentally) and make it easier to remember.
Now when you cluster your PK (Order Num), it's ordered by the info you need.
And you can always index the other (Date).
-- Alex Papadimoulis
"simon" wrote:
> I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
> as Primary key and by default also clustered index.
> I use this ID in my INNER JOINS with order items to connect them.
> I have also orderDate column in my order table, which is datetime field..
> A lot of my queries include search or order condition by date, for example
,
> simplified one:
> SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
> WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
> o.orderDate
> Now I would like to speed up the execution of this query.
> I have 3 options:
> 1: orderDate as Primary key (it will be clustered index) and ORDER_ID not
in
> any index
> 2: orderDate+ORDER_ID as Primary key
> 3:orderDate as Primary key and nonclustered index on ORDER_ID column
> Now, date will be in clustered index and select will be much faster, becau
se
> date is usually in where and order parts of query.
> Order_ID is usually only in join conditions, so, I think it's not so
> important to be as clustered index - if, than it should be append to date
> column and both will present clustered index.
> What is yours opinion?
> Any suggestions, expirience with that?
> Thank you,
> Simon
>
>
>|||> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
I don't know. I agree with your order number, I just still like to use
identity values for primary keys, with a unique key on things like this
order_id. There are quite a few positive reasons to do so (performance
being one, and development pattern simplification being another) and really,
as long as you have a natural key, it is an exceptionally useful way to have
a non-changing key. And there is never a need to modify a primary key,
which is usually a real pain.
I have never heard an argument against identities that made enough sense to
balance out the ease of use. I certainly will never agree that they are
"exposed physical locators" but I will agree that they cannot be "validated
or verified" as Joe Celko has said. If they were physical locators they
would change as the physical storage of a row was moved. They aren't. They
are not a value I would share with the user, but a convienience in
development that keeps key size managable.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:0C0B35F4-07FC-416F-BA3E-C398C4D30525@.microsoft.com...
> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
> "smart key" -- ie, it will actually convey info about your order, unlike
> Order #1702. Here's an exmple technique.
> Find the magnitude of average orders per day and add one. If you see 40-70
> orders, then your magnitude will be three.
> You have two options from here. I'd go by your customer/order ratio. If it
> is above 0.1 (which I would guess), then don't cater to only a handful of
> customers, so a date is better to embed in the order number. Otherwise,
> you'll want to embed the customer number.
> Normal Ratio: Y{1,2}DDD-S+
> Low Ration: C+-S+
> Let's explore ...
> Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005).
> It
> all depends on how long you need to remember orders.
> DDD is day of year. By doing this instead of MMDD, you save a digit. Not
> for disk space, for short term memory.
> S+ is the daily sequence number. The extra magnitude is for growth.
> C+ is your customer number
> - helps to split the number (mentally) and make it easier to remember.
> Now when you cluster your PK (Order Num), it's ordered by the info you
> need.
> And you can always index the other (Date).
> -- Alex Papadimoulis
> "simon" wrote:
>|||On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
> I certainly will never agree that they are
> "exposed physical locators" but I will agree that they cannot be "validate
d
> or verified" as Joe Celko has said. If they were physical locators they
> would change as the physical storage of a row was moved. They aren't.
Mr. Celko's use of the word "physical" is still a higher level than what
most people think of as physical. It's higher than magnetic spins on the
hard drive; higher than sectors on the hard drive; higher than bytes in the
file on the filesystem; higher even than the structure inside a DAT file.
Anything that can't be ported from one platform to the next strictly with
SQL statements is "physical" in Mr. Celko's view, because it's part of the
implementation that might change in the next release of the software. Since
the Identity() attribute of a column is specific to MS SQL Server, and
requires internal code to run at the time of insert, it's physical in that
sense. And, since the identity value may be different depending on the
order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
the query optimizer may reorder however it likes), it really has nothing
whatsoever to do with the values in the row (or as Mr. Celko would want me
to think, nothing to do with the actual identity of the entity that the
table represents).
As a surrogate key, yes, they're awfully convenient. But I've found that
when I take the trouble to use real keys in my schema, all my code ends up
simplified, not complexified.|||Thanks very much Ross, for your translation of what Joe means when he says
'physical'. It drives me up the wall every time I read a message from him
telling someone that an IDENTITY is a physical locator for the physical
storage. I don't think Joe understands anything about SQL Server real
physical storage. But now knowing that Joe means something entirely
different when he uses that term, I will stop pulling my hair out. But we
will have to be ever vigilant to make sure new users know that when used by
Joe, 'physical' does not mean what they think it means.
Thanks again...
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1xbcau9ino4a7$.dlg@.rpresser.invalid...
> On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
>
> Mr. Celko's use of the word "physical" is still a higher level than what
> most people think of as physical. It's higher than magnetic spins on the
> hard drive; higher than sectors on the hard drive; higher than bytes in
> the
> file on the filesystem; higher even than the structure inside a DAT file.
> Anything that can't be ported from one platform to the next strictly with
> SQL statements is "physical" in Mr. Celko's view, because it's part of the
> implementation that might change in the next release of the software.
> Since
> the Identity() attribute of a column is specific to MS SQL Server, and
> requires internal code to run at the time of insert, it's physical in that
> sense. And, since the identity value may be different depending on the
> order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
> the query optimizer may reorder however it likes), it really has nothing
> whatsoever to do with the values in the row (or as Mr. Celko would want me
> to think, nothing to do with the actual identity of the entity that the
> table represents).
> As a surrogate key, yes, they're awfully convenient. But I've found that
> when I take the trouble to use real keys in my schema, all my code ends up
> simplified, not complexified.|||>> I'll add a suggestion for generating your order number (which you
Can you explain how and why it is beneficial to use a "smart key" for an
identifier? Are you aware of any drawbacks of using such "smart" or
intelligent keys?
Anith|||>> But we will have to be ever vigilant to make sure new users know that
Then OTOH, we will have to be aware that the ones using the terms "physical
table", "physical row", "physical column" etc do not mean what they think it
means either :-)
Anith|||Anith,
According to some (such as Louis, who replied earlier), the draw backs are:
> performance
> storage size
> they can change
But let's think about each of those. Are they "real" problems?
Performance. Did you know, your app can shave maybe 30ns if you forgo
database technology altogether. And loops (for, while, etc) -- you can easil
y
save a few clock cycles by not using them. Never accept "performance" as a
reason for doing something unless there are real world data to back this
claim up. I could make silly performance articles about AutoID as well -- th
e
system has to take extra cycles to generate the ID, check that you don't try
to insert it, etc.
Storage Size. The key I suggested was 8 bytes (YDDDSSSS). This would allow
for 999 orders a day for 10 years. That's well over 3 million orders. If you
wanted to do the same with an auto ID, you'd need a bigint (8 bytes).
Whoops. Let's compare a smaller key (YDDDSS, 6 bytes) versus int (4 bytes).
Even if we max out the int (at 2.15 Million), we save a whopping 4.3 Million
bytes. How about we just delete "solitare" instead of worrying about this?
They can change. Oh this is my favorite. How many times has amazon.com told
you "dear customer, we're sorry, but your order number has changed from
21040204-a34 to 24030204-a34." Find me a case where your PK will change, and
I'll show you a poorly designed system.
AutoIDs should remain in MS Access. They're good for one thing -- whipping
together a quick and dirty database. The whole point of "Relational"
databases is to allow data to relate to other data by the data iteself (keys
)
instead of these artificial AutoIDs.
-- Alex Papadimoulis
"Anith Sen" wrote:
> Can you explain how and why it is beneficial to use a "smart key" for an
> identifier? Are you aware of any drawbacks of using such "smart" or
> intelligent keys?
> --
> Anith
>
>|||I also react to the "academic" view of Primary Keys... One argument that
always irritates me is the idea that Identitys are bad because they have no
relationship or connection to the entity in the row... (It is not an
attribute in the data model) But then I will see it argued by the same
individual, that Social Security Number, or CustomerNo, or PartNumber (all
constructed artificuially by a third party) IS an appropriate key! (... But
order_id sounds like the natural key...)
Any value that uniquely identifies a row, imho, is a suitable candidate for
a Key. That value Must be constructed... either from meaningful data, or
from non-meaningful data. If you choose to construct it from meaningful dat
a
(Attributes in the data model) Then you ALWAYS have the problem of picking a
n
attribute (or set of attributes) whose values are least likely to change -
AND the isssue of propagating changes when the real-world values of the
attributes for that row DO change, (And they always will - because nothing i
n
the real-world is 100% fixed, no matter what the academics think.)
If you choose a non-meaningful key, how it is constructed - whether you use
a Identity, or some arbitrary algorithm, doesn't really matter, as long as
you can guarantee uniqueness. And Identities do that quite nicely.
"Anith Sen" wrote:
> Then OTOH, we will have to be aware that the ones using the terms "physica
l
> table", "physical row", "physical column" etc do not mean what they think
it
> means either :-)
> --
> Anith
>
>
as Primary key and by default also clustered index.
I use this ID in my INNER JOINS with order items to connect them.
I have also orderDate column in my order table, which is datetime field..
A lot of my queries include search or order condition by date, for example,
simplified one:
SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
o.orderDate
Now I would like to speed up the execution of this query.
I have 3 options:
1: orderDate as Primary key (it will be clustered index) and ORDER_ID not in
any index
2: orderDate+ORDER_ID as Primary key
3:orderDate as Primary key and nonclustered index on ORDER_ID column
Now, date will be in clustered index and select will be much faster, because
date is usually in where and order parts of query.
Order_ID is usually only in join conditions, so, I think it's not so
important to be as clustered index - if, than it should be append to date
column and both will present clustered index.
What is yours opinion?
Any suggestions, expirience with that?
Thank you,
SimonLet's start with basics. An IDENTITY columns can not be a key by
definition. It is not an attribute in the data model, but an exposed
physical locator for the physical storage of the data. It cannot be
validated or verified.
Newbies use it because they don't know what a key is and this looks
like a pointer or record number. Next, rows are not records and columns
are not fields.
It looks like you use date *ranges*, so a clustered index on the date
column would help quite a bit.
But order_id sounds like the natural key for an Orders table (once you
make it a real data type, add a check digit or validation rule, etc.).
There are primary indexes -- those required to enforce business rules
(UNIQUE, PRIMARY KEY) and secondary indexes -- those added for
performance. You have one of each.
As an aside, other products like Sybase will see the PK-FK relationship
between Orders and OrderItems and build a pointer structure that will
"pre-join" them and things will much faster.
In SQL Server you currently have to add indexing to the referencing
table on your own. The original design of SQL Server was done by
people who mapped tables to single files rather than viewing the schema
as a whole. This is why I keep beating people up about confusing
files/records/fields with tables/rows/columns; a bad mental model leads
to bad code.|||Everything Joe Celko said. I'll add a suggestion for generating your order
number (which you *should* be using as a PK). Your order number should be a
"smart key" -- ie, it will actually convey info about your order, unlike
Order #1702. Here's an exmple technique.
Find the magnitude of average orders per day and add one. If you see 40-70
orders, then your magnitude will be three.
You have two options from here. I'd go by your customer/order ratio. If it
is above 0.1 (which I would guess), then don't cater to only a handful of
customers, so a date is better to embed in the order number. Otherwise,
you'll want to embed the customer number.
Normal Ratio: Y{1,2}DDD-S+
Low Ration: C+-S+
Let's explore ...
Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005). It
all depends on how long you need to remember orders.
DDD is day of year. By doing this instead of MMDD, you save a digit. Not
for disk space, for short term memory.
S+ is the daily sequence number. The extra magnitude is for growth.
C+ is your customer number
- helps to split the number (mentally) and make it easier to remember.
Now when you cluster your PK (Order Num), it's ordered by the info you need.
And you can always index the other (Date).
-- Alex Papadimoulis
"simon" wrote:
> I have order table with ORDER_ID [int] IDENTITY (1, 1) NOT NULL ,
> as Primary key and by default also clustered index.
> I use this ID in my INNER JOINS with order items to connect them.
> I have also orderDate column in my order table, which is datetime field..
> A lot of my queries include search or order condition by date, for example
,
> simplified one:
> SELECT * FROM ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID=i.ORDER_ID
> WHERE o.orderDate>='20050212' AND o.orderDate<'20050228' ORDER BY
> o.orderDate
> Now I would like to speed up the execution of this query.
> I have 3 options:
> 1: orderDate as Primary key (it will be clustered index) and ORDER_ID not
in
> any index
> 2: orderDate+ORDER_ID as Primary key
> 3:orderDate as Primary key and nonclustered index on ORDER_ID column
> Now, date will be in clustered index and select will be much faster, becau
se
> date is usually in where and order parts of query.
> Order_ID is usually only in join conditions, so, I think it's not so
> important to be as clustered index - if, than it should be append to date
> column and both will present clustered index.
> What is yours opinion?
> Any suggestions, expirience with that?
> Thank you,
> Simon
>
>
>|||> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
I don't know. I agree with your order number, I just still like to use
identity values for primary keys, with a unique key on things like this
order_id. There are quite a few positive reasons to do so (performance
being one, and development pattern simplification being another) and really,
as long as you have a natural key, it is an exceptionally useful way to have
a non-changing key. And there is never a need to modify a primary key,
which is usually a real pain.
I have never heard an argument against identities that made enough sense to
balance out the ease of use. I certainly will never agree that they are
"exposed physical locators" but I will agree that they cannot be "validated
or verified" as Joe Celko has said. If they were physical locators they
would change as the physical storage of a row was moved. They aren't. They
are not a value I would share with the user, but a convienience in
development that keeps key size managable.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:0C0B35F4-07FC-416F-BA3E-C398C4D30525@.microsoft.com...
> Everything Joe Celko said. I'll add a suggestion for generating your order
> number (which you *should* be using as a PK). Your order number should be
> a
> "smart key" -- ie, it will actually convey info about your order, unlike
> Order #1702. Here's an exmple technique.
> Find the magnitude of average orders per day and add one. If you see 40-70
> orders, then your magnitude will be three.
> You have two options from here. I'd go by your customer/order ratio. If it
> is above 0.1 (which I would guess), then don't cater to only a handful of
> customers, so a date is better to embed in the order number. Otherwise,
> you'll want to embed the customer number.
> Normal Ratio: Y{1,2}DDD-S+
> Low Ration: C+-S+
> Let's explore ...
> Y is for year. You can go one or two digits (5 for 2005 or 05 for 2005).
> It
> all depends on how long you need to remember orders.
> DDD is day of year. By doing this instead of MMDD, you save a digit. Not
> for disk space, for short term memory.
> S+ is the daily sequence number. The extra magnitude is for growth.
> C+ is your customer number
> - helps to split the number (mentally) and make it easier to remember.
> Now when you cluster your PK (Order Num), it's ordered by the info you
> need.
> And you can always index the other (Date).
> -- Alex Papadimoulis
> "simon" wrote:
>|||On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
> I certainly will never agree that they are
> "exposed physical locators" but I will agree that they cannot be "validate
d
> or verified" as Joe Celko has said. If they were physical locators they
> would change as the physical storage of a row was moved. They aren't.
Mr. Celko's use of the word "physical" is still a higher level than what
most people think of as physical. It's higher than magnetic spins on the
hard drive; higher than sectors on the hard drive; higher than bytes in the
file on the filesystem; higher even than the structure inside a DAT file.
Anything that can't be ported from one platform to the next strictly with
SQL statements is "physical" in Mr. Celko's view, because it's part of the
implementation that might change in the next release of the software. Since
the Identity() attribute of a column is specific to MS SQL Server, and
requires internal code to run at the time of insert, it's physical in that
sense. And, since the identity value may be different depending on the
order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
the query optimizer may reorder however it likes), it really has nothing
whatsoever to do with the values in the row (or as Mr. Celko would want me
to think, nothing to do with the actual identity of the entity that the
table represents).
As a surrogate key, yes, they're awfully convenient. But I've found that
when I take the trouble to use real keys in my schema, all my code ends up
simplified, not complexified.|||Thanks very much Ross, for your translation of what Joe means when he says
'physical'. It drives me up the wall every time I read a message from him
telling someone that an IDENTITY is a physical locator for the physical
storage. I don't think Joe understands anything about SQL Server real
physical storage. But now knowing that Joe means something entirely
different when he uses that term, I will stop pulling my hair out. But we
will have to be ever vigilant to make sure new users know that when used by
Joe, 'physical' does not mean what they think it means.
Thanks again...
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:1xbcau9ino4a7$.dlg@.rpresser.invalid...
> On Fri, 4 Mar 2005 10:54:26 -0600, Louis Davidson wrote:
>
> Mr. Celko's use of the word "physical" is still a higher level than what
> most people think of as physical. It's higher than magnetic spins on the
> hard drive; higher than sectors on the hard drive; higher than bytes in
> the
> file on the filesystem; higher even than the structure inside a DAT file.
> Anything that can't be ported from one platform to the next strictly with
> SQL statements is "physical" in Mr. Celko's view, because it's part of the
> implementation that might change in the next release of the software.
> Since
> the Identity() attribute of a column is specific to MS SQL Server, and
> requires internal code to run at the time of insert, it's physical in that
> sense. And, since the identity value may be different depending on the
> order of inserts (think of an INSERT INTO tbl1 SELECT blah FROM tbl2 ...
> the query optimizer may reorder however it likes), it really has nothing
> whatsoever to do with the values in the row (or as Mr. Celko would want me
> to think, nothing to do with the actual identity of the entity that the
> table represents).
> As a surrogate key, yes, they're awfully convenient. But I've found that
> when I take the trouble to use real keys in my schema, all my code ends up
> simplified, not complexified.|||>> I'll add a suggestion for generating your order number (which you
Can you explain how and why it is beneficial to use a "smart key" for an
identifier? Are you aware of any drawbacks of using such "smart" or
intelligent keys?
Anith|||>> But we will have to be ever vigilant to make sure new users know that
Then OTOH, we will have to be aware that the ones using the terms "physical
table", "physical row", "physical column" etc do not mean what they think it
means either :-)
Anith|||Anith,
According to some (such as Louis, who replied earlier), the draw backs are:
> performance
> storage size
> they can change
But let's think about each of those. Are they "real" problems?
Performance. Did you know, your app can shave maybe 30ns if you forgo
database technology altogether. And loops (for, while, etc) -- you can easil
y
save a few clock cycles by not using them. Never accept "performance" as a
reason for doing something unless there are real world data to back this
claim up. I could make silly performance articles about AutoID as well -- th
e
system has to take extra cycles to generate the ID, check that you don't try
to insert it, etc.
Storage Size. The key I suggested was 8 bytes (YDDDSSSS). This would allow
for 999 orders a day for 10 years. That's well over 3 million orders. If you
wanted to do the same with an auto ID, you'd need a bigint (8 bytes).
Whoops. Let's compare a smaller key (YDDDSS, 6 bytes) versus int (4 bytes).
Even if we max out the int (at 2.15 Million), we save a whopping 4.3 Million
bytes. How about we just delete "solitare" instead of worrying about this?
They can change. Oh this is my favorite. How many times has amazon.com told
you "dear customer, we're sorry, but your order number has changed from
21040204-a34 to 24030204-a34." Find me a case where your PK will change, and
I'll show you a poorly designed system.
AutoIDs should remain in MS Access. They're good for one thing -- whipping
together a quick and dirty database. The whole point of "Relational"
databases is to allow data to relate to other data by the data iteself (keys
)
instead of these artificial AutoIDs.
-- Alex Papadimoulis
"Anith Sen" wrote:
> Can you explain how and why it is beneficial to use a "smart key" for an
> identifier? Are you aware of any drawbacks of using such "smart" or
> intelligent keys?
> --
> Anith
>
>|||I also react to the "academic" view of Primary Keys... One argument that
always irritates me is the idea that Identitys are bad because they have no
relationship or connection to the entity in the row... (It is not an
attribute in the data model) But then I will see it argued by the same
individual, that Social Security Number, or CustomerNo, or PartNumber (all
constructed artificuially by a third party) IS an appropriate key! (... But
order_id sounds like the natural key...)
Any value that uniquely identifies a row, imho, is a suitable candidate for
a Key. That value Must be constructed... either from meaningful data, or
from non-meaningful data. If you choose to construct it from meaningful dat
a
(Attributes in the data model) Then you ALWAYS have the problem of picking a
n
attribute (or set of attributes) whose values are least likely to change -
AND the isssue of propagating changes when the real-world values of the
attributes for that row DO change, (And they always will - because nothing i
n
the real-world is 100% fixed, no matter what the academics think.)
If you choose a non-meaningful key, how it is constructed - whether you use
a Identity, or some arbitrary algorithm, doesn't really matter, as long as
you can guarantee uniqueness. And Identities do that quite nicely.
"Anith Sen" wrote:
> Then OTOH, we will have to be aware that the ones using the terms "physica
l
> table", "physical row", "physical column" etc do not mean what they think
it
> means either :-)
> --
> Anith
>
>
PRIMARY KEY
Is there any difference between defining a primary key this way:
personID int not null
PRIMARY KEY CLUSTERED(personID)
and this way:
CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
'DAC,
With the first you're going to end up with a system generated name for the
PRIMARY KEY that will not match your environment's naming convention.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Well, in the second way you get to choose the name for the constraint.
The first syntax is called a 'column level constraint' and the second is
called a 'table level constraint'. The two you have shown here will behave
internally exactly the same way.
With a table level constraint, you can have a composite key; a column level
constraint is on a single column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||assuming that both are in valid statements, you get a randomly named key
in the 1st and a name of your choosing in the 2nd.
other than that, i don't believe so.
DazedAndConfused wrote:
>Is there any difference between defining a primary key this way:
>personID int not null
>PRIMARY KEY CLUSTERED(personID)
>and this way:
>CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
>'
>
>|||Yes. One has a system generated constraint name and the other has a user
specified constraint name. I prefer to use the user specified constraint
name because it makes it easier to coerce the optimizer to use the implicit
index for the primary key constraint--WITH(INDEX(PK_CP_personID))--and to
query sysobjects and sysindexes during troubleshooting.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Thank you,
When I go into entrerprise managers Diagram, under Indexs/Keys tab there is
a check box for Create Unique and two Radio buttons:
Constraint and Index
What is the difference between constraint and index?
How would I create an index and why?
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
> assuming that both are in valid statements, you get a randomly named key
> in the 1st and a name of your choosing in the 2nd.
> other than that, i don't believe so.
> DazedAndConfused wrote:
>|||I think you can create foreign key relationships if you use a unique
constraint instead of a unique index, but I'm not absolutely certain about
that. From a performance standpoint, there is no difference, because a
unique constraint always creates a unique index.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
is
> a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||A constraint is a logical construct. By defining a constraint you are
telling SQL Server how you want your data to behave, and how you want the
system to control it. A PK constrains the data to uniqueness, and
non-nullability. A unique constraint constrains the data to uniqueness.
Right now, SQL Server physically enforces your constraint requirements by
building an index, but theoretically, it could enforce the requirements in
another way.
An index is a physical construct that is primarily used for performance
reasons (but is also used to provide the enforcement of uniqueness
constraints). There are all kinds of reasons to build indexes, and you
should find a good book or site on SQL Server query tuning to get more
information about the design and use of indexes.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
> is a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||fyi: an FK can use either
Brian Selzer wrote:
>I think you can create foreign key relationships if you use a unique
>constraint instead of a unique index, but I'm not absolutely certain about
>that.
>
>|||Right now I have a lot of tables that have a foreign key constraint in a
column named "updateby" that must have an entry in the "users" table in the
"userID" column. Does that mean I have indexes all over the place? Am I
creating a mess and a lot of overhead? I am trying to find out if you can go
too far with relationships and constraints.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eS%23$$IuuFHA.3548@.tk2msftngp13.phx.gbl...
>A constraint is a logical construct. By defining a constraint you are
>telling SQL Server how you want your data to behave, and how you want the
>system to control it. A PK constrains the data to uniqueness, and
>non-nullability. A unique constraint constrains the data to uniqueness.
>Right now, SQL Server physically enforces your constraint requirements by
>building an index, but theoretically, it could enforce the requirements in
>another way.
> An index is a physical construct that is primarily used for performance
> reasons (but is also used to provide the enforcement of uniqueness
> constraints). There are all kinds of reasons to build indexes, and you
> should find a good book or site on SQL Server query tuning to get more
> information about the design and use of indexes.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
>
personID int not null
PRIMARY KEY CLUSTERED(personID)
and this way:
CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
'DAC,
With the first you're going to end up with a system generated name for the
PRIMARY KEY that will not match your environment's naming convention.
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Well, in the second way you get to choose the name for the constraint.
The first syntax is called a 'column level constraint' and the second is
called a 'table level constraint'. The two you have shown here will behave
internally exactly the same way.
With a table level constraint, you can have a composite key; a column level
constraint is on a single column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||assuming that both are in valid statements, you get a randomly named key
in the 1st and a name of your choosing in the 2nd.
other than that, i don't believe so.
DazedAndConfused wrote:
>Is there any difference between defining a primary key this way:
>personID int not null
>PRIMARY KEY CLUSTERED(personID)
>and this way:
>CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
>'
>
>|||Yes. One has a system generated constraint name and the other has a user
specified constraint name. I prefer to use the user specified constraint
name because it makes it easier to coerce the optimizer to use the implicit
index for the primary key constraint--WITH(INDEX(PK_CP_personID))--and to
query sysobjects and sysindexes during troubleshooting.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:e7aazttuFHA.3752@.TK2MSFTNGP09.phx.gbl...
> Is there any difference between defining a primary key this way:
> personID int not null
> PRIMARY KEY CLUSTERED(personID)
> and this way:
> CONSTRAINT PK_CP_personID PRIMARY KEY CLUSTERED(personID)
> '
>|||Thank you,
When I go into entrerprise managers Diagram, under Indexs/Keys tab there is
a check box for Create Unique and two Radio buttons:
Constraint and Index
What is the difference between constraint and index?
How would I create an index and why?
"Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
> assuming that both are in valid statements, you get a randomly named key
> in the 1st and a name of your choosing in the 2nd.
> other than that, i don't believe so.
> DazedAndConfused wrote:
>|||I think you can create foreign key relationships if you use a unique
constraint instead of a unique index, but I'm not absolutely certain about
that. From a performance standpoint, there is no difference, because a
unique constraint always creates a unique index.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
is
> a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||A constraint is a logical construct. By defining a constraint you are
telling SQL Server how you want your data to behave, and how you want the
system to control it. A PK constrains the data to uniqueness, and
non-nullability. A unique constraint constrains the data to uniqueness.
Right now, SQL Server physically enforces your constraint requirements by
building an index, but theoretically, it could enforce the requirements in
another way.
An index is a physical construct that is primarily used for performance
reasons (but is also used to provide the enforcement of uniqueness
constraints). There are all kinds of reasons to build indexes, and you
should find a good book or site on SQL Server query tuning to get more
information about the design and use of indexes.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Thank you,
> When I go into entrerprise managers Diagram, under Indexs/Keys tab there
> is a check box for Create Unique and two Radio buttons:
> Constraint and Index
> What is the difference between constraint and index?
> How would I create an index and why?
>
> "Trey Walpole" <treypoNOle@.comSPAMcast.net> wrote in message
> news:eXKz5ztuFHA.728@.TK2MSFTNGP10.phx.gbl...
>|||fyi: an FK can use either
Brian Selzer wrote:
>I think you can create foreign key relationships if you use a unique
>constraint instead of a unique index, but I'm not absolutely certain about
>that.
>
>|||Right now I have a lot of tables that have a foreign key constraint in a
column named "updateby" that must have an entry in the "users" table in the
"userID" column. Does that mean I have indexes all over the place? Am I
creating a mess and a lot of overhead? I am trying to find out if you can go
too far with relationships and constraints.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eS%23$$IuuFHA.3548@.tk2msftngp13.phx.gbl...
>A constraint is a logical construct. By defining a constraint you are
>telling SQL Server how you want your data to behave, and how you want the
>system to control it. A PK constrains the data to uniqueness, and
>non-nullability. A unique constraint constrains the data to uniqueness.
>Right now, SQL Server physically enforces your constraint requirements by
>building an index, but theoretically, it could enforce the requirements in
>another way.
> An index is a physical construct that is primarily used for performance
> reasons (but is also used to provide the enforcement of uniqueness
> constraints). There are all kinds of reasons to build indexes, and you
> should find a good book or site on SQL Server query tuning to get more
> information about the design and use of indexes.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHcGY$tuFHA.3676@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)