I have an index on EmpId. I wanted to include a few columns to the index.

Create Index IX_01_EmpId On empTable(EmpId) INCLUDE (name, dept, salary, joining_dt). 

I know that included columns speed up the query by avoiding key lookups. How do they affect the performance of the Insert/Update/Delete statements? What happens behind the scenes to the index table when any of the Insert/Update/Delete statements are performed on the table?

CodePudding user response：

IMO, the performance impact is more on the SELECT side.

When SELECTing, you might use the index. If you do many queries which show these columns, and you have included them in the index, it will avoid key lookups as you said. This comes at the cost that each page (SQL stores everything in 8KB pages) of the index now contains less rows, since they are bigger. Thus, the same query that uses an index will take longer, as it will have to scan(seek) more pages. The more columns included, the stronger this effect will be.

When you perform INSERT/UPDATE/DELETE, SQL server has to maintain this index. It will modify the page data accordingly. I'm betting that as long as it has to insert/update/delete a "row" from the index and touch its page, it will not make a large difference really.