Microsoft KB Archive/931291

= 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 </li>  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 </li></ol>

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.

<div class="status_section">

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

<div class="moreinformation_section">

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 </li>  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 </li>  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 </li>  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 </li>  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 </li>  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 </li>  Use the following statements to create the target database: use master go

Create database TargetDB go

use TargetDB go </li>  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 </li>  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 </li>  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 </li>  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 &quot;Symptoms&quot; section. </li></ol>

Keywords: kbtshoot kbbug kbexpertiseadvanced kbsql2005engine KB931291

-

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

© Microsoft Corporation. All rights reserved.