Newly split one more empty partition, can not view in distribution view and can not switch data in?


  • Hello Friends,

    I just splitted one more empty partition. However, it can not show me the new partition with 0 row by uising below SQL

    partition = $PARTITION.[pf_range_fact]([OnlineSalesKey])
    ,rows      = COUNT(*)
    ,min    = MIN([OnlineSalesKey])
    ,max    = MAX([OnlineSalesKey])
    FROM [dbo].[FactOnlineSales_Prtition]
    GROUP BY $PARTITION.[pf_range_fact]([OnlineSalesKey])
    partition	rows	min	max
    1	2439516	19560484	21999999
    2	3000000	22000000	24999999
    3	3000000	25000000	27999999
    4	2000000	28000000	29999999
    5	1000000	30000000	30999999

    I suppose it should be empty becasue I split the new partition from 31000000 and the SQL result does not show  me any rows beginning from this number.

    Then I tried to switch my stageT to this new partition 6.

    ALTER TABLE  [STG].[FactOnlineSales] SWITCH TO [dbo].[FactOnlineSales_Prtition] PARTITION 6

    It reports the error like below

    Warning: The specified partition 6 for the table 'ContosoRetailDW.dbo.FactOnlineSales_Prtition' was ignored in ALTER TABLE SWITCH statement because the table is not partitioned.
    Msg 4905, Level 16, State 1, Line 1
    ALTER TABLE SWITCH statement failed. The target table 'ContosoRetailDW.dbo.FactOnlineSales_Prtition' must be empty.
    Any thoughts on this?


    • Edited by Derek Dai Wednesday, May 09, 2012 7:10 PM
    Wednesday, May 09, 2012 6:00 PM


  • Resolved. The reason of failure is becasue the PK isn's created on the partition schema.


    • Marked as answer by Derek Dai Wednesday, May 09, 2012 7:55 PM
    Wednesday, May 09, 2012 7:53 PM