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.
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
(http://support.microsoft.com/kb/936305/LN/
)
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
(http://support.microsoft.com/kb/937137/LN/
)
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.
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:
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.
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.
Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
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
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:
Back to the top
