What are advantages and disadvantages of indexing in database?

Advantages

• Speed up SELECT query
• Helps to make a row unique or without duplicates(primary,unique) 
• If index is set to fill-text index, then we can search against large string values. for example to find a word from a sentence etc.

Disadvantages

• Indexes take additional disk space.
• indexes slow down INSERT,UPDATE and DELETE, but will speed up UPDATE if the WHERE condition has an indexed field.  INSERT, UPDATE and DELETE becomes slower because on each operation the indexes must also be updated. 

The advantages of indexes are as follows:

• Their use in queries usually results in much better performance.
• They make it possible to quickly retrieve (fetch) data.
• They can be used for sorting. A post-fetch-sort operation can be eliminated.
• Unique indexes guarantee uniquely identifiable records in the database.

The disadvantages of indexes are as follows:

• They decrease performance on inserts, updates, and deletes.
• They take up space (this increases with the number of fields used and the length of the fields).
• Some databases will monocase values in fields that are indexed.

You should only create indexes when they are actually needed.

Advantages:

1. The most important use for an index is in finding a record or set of records matching a WHERE clause.

It could fasten Update and Delete command by means of where clause in non-clustered index.

1. When there are no indexes, the database will scan the table and then sort the rows to process the query. However, the index will provide the database with already sorted list of table’s columns. The database can simply scan the index from the first record to the last record and retrieve the rows in sorted order. 

2. We can use a GROUP BY clause to group records and aggregate values, e.g. for counting the number of customers (table) in a country (column). To process a query with a GROUP BY clause, the database will quite often sort the results on the columns included in the GROUP BY. The following query counts the number of customers from every country with the same UnitPrice value. SELECT Count(*) FROM Products GROUP BY UnitPrice.

3.  Index is the best tool for the database to use to enforce uniqueness in data of a column. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values. Indexes, as we should know by now, will improve this search time.

Disadvantages:

1. Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. (To see the space required for a table, use the sp_spaceused system stored procedure in a query window.) 

2. If the data is modified on regular intervals then database engine requires updating all the indexes, thus too many indexes will slows down the performance. 

Also if we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. In this sense, it’ll slowdown Update and Delete command.

1. The disadvantage of a non-clustered index is that it is slightly slower than a clustered index and they can take up quite a bit of space on the disk. 

2. Irrespective of frequently modifying database, too many indexes can actually slow your database down. Thinking of a book again, imagine if every "the", "and" or "at" was included in the index. That would stop the indemx being useful - the index becomes as big as the text!