Indexes

[sagana]

I am new to SQL and would appreciate some advice on the best way of indexing the following.

I have a general ledger table containing account details that are unique on a combination of account number, branch, sub-account.

The transaction file's main searchable criteria are account number, branch, sub-account and date.

1. Should the GL table have a Primary,Clustered index on a concatanation of account number, branch, sub-account; or just a Primary index on the concatanation; or should I create a column to contain the concatanated expression and use that as the Primary index.

2. To efficiently search the transaction table is it better to have four separate indexes account number, branch, sub-account and date or a complex index on their concatanation; or a complex index on the concatanation of account number, branch, sub-account and a separate index on date?

Many thanks.

[skhanal]

1. Create a primary key on account number, branch, sub-account

Do not create a new column with concatenated data.

2. Creating an index on non-selective column like branch may not help unless you have to select every account from a branch, in that case it is a good candidate for a clustered index.

An index on date may be useful too.