Deciding on the indexes using the query

Question

I have a table named "Repayment" which has borrower_id as the primary key. I need to decide on the indexes that needs to created on this table. I have the following queries and I have come up with the indexes. I need to know if these indexes are correct or if there is a better way.

SELECT DISTINCT name, address
FROM Repayment
WHERE borrower_id = 4;

create index idxrep on repayment(borrower_id) include(name,address);

---

SELECT *
FROM Repayment
WHERE borrower_id = ? AND repayment_date > '11-12-2014';

create idxborrrep on repayment(borrower_id,repayment_date);

---

SELECT borrower_id, loanamount
FROM Repayment
WHERE loanamount BETWEEN ? AND ?;

create indxloanamount on repayment(loanamount) include(borrower_id,loanamount);

---

Answer 1

Your indexes look fine for your queries. You can reduce the three indexes to two mostly covering indexes:

create idxborrrep on repayment(borrower_id, repayment_date) include (name, address);

create indxloanamount on repayment(loanamount) include(borrower_id,loanamount);

I would question the inclusion of name and address columns in the index. These can be rather large, so the overhead of looking them up in the data pages is perhaps less than the increase in size on the index. However, this is most likely a very minor point, either way.