Index problem! Please guide

Question

Post reply

Index problem! Please guide

Jai-448139

SSC Eights!

Points: 818
More actions
November 1, 2007 at 4:22 am

#178918

example 1
Table Name
Util.CdDede
Cluster Index
CodeID (This is a pK Field with indentity col created by default)
Select where condition includes
CreatedDate,CodeName
example 2
Table Name
Util.UserDetail
Cluster Index
UserID(This is a pK Field with indentity col created by default)
Non Cluster Index
NRIC, UserLogin (because they are unique fields created by default)
Select where condition includes is
DisabledDate,CompanyID,UserID
sort by field
DisabledDate
Dear all,
i want to know, how to set cluster and non cluster index for these tables and what basis.
the one's which are created by default as cluster and non cluster.
should i keep the same or how i should decide. please guide me as this feedback i will apply for my rest of the table.
and the database tuning wizard are not working fine for me. any help will be appreciated.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 1

Hi,

Generally the primary key is the clustered index (just one clustered index per table).

Take a look at http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

Ahmed

ARUL-285958 Ten Centuries Points: 1081 More actions · Answer 2

This got to do a lot with the requirement, data storage and Db design.... do not create indexes cluster/non-cluster just for the sake of creating indexes. We ran into trouble by adhoc DB designing and had indexes everywhere but it burnt us in the end.

So, my piece of advise is to get a logical DB design, study the future plans and requirements and pick up what to do...

Get busy living ....or get busy dying....

Bob Fazio SSChampion Points: 10794 More actions · Answer 3

If you have an identity field PK, then it should be your clustered index. There are many reasons why, but simple one is data is organized in the table by the index. If you are using an identity column, the data is being inserted in the order of the key.

To develop Non-Clustered indexes the following:

If you have foreign keys, you should in most cases have an index on the columns in the order of the key (assuming more than 1).

Don't create 2 indexes with the same columns in different orders. This can be done, but I do discourage it.

Index column order is more based upon the following if ALL columns are always part of the where clause.

1) The most discriminating (more distinct values) columns should be first in most cases

a)Fixed lenght fields if they are close in discrimination should be first rather than varchars

2) Don't bother with fields that have less than 5 distinct values

a) If the distribution is lopsided this rule can be ignored if it is common to go after the smaller subsets.

i) Basically if you select more than 20% of the rows based upon this key, it in most cases more trouble than it is worth.

Lastly. Try different combinations, and see which ones work best in real testing.

Bob Fazio SSChampion Points: 10794 More actions · Answer 4

I forgot to add.

If you have several queries (which most people do) create an index based upon the more common set of those fields.

Example:

select where a,b,c

select where b,c,d

select where c,d,a

lets say that the following number of distinct columns exist.

a - 50

b - 200

c - 5

d - 10

Based upon distinct values I would do the following

b,a,d,c

However since all of the queries use c

c,b,a,d would be better

unfortunately Query 3 can only use c in that index, so let's ignore it for this.. It will get it's own index

So it ends up with

b,c,a

and

d,c,a

Jai-448139 SSC Eights! Points: 818 More actions · Answer 5

Thanks for ur kind help, will come back to you if i have more doubts

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Bob Fazio (11/2/2007)
I forgot to add.
If you have several queries (which most people do) create an index based upon the more common set of those fields.

What a nice, short, to-the-point explanation of how to analyze and select indexes. Well done, Bob!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

Thanks Jeff!

Even the blind squirrel occasionally finds a nut 🙂