FIX: Error message when you run a query in SQL Server 2005: "Cannot insert duplicate key row in object <TableName> with unique index <IndexName>"

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Bug: #50001287 (SQL Hotfix)

Symptoms

In SQL Server 2005, you receive the following error message when you run a query:
Error: 2601, Severity: 14, State: 1

Cannot insert duplicate key row in object 'TableName' with unique index 'IndexName'.
This issue occurs when the execution plan that is generated for the query contains a Table Spool operator that has more than 2,147,483,648 input rows.

Cause

This issue occurs because the Table Spool operator creates a unique clustered index on a column when the execution plan is generated for the query. When the number of the input rows exceeds 2,147,483,648, the uniquifier for the clustered index runs out. Therefore, you receive the error message that states that duplicate rows cannot be inserted.

Resolution

Cumulative update information

The fix for this issue was first released in Cumulative Update 2. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
936305 Cumulative update package 2 for SQL Server 2005 Service Pack 2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Workaround

To work around this issue, use one of the following methods to avoid the Table Spool operator.

Note The following methods use the query that is mentioned in the "Steps to reproduce this issue" section in the "More Information" section as an example to work around.
  • Use distinct and non-distinct aggregations in separate queries.

    For example, in the example query in the "More Information" section, break the query into two parts as follows:
    --Part 1
    select t1.c1,sum(t2.c3)
    from t1
    inner join t2 on t1.c1=t2.c1
    group by t1.c1
    --Part 2
    select t1.c1 , count(distinct t1.c3)
    from t1
    inner join t2 on t1.c1=t2.c1
    group by t1.c1
  • Use a temporary table:
    1. Create a temporary table.
    2. Insert the rows that have to be aggregated into the temporary table.
    3. Do the aggregations against the temporary table.
  • Try to use some query hints or index optimizer hints to rewrite the query. You may find a way to avoid the Table Spool operator.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

This hotfix corrects the logic to detect this problem and raise a suitable error message in this situation. After you apply this hotfix, you receive the following error message when you experience this issue:
The maximum system-generated unique value for a duplicate group was exceeded for index with partition ID ‘PartitionID’. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Steps to reproduce this issue

  1. Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
  2. Create a new query, and then run the following statements:
    if exists(select name from sysobjects where name=N't1' and xtype='U')
    drop table t1
    go
    create table t1
    ( c1 bigint identity,
    c2 char (10),
    c3 int ,
    c4 int)
    go

    if exists(select name from sysobjects where name=N't2' and xtype='U')
    drop table t2
    go
    create table t2
    ( c1 bigint identity,
    c2 char (10),
    c3 int ,
    c4 int)
    go
  3. Run the following statements:
    set showplan_all on
    go
    select t1.c1,sum(t2.c3), count(distinct t1.c3)
    from t1
    inner join t2 on t1.c1=t2.c1
    group by t1.c1
    go

    set showplan_all off
    go
