33

Is there any reason I shouldn't create an index for every one of my database tables, as a means of increasing performance? It would seem there must be some reason(s) else all tables would automatically have one by default.

I use MS SQL Server 2016.

Stpete111
  • 3,109
  • 4
  • 34
  • 74
  • Simple answer is when there is more `INSERT/UPDATE/DELETE` than `SELECT` in your table then creating `Index` on that table may not be effective – Pரதீப் Dec 31 '16 at 17:37
  • Other minuses would be increased storage requirements (for the indexes themselves), possibly index maintenance plans. Do you plan on creating indexes in 2017? – Radu Gheorghiu Dec 31 '16 at 17:39
  • please check the following links 1. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bd9b36db-7b70-43b8-a971-f6c881cde269/disadvantages-of-clustered-and-nonclustered-indexes?forum=sqlgetstarted 2. http://stackoverflow.com/questions/764210/what-are-the-disadvantages-of-having-many-indices – ps_prakash02 Dec 31 '16 at 17:39

3 Answers3

51

One index on a table is not a big deal. You automatically have an index on columns (or combinations of columns) that are primary keys or declared as unique.

There is some overhead to an index. The index itself occupies space on disk and memory (when used). So, if space or memory are issues then too many indexes could be a problem. When data is inserted/updated/deleted, then the index needs to be maintained as well as the original data. This slows down updates and locks the tables (or parts of the tables), which can affect query processing.

A small number of indexes on each table are reasonable. These should be designed with the typical query load in mind. If you index every column in every table, then data modifications would slow down. If your data is static, then this is not an issue. However, eating up all the memory with indexes could be an issue.

TT.
  • 15,774
  • 6
  • 47
  • 88
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 3
    Too many indexes also add compilation overhead and can increase the likelihood of deadlocks due to different data access paths. – Dan Guzman Dec 31 '16 at 19:04
  • 1
    @TT. . . . You are correct and I learned something: https://en.oxforddictionaries.com/explore/number-of-people-is-or-are. – Gordon Linoff Jan 02 '17 at 01:15
  • @GordonLinoff Yeah, I was little too eager to correct that, but I had a tingling doubt. Looked it up and it was correct as you wrote it initially :). HNY! – TT. Jan 02 '17 at 09:16
17

Advantage of having an index

  • Read speed: Faster SELECT when that column is in WHERE clause

Disadvantages of having an index

  • Space: Additional disk/memory space needed
  • Write speed: Slower INSERT / UPDATE / DELETE
Martin Thoma
  • 124,992
  • 159
  • 614
  • 958
2

As a minimum I would normally recommend having at least 1 index per table, this would be automatically created on your tables primary key, for example an IDENTITY column. Then foreign keys would normally benefit from an index, this will need to be created manually. Other columns that are frequently included in WHERE clauses should be indexed, especially if they contain lots of unique values. The benefit of indexing columns, such as gender (low-cardinality) when this only has 2 values is debatable. Most of the tables in my databases have between 1 and 4 indexes, depending on the data in the table and how this data is retrieved.

Tim Newton
  • 817
  • 1
  • 11
  • 24