I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clusterin
g
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set u
p
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys tha
t
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
Showing posts with label keys. Show all posts
Showing posts with label keys. Show all posts
Wednesday, March 7, 2012
PK Index Cluster/Non Cluster
I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
PK Index Cluster/Non Cluster
I have application that I inherited. Looks to me the indexes where set up
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
were set up with Identity Incremented by 1.
I was told the primary keys that are increasing in numerical value should be
a cluster index.
Is there a list of rules for best practices on setting up Cluster and
Non-Indexes?
Thanks,
Most of the time yes, it probably would be clustered. But just because it's
not doesnt mean its incorrect. In a reporting server, you may want clustering
done on date fields, as thats what most queries are run against and that
could greatly speed stuff up.
TIA,
ChrisR
"Joe K." wrote:
> I have application that I inherited. Looks to me the indexes where set up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
|||Here's my shortlist of reasons why identities make for excellent clustered
indexes:
(a) They're narrow - because the keys of a clustered index CIX) are also
stored in the leaf nodes of all non-clustered indexes (NCIX), the narrower
the CIX keys, the less size impact they'll have on the size of the NCIX's.
Identities can be any of the integer types, sos their size can vary, but in
general, a 4 byte integer key makes for a nice compact key.
(b) Unique - SQL Server "uniquefies" non-unique values in CIXs with 8 byte
uniquefiers. Because identities are reasonably unique, they don't suffer
from this problem.
(c) Incremental - because identity columns are generally incremental in
nature, you typically don't suffer much from fragmentation. Non-incremental
keys (eg, customername) can heavily fragment a database during insert /
update / delete operations
(d) Stable - if a CIX key gets updated, any associated NCIXs (on the same
table) also have to be updated to synchronise their CIX bookmark keys (as
described in (a) )
Regards,
Greg Linwood
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:7E115D53-5D47-410E-9900-FE935FE397D6@.microsoft.com...
> I have application that I inherited. Looks to me the indexes where set
> up
> incorrectly. Numerous tables have Non-Cluster indexes as Primary Keys
> that
> were set up with Identity Incremented by 1.
> I was told the primary keys that are increasing in numerical value should
> be
> a cluster index.
> Is there a list of rules for best practices on setting up Cluster and
> Non-Indexes?
> Thanks,
PK
Are timestamp columns good candidates for primary keys? Why?Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
PK
Are timestamp columns good candidates for primary keys? Why?
Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>
|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>
|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
PK
Are timestamp columns good candidates for primary keys? Why?Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Are timestamp columns good candidates for primary keys? Why?
>>
>>
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Are timestamp columns good candidates for primary keys? Why?
>>
>>
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to:
Posts (Atom)