Sunday, February 12, 2012

CLUSTERED INDEX or NONCLUSTERED

I have 3 table A, B, C

Table A (15 field, 4 fields indexed and Primary Key) approximate rows: 50.000 60.000

Table B (18 field, 6 fields indexed and Primary Key) approximate rows: 350.000 500.000

Table C (16 filed, 9 fields indexed and Primary Key) approximate rows: 500.000 1.000.000

Structure is something like this:
A (master) --> B (detail) --> C (sub detail)

On each 3 table is added new record, in table C the record is added after a search in table B.
My question is: Which is the best method? CLUSTERED INDEX or NONCLUSTERED INDEX

Thanks
Sorry for my englishIt is not clear about relations between tables (number of fields, etc.) by anyway clustered index for PK and nonclustered for others will be OK.|||Not enough info, these links may help you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TuningofaDifferentSort.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_5h6b.asp|||Thank you for your answer.
The diagram is attached, form left to right table A; B; C|||The diagram|||Still not enough info. Some questions:

What is your ratio of inserts to queries? Are you heavy insert or heavy queries or both?

What is typically used for your select criterias?

I would reccomend you start with reading those articles and you may play around with "set statistics IO on" to evaluate your logical IO when you have added a clustered index, taken it off, added a nonclustered index, etc. This to me is the best advice to become self sufficient on indexing questions.

HTH

No comments:

Post a Comment