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

Article translations Article translations
Article ID: 937533 - View products that this article applies to.
Bug: #50001287 (SQL Hotfix)
Expand all | Collapse all

On This Page

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: October 8, 2011 - Revision: 2.0
APPLIES TO
  • 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
Keywords: 
kbsql2005engine kbexpertiseadvanced kbfix kbqfe KB937533

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com