CODEWITHSUNDEEP
sql-server-2005indexing
Marek Kwiendacz
Marek Kwiendacz

Reputation: 9854

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 smaller than 500 bytes, so I think that it could be stored in server memory (it is answer to Paul Sanwald note)

Upvotes: 5

Views: 1298

Answers (4)

HLGEM
HLGEM

Reputation: 96590

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.

Upvotes: 2

Paul Sanwald
Paul Sanwald

Reputation: 11339

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.

Upvotes: 0

kemiller2002
kemiller2002

Reputation: 115508

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

Upvotes: 0

Aaron Digulla
Aaron Digulla

Reputation: 328644

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.

Upvotes: 2

Related Questions