1

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 :(

1
  • 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, 2011 at 8:01

1 Answer 1

4

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:

1
  • 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
    – Persk
    Nov 10, 2011 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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