SQL Server 2022 reports a unique index violation for a non-unique index

Question

SQL Server 2022 reports a unique index violation for a non-unique index

Zalán Kovalcsik 0

Under SQL Server 2022, an intermittent 2601 error occurs during a scheduled data load.

SQL Server reports a duplicate key violation on a non-unique index, while referring to the index as a unique index.

Another anomaly is that the duplicate key reported in the error message contains one more value than the actual number of key columns in the affected index.

For example, the index has 3 key columns:

(transportation_flag, valid_from, valid_to)

but the error message reports 4 values:

(A, <date>, <date>, <identifier>)

Therefore, the duplicate key reported by SQL Server contains 4 values, while the index has only 3 key columns.

The error occurs intermittently and does not happen on every execution.

SQL Server version: Microsoft SQL Server 2022 (RTM-CU21)

Could this be a known SQL Server 2022 bug, and is there a fix or workaround available for this issue?

Erland Sommarskog 136.8K Reputation points MVP Volunteer Moderator

2026-08-25T06:53:26.03+00:00

No, I don't think I've ever heard about something like this before.

Do you see this with more than one table?

What about the clustered index on these table(s). Do they have a one-column key? Are the unique? Do they have the property IGNORE_DUP_KEY option set? (I'm asking these questions to see if there is a specific pattern.)

I would like to see a repro of this, but since it is intermittent, it may not be easy to produce.

Zalán Kovalcsik 0

There are multiple affected tables showing the same general pattern.

In all of the affected tables, the clustered primary key consists of two columns: (ID, date_from). The clustered primary key is unique, and IGNORE_DUP_KEY is set to OFF.

For example, an anonymized version of one of the affected tables is:

CREATE TABLE [dbo].[table_1](
    [id_1] [int] NOT NULL,
    [date_from] [smalldatetime] NOT NULL,
    [id_2] [int] NOT NULL,
    [code] [char](5) NOT NULL,
    [date_to] [smalldatetime] NULL,

    CONSTRAINT [PK_table_1] PRIMARY KEY CLUSTERED 
    (
        [id_1] ASC,
        [date_from] ASC
    )
    WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON,
        FILLFACTOR = 100
    ) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_table_1_code_date]
ON [dbo].[table_1]
(
    [code] ASC,
    [date_from] ASC,
    [date_to] ASC
)
INCLUDE ([id_1], [id_2])
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 100
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_table_1_date]
ON [dbo].[table_1]
(
    [date_from] ASC,
    [date_to] ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    FILLFACTOR = 100
) ON [PRIMARY]

The error:

Cannot insert duplicate key row in object 'dbo.table_1'
with unique index 'IX_table_1_date'.
The duplicate key value is
(Jul 29 2026 12:00AM, Jul 30 2026 12:00AM, 512479)

Cannot insert duplicate key row in object 'dbo.table_1'
with unique index 'IX_table_1_code_date'.
The duplicate key value is
(A, Jul 29 2026 12:00AM, Jul 30 2026 12:00AM, 512501)

There are multiple tables with this general structure, and the clustered primary key is consistently (ID, date_from) with IGNORE_DUP_KEY = OFF.

However, the IX_table_1_date and IX_table_1_code_date index shown above is not unique and contains only (date_from, date_to) and (code, date_from, datw_to).

Now first I disable the relevant indexes, perform the data load, and then rebuild the indexes afterwards, but it is slow.

I only found this topic: https://www.sqlservercentral.com/forums/topic/error-cannot-insert-duplicate-key-row-in-a-non-unique-index

Viorel 127.2K Reputation points

2026-08-25T12:17:22.5366667+00:00

Is it possible to try CU26 as well?
Erland Sommarskog 136.8K Reputation points MVP Volunteer Moderator

2026-08-25T13:19:48.28+00:00

I looked at that thread from SQL Server Central, and there seems to be some things that are in common with your case.

Have you been able to nail to the statements that are failing? Are they also UPDATE FROM where you update the PK? Is Change Tracking involved?

To explain the error messages as such, here are the news: All indexes are unique, physically. When an index is created a non-unique, SQL Server will add keys to the index to make it unique. This is discussed in the SQL Server Central thread, but the discussion in not entirely accurate.

If the index is in a table with a clustered index, the keys of the clustered index are always added as row locators. And if the index is non-unique, the keys of the clustered index will be added to make it unique. (If the clustered index itself is non-unique, SQL Server adds a uniquifier to that index, but since your CI is unique, we can disregard that.)

