sql server - Destination table partition is empty but alter table switch still fails stating destination partition must be empty -

i working sql server 2008. have 2 100 partitioned tables of same structure, partition schema/function , same file group.

table structure:

create table source_table (      id int,       xmldata xml,       partitionkey ([id]%(100)) persisted )  create table destination_table (      id int,       xmldata xml,       partitionkey ([id]%(100)) persisted ) 

requirement:

destination_table has records partition 23 empty. need move partition 23 records source_table destination_table.

alter table source_table  switch partition 23 destination_table partition 23 

i error

alter table switch failed. target partition 23 of destination_table must empty.

the partition 23 of destination_table empty.

select count(1)  destination_table  

returns 0.

then why error?

had confusion between partition value , partition id. partition value 23 partition id 24 partition value started 0 99 , partition id 1 100.

so, partition id 24 worked moving partition value 23:

alter table source_table switch partition 24 destination_table partition 24