Should I add indices to small table?

Question

Is there any reason to add indices to table with few (less than 10) elements? It is frequently used in joins, but I'm not sure if index on such small table increase its performance or decrease.

Edit: rows in table are quite small: 7 columns, with total size smaller than 500 bytes, so I think that it could be stored in server memory (it is answer to Paul Sanwald note)

Answer 1

It is unlikely that a table that size will ever use the index. If the table will be stable in size and always be small, I would not add indexes.

From Books Online:

Indexing small tables may not be optimal because it can take the query optimizer longer to traverse the index searching for data than to perform a simple table scan. Therefore, indexes on small tables might never be used, but must still be maintained as data in the table changes.

So it likely to be a poor idea to index small tables.

Answer 2

As with all performance questions, the answer is: Create a performance test and measure the results.

Otherwise, there is a 90% chance the answer is wrong.