Do wide included column indexes affect inserts?

Question

I had to create indexes with included columns due to high I/O, nested loops joins, and key look ups that blocked inserts:

SELECT * FROM Table WHERE (Column1 = 'ED69K')    

I created an index on Column1 with 20 included columns for the rest of the columns in the select list.

SELECT TOP (50000) * FROM Table WHERE datetime = 6/10/2021

Create index on Datetime with 20 included columns for the rest of the columns in the select list.

The above two indexes reduced nested loops joins and key lookups, but at the same time increased the database space.

Now since the indexes are both identical, with only the key columns changing, will the inserts now take twice the time?

I have a clustered Index on the ID column, but the where clause is not filtering by the ID. The optimizer uses the cluster index to find rows which are not in the non clustered index.

Answer 1

As I understand it, you created two non-clustered indexes and in order to cover the query, you have all the columns from the table in these indexed (the key is the key and the other columns are included columns).

If above is correct, you now have two more copies of the table table, sort of. I.e., three instances of the same data. You have the clustered index that is the table and is sorted by the clustered key. Then you have all columns again, twice, in each of the non-clustered indexes. The table has now tripled in size.

So, your creation of the non-clustered indexes causes the modifications to be two times more expensive compared to earlier. Or even a bit more than that since you likely now in the non-clustered indexes will experience more page-splits compared to earlier and you pay the penalty for that as well when it happens (including the transaction-logging of the data-movement when the split occurs).

Answer 2

Included columns are more data that needs to be maintained in the index, so yes, there's likely at least a small additional overhead for INSERT operations with such a wide index.

Why not try creating your clustered index on (datetime, Column1), it doesn't have to be on your table's primary key. In fact, the primary key can have a nonclustered index on it instead, if that's something frequently queried on in other ways.

This may improve performance by removing key lookups in the two example queries you provided, and potentially leveraging a clustered index seek operation when applicable.

Check your performance via comparing execution plans and time and I/O statistics of both implementations, as YMMV, and that's a few ways to compare in practice.