Knowledge Base

BUG: You may receive incorrect results when you run a query against the target partitioned table after you insert data from a partitioned table to another partitioned table in SQL Server 2005

Article ID: 931291

Article Last Modified on 11/20/2007

APPLIES TO

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

Bug #: 439362 (SQLBUDT)

SYMPTOMS

You use the INSERT statement in SQL Server 2005 to insert data from a partitioned table to another partitioned table. For example, you run the following statements:

Insert into TargetDB.owner.Target_Partitioned_Table 
Select * from SourceDB.owner.Source_Partitioned_Table
Go

When you run a query against the target partitioned table, you may receive incorrect results. For example, some rows are unexpectedly missing in the results.

This issue occurs if the following conditions are true:

The target partitioned table and the source partitioned table are both partitioned in two databases.
You use two different partition functions that have different boundary values on the two partitioned tables.
The two partition functions have the same value for the function_id column in the sys.partition_functions system table in the two databases.

Additionally, if you run the DBCC CHECKDB statement on the target database, you receive an error message that resembles the following:

Msg 8984, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 0, partition ID 72057594038452224. A row should be on partition number 2 but was found in partition number 3. Possible extra or invalid keys for:
Msg 8988, Level 16, State 1, Line 1
Row (1:162:0) identified by (HEAP RID = (1:162:0)).

CAUSE

This issue occurs because SQL Server 2005 generates an incorrect query plan for the INSERT statement. The partition id from the source partitioned table is used for the target partitioned table when you insert data. This behavior causes the rows to be inserted into incorrect positions.

WORKAROUND

To work around this issue, use one of the following methods, depend on the scenario that you are facing.

Note In the code examples, the following names are used:

The name of the target database is TargetDB.
The name of the source database is SourceDB.
The name of the target partitioned table is Target_Partitioned_Table.
The name of the source partitioned table is Source_Partitioned_Table.

Data in the source table is no longer available

You already have a problematic table in the target database, and the data in the source table is no longer available. In this scenario, follow these steps to work around this issue.

Copy the data from the target partitioned table by running the following statements:
```
Use TargetDB
Go
Select * into Target_Keep_Data
From Target_Partitioned_Table
Go
```
Drop the Target_Partitioned_Table table by running the following statements:
```
Drop table Target_Partitioned_Table
Go
```
Re-create the Target_Partitioned_Table table.
Run the following statements to insert data into the Target_Partitioned_Table table from the Target_Keep_Data table:
```
Insert into Target_Partitioned_Table
Select * from Target_Keep_Data
Go
```
Create partitioned indexes on the Target_Partitioned_Table table.

The boundary values of the partition functions differ

You want to insert data from a partitioned table to a target partitioned table. The target partitioned table uses a different partition function. This partition function uses boundary values that differ from the boundary values that the partition function uses in the source partitioned table.

In this scenario, use one of the following methods to avoid corrupted data appearing in the target partitioned table.

Method 1

If you have sufficient disk space to create an intermediate table, follow these steps:

In the SourceDB database, run the following statements to copy the data into a non-partitioned table:
```
Use SourceDB
Go
Select * into Source_Keep_Data
From  Source_Partitioned_Table
Go
```
Use the following statements to insert data from the Source_Keep_Data table into the Target_Partitioned_Table table:
```
Insert into Target_Partitioned_Table
Select * from SourceDB.. Source_Keep_Data
Go
```

Method 2

If you have insufficient disk space to create the Source_Keep_Data intermediate table, use a SQL Server 2005 Integration Services package to transport the data.

Alternatively, you can copy each partition of the source partitioned table into a separate, non-partitioned table. Then, run multiple INSERT statements for each partition to transport the data to the target partitioned table.

STATUS

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

MORE INFORMATION

Steps to reproduce the issue

To reproduce this issue, follow these steps:

Run the following statements to create the source database:

use master
go

Create database SourceDB
go

use SourceDB
go

Use the following statements to create a partition function and a partition scheme on the source database:

CREATE PARTITION FUNCTION [pf_source] 
(
    int
)
AS RANGE LEFT 
FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9)
GO

CREATE PARTITION SCHEME [ps_source]
AS PARTITION [pf_source] all 
TO
(
    [primary]
)
GO

Use the following statements to create the source partitioned table:

create table Source_Partitioned_Table(a int, b int, c char(10)) 
ON [ps_source]
(
    a
)
GO

Use the following statements to populate the source partitioned table with data:

declare @counter int
set @counter = 0
while (@counter < 100)
begin
    insert into Source_Partitioned_Table(a, b, c) values (@counter, @counter * 5, 'Hello')
    set @counter = @counter + 1
end
go

Use the following statements to create the clustered partitioned index on the source partitioned table:

CREATE CLUSTERED INDEX [CIX_Tbl_aa]
ON [dbo].[Source_Partitioned_Table]
(
    a,
    b
)
ON [ps_source]
(
    a
)
GO

Use the following statements to run a simple query to check the data:

select *
from Source_Partitioned_Table
where a = 8
go

dbcc checkdb
go

Use the following statements to create the target database:

use master
go

Create database TargetDB
go

use TargetDB
go

Create a partition function and partition scheme on the target database that differs from the partition in the source database. To do this, use the following statements:

CREATE PARTITION FUNCTION [pf_target] 
(
    int
)
AS RANGE LEFT 
FOR VALUES (1, 5, 10, 15, 100)
GO

CREATE PARTITION SCHEME [ps_target]
AS PARTITION [pf_target] all 
TO
(
    [primary]
)
GO

Use the following statements to create the target partitioned table on the target database:

create table Target_Partitioned_Table(a int, b int, c char(10)) 
ON [ps_target]
(
    a
)
GO

Use the following statements to insert data to the target partitioned table from the source partitioned table:
```
Insert into Target_Partitioned_Table
Select * from SourceDB..Source_Partitioned_Table
go
```
Use the following statements to create the clustered partitioned index for the target partitioned table:
```
CREATE CLUSTERED INDEX [CIX_Tbl_bb]
ON [dbo].[Target_Partitioned_Table]
(
    a,
    b
)
ON [ps_target]
(
    a
)
GO
```
When you run the following query against the target partitioned table, you experience unexpected results:
```
select *
from Target_Partitioned_Table
where a = 8
go
```
Additionally, when you run the DBCC CHECKDB statement on the target database, you receive the error message that is mentioned in the "Symptoms" section.

Keywords: kbtshoot kbbug kbexpertiseadvanced kbsql2005engine KB931291

Microsoft KB Archive/931291

Contents