Should I add indices to small table?

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 small开发者_C百科er than 500 bytes, so I think that it could be stored in server memory (it is answer to Paul Sanwald note)

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.

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.

When you make a primary key on the table, it will create a clustered index (assuming you haven't already created another clustered index on the table already. There can be only one.) Since you are talking about referencing it on other tables, I would assume that you will do this. So to answer your question, it is not necessary to create an index, because one will be created for you, assuming that you are using the primary key to look up information.

Sql Server Clustered Indexes

probably not, but it does depend at least partially on how many columns your table has, and what type those columns are. If your rows were too big to be stored in memory, but the index could be, then it could make sense to have an index on a subset of columns that are frequently used in your queries.