Solved

unique nonclustered index on the primary key.

Posted on 2011-02-18
8
551 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
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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now