When you complete these steps, you receive the following execution plan:
StmtText                                                                                                                                                                              StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                              DefinedValues                                                                           EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                          Warnings Type                                                             Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
select t1.c1,sum(t2.c3), count(distinct t1.c3)
from t1
inner join t2 on t1.c1=t2.c1
group by t1.c1 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.06636031 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1]) [Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1] 1 0 1E-07 23 0.06636031 [Test2].[dbo].[t1].[c1], [Expr1008], [Expr1009] NULL PLAN_ROW 0 1
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Test2].[dbo].[t1].[c1])=([Test2].[dbo].[t1].[c1]), RESIDUAL:([Test2].[dbo].[t1].[c1] = [Test2].[dbo].[t1].[c1])) 1 3 2 Merge Join Inner Join MANY-TO-MANY MERGE:([Test2].[dbo].[t1].[c1])=([Test2].[dbo].[t1].[c1]), RESIDUAL:([Test2].[dbo].[t1].[c1] = [Test2].[dbo].[t1].[c1]) NULL 1 0.000313 0.00564738 23 0.06636021 [Expr1008], [Expr1009], [Test2].[dbo].[t1].[c1] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1])) 1 4 3 Compute Scalar Compute Scalar DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1]) [Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1] 1 0 1E-07 19 0.03019842 [Expr1009], [Test2].[dbo].[t1].[c1] NULL PLAN_ROW 0 1
| |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1019],0))) 1 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1019],0)) [Expr1009]=CONVERT_IMPLICIT(int,[Expr1019],0) 1 0 1.1E-06 19 0.03019832 [Test2].[dbo].[t1].[c1], [Expr1009] NULL PLAN_ROW 0 1
| |--Stream Aggregate(GROUP BY:([Test2].[dbo].[t1].[c1]) DEFINE:([Expr1019]=COUNT([Test2].[dbo].[t1].[c3]))) 1 6 5 Stream Aggregate Aggregate GROUP BY:([Test2].[dbo].[t1].[c1]) [Expr1019]=COUNT([Test2].[dbo].[t1].[c3]) 1 0 1.1E-06 19 0.03019832 [Test2].[dbo].[t1].[c1], [Expr1019] NULL PLAN_ROW 0 1
| |--Sort(DISTINCT ORDER BY:([Test2].[dbo].[t1].[c1] ASC, [Test2].[dbo].[t1].[c3] ASC)) 1 7 6 Sort Distinct Sort DISTINCT ORDER BY:([Test2].[dbo].[t1].[c1] ASC, [Test2].[dbo].[t1].[c3] ASC) NULL 1 0.01126126 0.000100019 19 0.03019721 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3] NULL PLAN_ROW 0 1
| |--Table Spool 1 9 7 Table Spool Eager Spool NULL NULL 1 0.006579998 5.041407E-05 31 0.01883594 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3], [Test2].[dbo].[t2].[c3] NULL PLAN_ROW 0 1
| |--Hash Match(Inner Join, HASH:([Test2].[dbo].[t2].[c1])=([Test2].[dbo].[t1].[c1]), RESIDUAL:([Test2].[dbo].[t2].[c1]=[Test2].[dbo].[t1].[c1])) 1 10 9 Hash Match Inner Join HASH:([Test2].[dbo].[t2].[c1])=([Test2].[dbo].[t1].[c1]), RESIDUAL:([Test2].[dbo].[t2].[c1]=[Test2].[dbo].[t1].[c1]) NULL 1 0 0.01777693 31 0.02434613 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3], [Test2].[dbo].[t2].[c1], [Test2].[dbo].[t2].[c3] NULL PLAN_ROW 0 1
| |--Table Scan(OBJECT:([Test2].[dbo].[t2])) 1 11 10 Table Scan Table Scan OBJECT:([Test2].[dbo].[t2]) [Test2].[dbo].[t2].[c1], [Test2].[dbo].[t2].[c3] 1 0.003125 0.0001581 19 0.0032831 [Test2].[dbo].[t2].[c1], [Test2].[dbo].[t2].[c3] NULL PLAN_ROW 0 1
| |--Table Scan(OBJECT:([Test2].[dbo].[t1])) 1 12 10 Table Scan Table Scan OBJECT:([Test2].[dbo].[t1]) [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3] 1 0.003125 0.0001581 19 0.0032831 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1])) 1 26 3 Compute Scalar Compute Scalar DEFINE:([Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1]) [Test2].[dbo].[t1].[c1]=[Test2].[dbo].[t1].[c1] 1 0 1E-07 19 0.03019842 [Expr1008], [Test2].[dbo].[t1].[c1] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END)) 1 27 26 Compute Scalar Compute Scalar DEFINE:([Expr1008]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END) [Expr1008]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END 1 0 1.1E-06 19 0.03019832 [Test2].[dbo].[t1].[c1], [Expr1008] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY:([Test2].[dbo].[t1].[c1]) DEFINE:([Expr1020]=COUNT_BIG([Test2].[dbo].[t2].[c3]), [Expr1021]=SUM([Test2].[dbo].[t2].[c3]))) 1 28 27 Stream Aggregate Aggregate GROUP BY:([Test2].[dbo].[t1].[c1]) [Expr1020]=COUNT_BIG([Test2].[dbo].[t2].[c3]), [Expr1021]=SUM([Test2].[dbo].[t2].[c3]) 1 0 1.1E-06 19 0.03019832 [Test2].[dbo].[t1].[c1], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Sort(ORDER BY:([Test2].[dbo].[t1].[c1] ASC)) 1 29 28 Sort Sort ORDER BY:([Test2].[dbo].[t1].[c1] ASC) NULL 1 0.01126126 0.000100019 19 0.03019721 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t2].[c3] NULL PLAN_ROW 0 1
|--Table Spool 1 31 29 Table Spool Eager Spool NULL NULL 1 0.006579998 5.041407E-05 31 0.01883594 [Test2].[dbo].[t1].[c1], [Test2].[dbo].[t1].[c3], [Test2].[dbo].[t2].[c3] NULL PLAN_ROW 0 1
If one of the test tables has more than 2,147,483,648 rows, you experience this issue.
Properties

Article ID: 937533 - Last Review: Oct 8, 2011 - Revision: 1

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

Feedback