Solved

unique nonclustered index on the primary key.

Posted on 2011-02-18
8
557 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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