So your example table has the official index keys (code, date_from, date_to), while the CI is on (id_1, date_from). You have explicitly added id_1 and id_2 as included columns, but id_1 would be there even without the INCLUDE clause. Furthermore, id_1 is added to the index key to make it unique. Thus, that extra value you see in the error message is a value from id_1.

If you have problems tracking down the error statements, I note that Eric Russel in the SSC thread used Query Store. If you have Query Store enabled, trawling sys.query_store_runtime_stats for rows with execution_type = 4. Although, in Query Store, you cannot see why the query failed.

Another option is to set up an extended-events session that captures the event error_reported. I have an article on my web site where I describe how to do this, and you can query the event session from a view or a stored procedure so that you get the actual statements. Monitor Exceptions in Your Database.

1 answer

Your answer

Erland Sommarskog 136.8K Reputation points MVP Volunteer Moderator

2026-08-25T06:53:26.03+00:00

No, I don't think I've ever heard about something like this before.

Do you see this with more than one table?

What about the clustered index on these table(s). Do they have a one-column key? Are the unique? Do they have the property IGNORE_DUP_KEY option set? (I'm asking these questions to see if there is a specific pattern.)

I would like to see a repro of this, but since it is intermittent, it may not be easy to produce.
Viorel 127.2K Reputation points

2026-08-25T12:17:22.5366667+00:00

Is it possible to try CU26 as well?
Erland Sommarskog 136.8K Reputation points MVP Volunteer Moderator

2026-08-25T13:19:48.28+00:00

I looked at that thread from SQL Server Central, and there seems to be some things that are in common with your case.

Have you been able to nail to the statements that are failing? Are they also UPDATE FROM where you update the PK? Is Change Tracking involved?

To explain the error messages as such, here are the news: All indexes are unique, physically. When an index is created a non-unique, SQL Server will add keys to the index to make it unique. This is discussed in the SQL Server Central thread, but the discussion in not entirely accurate.

If the index is in a table with a clustered index, the keys of the clustered index are always added as row locators. And if the index is non-unique, the keys of the clustered index will be added to make it unique. (If the clustered index itself is non-unique, SQL Server adds a uniquifier to that index, but since your CI is unique, we can disregard that.)

So your example table has the official index keys (code, date_from, date_to), while the CI is on (id_1, date_from). You have explicitly added id_1 and id_2 as included columns, but id_1 would be there even without the INCLUDE clause. Furthermore, id_1 is added to the index key to make it unique. Thus, that extra value you see in the error message is a value from id_1.

If you have problems tracking down the error statements, I note that Eric Russel in the SSC thread used Query Store. If you have Query Store enabled, trawling sys.query_store_runtime_stats for rows with execution_type = 4. Although, in Query Store, you cannot see why the query failed.

Another option is to set up an extended-events session that captures the event error_reported. I have an article on my web site where I describe how to do this, and you can query the event session from a view or a stored procedure so that you get the actual statements. Monitor Exceptions in Your Database.

Answer 1

Deepesh Dhake 840

SQL Server appends the clustering key to non-unique nonclustered indexes to make them internally unique. This is documented, well-established behavior.

Since date_from is already a key column in both indexes, only the missing PK column id_1 is appended which is why one extra value appears, not two.

Therefore 512479 and 512501 are id_1 values, and both collisions reduce to a duplicate (id_1, date_from) which is also your PK.

Zalán Kovalcsik 0 Reputation points

2026-08-26T07:10:31.04+00:00

Thanks, that explains the additional value in the duplicate key. I agree that the extra value is the clustered key column id_1, since date_from is already part of the nonclustered index key.

However, I still don't understand why SQL Server reports the non-unique nonclustered index as a "unique index" in error 2601.
Erland Sommarskog 136.8K Reputation points MVP Volunteer Moderator

2026-08-26T16:38:59.4833333+00:00

Well, as I said in my post above: All indexes in SQL Server are unique. The non-unique moniker is just a smoke screen that SQL Server puts up for us. The more relevant question is why SQL Server is not able to uphold that smoke screen in this case. Certainly, one could be inclined to call this a bug. But before I agree with that for sure, I would like to know more. I don't know if you missed my second post yesterday, but I asked for some more information.
Deepesh Dhake 840 Reputation points

2026-08-26T17:42:49.3033333+00:00
Those indexes really are UNIQUE.

Check:

SELECT name, is_unique FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.table_1');

is_unique = 1 --> wording is literal, they're unique indexes; your duplicate (id_1, date_from) violates it.

is_unique = 0 --> genuinely unusual, worth digging into further.