Solved

unique nonclustered index on the primary key.

Posted on 2011-02-18
8
559 Views
Last Modified: 2012-06-27
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
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 150 total points
ID: 34926237
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
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 150 total points
ID: 34926396
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
 

Author Comment

by:Mr_Shaw
ID: 34926913
JoeNuvo,

The where clause could change depending what query is run.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 80 total points
ID: 34931175
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
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 120 total points
ID: 34932794
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
 

Author Closing Comment

by:Mr_Shaw
ID: 34934680
thanks
0
 
LVL 22

Expert Comment

by:dportas
ID: 34949774
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34952765
>>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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question