Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

unique nonclustered index on the primary key.

The book SQL Server 2008 Query Performance Tuning Distilled (Page 118) suggests using unique nonclustered index on the primary key.

I have never thought of doing this...
From what I have read this sound a good idea, but the following link says this will effect  DAO and ADO.
here is the link:
http://bytes.com/topic/sql-server/answers/81459-why-cluster-primary-key
Does anybody have any comments?
0
Mr_Shaw
Asked:
Mr_Shaw
4 Solutions
 
knightEknightCommented:
I'm assuming the PK is a numeric IDENTITY column...

It depends on your throughput and usage.  If the PK of the table is used often in the WHERE clause of your queries, then clustering it will improve query performance.  Otherwise it may be better to make the clustered index on the most selectable column used in your WHERE clauses.

However, if there are millions of new rows inserted into the table each day (many more inserts than selects), then it will help overall server performance to make your clustered index on the most selectable column, as opposed to the PK - even if the PK is used in your WHERE clauses.  Note I said "overall server performance" as opposed to "query performance".  
0
 
JoeNuvoCommented:
I'm actually do that myself for put something else as clustered index rather than primary "identity" key.
Since on that system, real time monitoring is focus on "per location -vs- category" rather than "per receipt_id"
true also for perform "balance clearing"

so, put clustered index on field matching with "where" condition, help on performance.
since server just perform "clustered index seek",  instead of "index seek" + "key lookup"

anyway, IMHO, this should depend on each system requirement.
0
 
Mr_ShawAuthor Commented:
JoeNuvo,

The where clause could change depending what query is run.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Anthony PerkinsCommented:
I suspect you are thinking about it the wrong way.  The clustered index should be unique, narrow and static.  If the primary key complies with that than make the Primary Key clustered.  It does not get more complicated than that.
0
 
Aaron ShiloCommented:
hi
folowo this basic rule

1. Best Practices for Clustered Indexes

Large amount of selects on a table, create a clustered index on the primary key of the table. Then create non-clustered indexes for all other columns used in selects and searches. Put non-clustered indexes on foreign key/primary key columns that are used in joins

2. Best Practices for Non-clustered Indexes

Add non-clustered indexes for queries that return smaller result sets. Large results will have to read more table pages anyway so they will not benefit as much from a non-clustered index.
Add to columns used in WHERE clauses that return exact matches.
If a clustered index is not used on these columns, add an index for collections of distinct values that are commonly queried such as a first and last name column group.
Add for all columns grouped together for a given query that is expensive or very common on a large data table.
Add to foreign-key columns where joins are common that are not covered by the clustered index.


and allways remember : Indexes are a lot of "trial and error" depending on your database design, SQL queries, and database size
0
 
Mr_ShawAuthor Commented:
thanks
0
 
dportasCommented:
It depends. It's pretty arbitrary to say the primary key should/should not be clustered without knowing what that key is and how it is used. DAO / ADO? Does anyone really still use those? :)
0
 
Anthony PerkinsCommented:
>>It's pretty arbitrary to say the primary key should/should not be clustered without knowing what that key is and how it is used. <<
Absolutely.

>>DAO / ADO? Does anyone really still use those?<<
It is quite amazing how long they have lasted.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now