switch partition to table

Question

Post reply

switch partition to table

tklimczak

Old Hand

Points: 321
More actions
October 29, 2007 at 6:02 am

#176613

I get error message when I want to switch partition to table
There is problem with primary_key (when I remove PK from taget table TABLE_B - switching works)
This primary keys are the same (only one difference is that PK from TABLE_A is partitioned)
ALTER TABLE TABLE_A SWITCH PARTITION @partition_number TO TABLE_B
Error :
"ALTER TABLE SWITCH statement failed. There is no identical index in source table 'TABLE_A' for the index 'PK_TABLE_B' in target table 'TABLE_B' "

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 1

http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

The message clears states that you don't have an index on source table which is there in target table....

Taken for BOL

Table and Index Structure Requirements

Both the source table and the target table must have the same structure. This means the following:

The tables must have the same columns with the same names and the same data type, length, collation, precision, scale, nullability, and PRIMARY KEY constraints, and also have the same settings for ANSI_NULLS and QUOTED IDENTIFIER. Additionally, the columns must be defined in the same order. The IDENTITY property is not considered.

Caution:

Performing partition switching can introduce duplicate values in IDENTITY columns of the target table, and gaps in the values of IDENTITY columns in the source table. Use DBCC CHECKIDENT to check the identity values of your tables and correct the values if you want.

The nullability of their partitioning columns must agree. That is, both must be either NULL or NOT NULL. If one of the tables is not partitioned, the nullability of the column corresponding to the other table's partitioning column must be in agreement with the other table's partitioning column.

Important:

We recommend that you specify NOT NULL on the partitioning column of partitioned tables, and also nonpartitioned tables that are sources or targets for ALTER TABLE...SWITCH operations. By doing this, you make sure that any CHECK constraints on partitioning columns do not have to check for null values. Null values are ordinarily placed in the leftmost partition of a partitioned table. When you are switching any partition other than the leftmost partition and when the ANSI_NULLS database option is set to ON, the absence of a NOT NULL constraint on the source and target tables might interfere with any CHECK constraints that are also defined on the partitioning column.

If their corresponding partition keys are computed columns, the syntax of expressions that are defining their computed columns are the same, and both computed columns are persisted.

Any column that is defined with the ROWGUID property must correspond to a column in the other table that is also defined with the ROWGUID property.

Any xml columns must by typed to the same XML schema collection.

The in-row settings of any text, ntext, or image columns are the same. For more information about this setting, see In-Row Data.

The tables have the same clustered indexes. These indexes cannot be disabled.

Any nonclustered indexes that are defined on the target table are also defined on the source table and are structured identically in terms of uniqueness, subkeys, and the sorting direction (ASC or DESC) for each index key column. Nonclustered indexes that are disabled are exempt from this requirement.

--Ramesh