Adding indexes (both clustered and nonclusterd) will decrease the amount of time that your INSERT, UPDATE and DELETE statement take

The side effect of indexes is related to the cost of INSERT, UPDATE, MERGE and DELETE statements. Such statements can take longer to execute in the presence of indexes since they alter the data on the table resulting the update of the indexes too. Imagine the situation of an INSERT statement that has to add rows to a table with a clustered index. Table rows may need to be repositioned since clustered index needs to order the data pages themselves thus creating more overhead. So, it is crucial to take into account the overhead of INSERT, UPDATE and DELETE statements before designing your indexing strategy. Although there is an overhead in the above statements, you have to take into account that many times an UPDATE or DELETE statement will have to execute in a subset of data, defined by a WHERE clause, were indexing may outweigh the additional cost of index updates since SQL server has to find the data before updating them.