Microsoft KB Archive/937533

= FIX: Error message when you run a query in SQL Server 2005: &quot;Cannot insert duplicate key row in object ' with unique index ' '.

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.



Cumulative update information
To resolve this problem, obtain the cumulative update package (build 3175) for SQL Server 2005 Service Pack 2 (SP2). For more information, 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 is available



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 &quot;Steps to reproduce this issue&quot; section in the &quot;More Information&quot; section as an example to work around.   Use distinct and non-distinct aggregations in separate queries.

For example, in the example query in the &quot;More Information&quot; 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:  Create a temporary table. Insert the rows that have to be aggregated into the temporary table. 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.</li></ul>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_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 ‘ ’. Dropping and re-creating the index may resolve this; otherwise, use another clustering key.

Steps to reproduce this issue
 Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.</li>  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 </li>  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 </li></ol>

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.

Keywords: kbsql2005engine kbexpertiseadvanced kbfix kbpubtypekc kbqfe kbhotfixserver KB937533

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.