HI,

just to make sure I understand:

You could essentially retrieve that unique record by using just 1 or 2 columns in your WHERE clause.

E.g. SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123

But the question is then: Would the performance of the query (i.e. the time it takes to return data) improve if you would add a bunch of columns to the WHERE clause to retrieve the same row, e.g.:

SELECT EMP_ID, NAME FROM STAFF_LIST WHERE EMP_ID = 123 AND FIRST_NAME = 'Eric' AND SURNAME = 'Mackin' AND EMP_ROLE = 'Analyst'

Although looking at the query execution plan and the associated stats would provide you with the answer, I would suggest that the more columns are referenced in your SELECT clause or WHERE clause, then the more data has to be transported over the network, the mroe data fetched from the disk, and the more CPU cycles to processes the extended clauses.

I think that the second query would be equivalent (performance/execution plan wise) to:

SELECT EMP_ID, NAME, FIRST_NAME, SURNAME, EMP_ROLE FROM STAFF_LIST WHERE EMP_ID = 123

When it comes to the number of indexes on your table(s) - don't necessarily believe that the more indexes you have, the more your performance will suffer - although there is surely a happy mid point somewhere.

As it goes, the right answer is "it depends" - is your table heavily written to and read relatively infrequently? then small number of indexes is what you want as every write to the table involves a write to the indexes too. Is your table read from very often but not written to very often? then load the table up with indexes.

HTH,

B