[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

unique nonclustered index on the primary key.

Posted on 2011-02-18
8
Medium Priority
?
576 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 600 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 600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 320 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 480 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

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.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

650 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