Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have bank transaction table with columns

Division
Brcode
TrDate
ProjectCode
Accountnumber
EffectiveDate
DrCr
Amount
EndBalance

In the other table the Brcode itself is unique and is assigned under only 1 division.

I am planing to set Division,Brcode,TrDate as cluster index(composite) and set each of them as non-cluster index individually.

What I curious is

  1. since Brcode is unique for all division so clustering Division,Brcode,TrDate is implicitly clustering Brcode,does Brcode index get benefit from this? Does it need to search all division?

  2. If I search by TrDate it won't get benefit from cluster index,is that right? this column shouldn't include in composite?

  3. If I search by TrDate will cause full table scan or index scan?

ps.this table have 7 millions record so even minor factor will cause a huge different :(

share|improve this question
1  
What datatypes are your columns?? Your clustering key should be as small as possible - best is an INT IDENTITY. You should avoid wide clustering keys at all costs; also try to avoid variable-length columns in a clustering key, and avoid any columns that might change their values - all those factors are horribly bad in a clustering key. –  marc_s Nov 10 '11 at 8:01
add comment

migrated from stackoverflow.com Nov 10 '11 at 8:27

This question came from our site for professional and enthusiast programmers.

1 Answer

First off, don't mix "primary key" and "clustered index". One is "how to identify a row", the other is "on-disk structure"

Then, you don't need an index on Division by itself because it is the left hand column of the clustered index. However, it won't be covering (see links below)

And for each point in turn:

  1. this table doesn't know that "Brcode is unique for all division". If it is, then brCode should be first and unique and the clustered index by itself
  2. a search on TrDate won't benefit from the clustered index
  3. it depends on selectivity, statistics, how many columns are required. It may seek with a key lookup or it may scan the clustered index (= a table scan) or scan/seek the index on TrDate

Finally, I'd suggest some background reading:

share|improve this answer
    
this table doesn't have any key because bank transaction can only identify by time (which is not include in table) but it need proper index since it always have been used and has millions of record. It is usually searched by accountnumber,occasionally by brcode or division so brcode and accountnumber is the way to go? I'm new to SA and DBA,I'm reading ur link.thx a lot –  thReality Nov 10 '11 at 8:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.