Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

clustered index

Posted on 2009-05-10
13
Medium Priority
?
531 Views
Last Modified: 2013-11-11
1) Keys: When to cluster a key?
2) Indexes: When to cluster an index?
3) Does Cluster on index improve performance?
4) Should all index be clustered?
0
Comment
Question by:robertkc
[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
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24351388
refer few of these articles
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
http://beginner-sql-tutorial.com/sql-index.htm
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Usually the indexes, with some exceptions, will improve data retrival, but it can affect the INSERT operations
0
 
LVL 6

Accepted Solution

by:
bull_rider earned 1000 total points
ID: 24351455
Indexes are mainly used for faster retrival of the data from the table or view. Primary key is best candidate for creation of clustered key: reason all the values in Primary Key column would be unique. Also you can create clustered index on foreign key column of table which is used quite frequently in the join
condition and on the column which is used frequently in the where clause of the table.

Yes of course it increases performance in running queries which do not include DML statements. Indexes are costly in DML statements.

A table can have 1 clustered index and 249 non-clustered indexes.
Generally clustered index is ideal for the Primary Key and non-clustered indexes are ideal for columns which are used in sorting (the columns in Order By)

Hope this answers all your questions and let me know if you have any doubts in this explanation.
0
 

Author Comment

by:robertkc
ID: 24351755
Want unique AccountNumber and unique UserID.

Which is correct A or B.


A)
---
Create table dbo.Account (
 
etc...
 
constraint AK_KEY_1 unique (AccountNumber),
constraint AK_KEY_2 unique (UserID)
)
 
create unique index Index_1 on dbo.Account (
AccountNumber ASC
)
create unique index Index_2 on dbo.Account (
User ASC
)
 
 
B)
---
Create table dbo.Account (
 
etc...
constraint AK_KEY_1 unique (AccountNumber, UserID)
)
 
create unique index Index_1 on dbo.Account (
AccountNumber ASC,
UserID ASC
)

Open in new window

0
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.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24351773
well, you can create AccountNumber as Primary key and userid could be unique index. In your current scenario, I would like you to go for option B.
0
 
LVL 6

Expert Comment

by:bull_rider
ID: 24351795
Same here as RiteshShah. I would also want you to opt for the option B. Hope you got a clear understanding of indexes from the xplanation given by all experts. Let me know if you have any more questions.
0
 

Author Comment

by:robertkc
ID: 24351893
why B? and not A?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24351900
because composite is better than separate.
0
 

Author Comment

by:robertkc
ID: 24351958
1 problem, UserID can change. so it should not be a index?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24351965
>>1 problem, UserID can change. so it should not be a index?<<

didn't get you 100% but all I can say is, there is no problem if you keep changing the value in indexed field.
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 1000 total points
ID: 24352016
In both A and B you have created a unique constraint AND an index on the SAME columns. That isn't a good idea because a constraint creates an index anyway, so you have a redundant index which only harms insert/update/delete performance and wastes space.

I don't agree with the previous comment that "Primary key is best candidate for creation of clustered key". It depends a lot on what the primary key is. If new keys aren't inserted in any particular order then clustering on that key may result in lots of page splits and fragmentation, which is generally something you want to avoid. There is no simple rule for selecting a clustered index. You may need to weigh up a range of different factors. Some tables may be better off without a clustered index at all.
0
 

Author Comment

by:robertkc
ID: 24352038
sorry i meant to option B)
contains an alternative key userid which can change.

constraint AK_KEY_1 unique (AccountNumber, UserID)
)


OK?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24352040
dportas,

>>I don't agree with the previous comment that "Primary key is best candidate for creation of clustered key"<<

I told him to create PK for Account Number which suppose to be unique, isn't it good idea to create PK on that field?
0
 
LVL 22

Expert Comment

by:dportas
ID: 24352171
I wasn't commenting on what the PK should be in this case. I only meant that a primary key isn't necessarily the best choice for a clustered index.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 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