-1

I would like to create a covering index to improve query performance. I do know that this one more index will impact INSERT performance.

The table has only INSERT operations no UPDATE or DELETE. The data in covering index will unique because the index keys contain the table's PK, so I need no further constraining uniqueness, my only goal to improve query performance.

Question

Which type of index will the optimal (means degrade less) the INSERT performance, unique or not unique?

g.pickardou
  • 32,346
  • 36
  • 123
  • 268
  • "I do know that this one more index will impact INSERT performance." - the impact of a single index on `INSERT` performance is, in my experience, negligible. There's a lot of very old (mis)information about DML performance dating from the 1990s still being spread around online. The only way to know for sure is by doing your own profiling. Also, be mindful of things like index-fragmentation - a recent RDBMS build running a well-maintained database on commodity hardware today should be able to handle thousands of DML statements on kilobyte-sized rows every second. – Dai Jan 24 '21 at 14:17
  • "Which type of index will the optimal (means degrade less)" - what do you mean by "degrade"? If you're referring to index-fragmentation caused by inserts, then if you aren't doing _any_ deletes and your indexed columns (not `INCLUDE` columns) are monotonically increasing then (theoretically) there shouldn't be _any_ internal fragmentation or "degrading". – Dai Jan 24 '21 at 14:20
  • 2
    It would be best if you posted your **actual** queries, `CREATE TABLE` statement, and those queries' execution-plans (a screenshot of the plan will do) - otherwise all we can do is speculate. Also, are you doing individual `INSERT` statements or multi-row inserts? Are you employing any locks (table locks, updlocks, holdlocks, etc) when running transactions? etc – Dai Jan 24 '21 at 14:21
  • An ever-increasing identity column would be pretty performant. If you are on SQL Server 2019, use `optimize_for_sequential_key = on` hint – Charlieface Jan 24 '21 at 14:56
  • @Dai: You are right, I should have provided exact CREAT and and SELECTs. On degrade I mean : performance degradation of INSERT operations. The table has an Identity PK and a clustered index for PK – g.pickardou Jan 24 '21 at 16:29

4 Answers4

1

This depends on your definition of "degrade". If you're talking about indexing the same column in either case, and are able to use the UNIQUE keyword because the column will always contain a unique set of values, then it can be a little more performance optimal to specify that keyword for future queries that use that index.

You can read on the performance benefits that come from being able to use the UNIQUE keyword when creating an index in Brent Ozar's - Performance Benefits of Unique Indexes.

In response to your comments, there will be no difference in INSERT performance if you denote your index as UNIQUE vs not specifying that keyword.

J.D.
  • 954
  • 6
  • 22
1

Which type of index will the optimal (means degrade less) the INSERT performance, unique or not unique?

The difference if any is negligible.

On the other hand, what could have some impact is the index key length. Smaller is better. You could remove the primary key from the index key and make the index non unique. It will remain being a covering index because all non clustered indexes have the clustered index key on leaf page nodes, assuming your primary key is clustered.

Jesús López
  • 8,338
  • 7
  • 40
  • 66
1

The data in covering index will unique because the index keys contain the table's PK ... Which type of index will the optimal unique or not unique?

Assuming your table has a clustered index:

A non-unique nonclustered index in SQL Server is physically stored as a unique index after the clustered index keys are added as index key columns.

So for a nonclustered index that includes the clustered index keys there is absolutely no difference whether you declare the index to be unique or not.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • "A non-unique nonclustered index in SQL Server is physically stored as a unique index after the clustered index keys are added as index key columns." - can you elaborate on this? – Dai Jan 24 '21 at 16:57
0

In our cases, I prefer to use not unique indexes. For instance we have a table with hundreds of millions keywords to be analyzed. The keyword is unique (clustered index & primary key). But as we cannot do them all at once, we have a priority column as well. So we create a not unique, non-clustered index with only the Priority column and the keyword as included column.

This gives the following performance advantages:

  1. No additional constraint is checked when adding.
  2. When inserting a keyword, any page which has free space left and containing the same priority entries can be used. Meaning less fragmentation, less maintenance (rebuild/reorganize) Note that we also delete from this table and priority is just a tinyint, so this happens in most cases.
Gregory Liénard
  • 1,071
  • 3
  • 7