有几个关于分区的问题

有几个关于分区的问题，希望大家帮忙了
1. 表已经存在，能否再应运分区方案呢，如果可以，这么用？
2. sqlserver里能否直接通过执行Select * into 表 from 表2时，也带上分区方案，如果能这样那是方便不过？

 

 

可以
1:刪除普通索引
2:刪除主健索引並轉為分區表
3：再恢復主健索引
4:恢復普通索引

 

use TEMPDB
go
create table t(ID int identity ,Num int not null constraint PK_T primary key(Num,ID))
create index IX_T_Num on T(Num)

go
create partition function F_Partition(int)
as range right for values(1,100,1000)
go
CREATE PARTITION SCHEME  P_schema
as partition F_Partition  ALL to ([PRIMARY])

go
drop index IX_T_Num on T
alter table T drop constraint PK_T with(Move to P_schema(Num))

alter table t alter column ID int  not null

alter table T add constraint PK_T primary key (Num,ID)


select * from T


DROP TABLE T
DROP PARTITION SCHEME P_schema DROP partition function  F_Partition

 

-----------------------------

如果主健只有一列時直接改

 

   
   

    
    
    
    use
    
     TEMPDB

    
    go
    
    

    
    create
    
     
    
    table
    
     t(ID 
    
    int
    
     
    
    identity
    
     
    
    constraint
    
     PK_T 
    
    primary
    
     
    
    key
    
    ,Num 
    
    int
    
     
    
    not
    
     
    
    null
    
     )
    
    --
    
    只有一個ID
    
    

    
    create
    
     
    
    index
    
     IX_T_Num 
    
    on
    
     T(Num)


    
    go
    
    

    
    create
    
     partition 
    
    function
    
     F_Partition(
    
    int
    
    )

    
    as
    
     range 
    
    right
    
     
    
    for
    
     
    
    values
    
    (
    
    1
    
    ,
    
    100
    
    ,
    
    1000
    
    )

    
    go
    
    

    
    CREATE
    
     PARTITION SCHEME  P_schema

    
    as
    
     partition F_Partition  
    
    ALL
    
     
    
    to
    
     (
    
    [
    
    PRIMARY
    
    ]
    
    )


    
    go
    
    

    
    drop
    
     
    
    index
    
     IX_T_Num 
    
    on
    
     T

    
    alter
    
     
    
    table
    
     T 
    
    drop
    
     
    
    constraint
    
     PK_T 
    
    with
    
    (Move 
    
    to
    
     P_schema(ID))
    
    --
    
    這里改為ID
    
    

    
    

    
    alter
    
     
    
    table
    
     t 
    
    alter
    
     
    
    column
    
     ID 
    
    int
    
      
    
    not
    
     
    
    null
    
    


    
    alter
    
     
    
    table
    
     T 
    
    add
    
     
    
    constraint
    
     PK_T 
    
    primary
    
     
    
    key
    
     (ID)
    
    --
    
    改為ID
    
    

    
    


    
    select
    
     
    
    *
    
     
    
    from
    
     T



    
    DROP
    
     
    
    TABLE
    
     T

    
    DROP
    
      PARTITION SCHEME  P_schema

    
    DROP
    
     partition 
    
    function
    
      F_Partition
   
   

   
   

    
    -----------------------------------
   
   

   
   

    
    如果是into生成的表，這樣用 

     
     

      
      
      
      

       
       
        
        

         
         
         
         use
         
          TEMPDB

         
         go
         
         

         
         select
         
          
         
         *
         
          
         
         into
         
          T 
         
         from
         
          sysobjects


         
         go
         
         



         
         create
         
          partition 
         
         function
         
          F_Partition(
         
         int
         
         )

         
         as
         
          range 
         
         right
         
          
         
         for
         
          
         
         values
         
         (
         
         1
         
         ,
         
         100
         
         ,
         
         1000
         
         )

         
         go
         
         

         
         CREATE
         
          PARTITION SCHEME  P_schema

         
         as
         
          partition F_Partition  
         
         ALL
         
          
         
         to
         
          (
         
         [
         
         PRIMARY
         
         ]
         
         )


         
         go
         
         

         
         alter
         
          
         
         table
         
          T 
         
         alter
         
          
         
         column
         
          ID 
         
         int
         
          
         
         not
         
          
         
         null
         
         


         
         alter
         
          
         
         table
         
          T 
         
         add
         
          
         
         constraint
         
          PK_T 
         
         primary
         
          
         
         key
         
          (ID) 
         
         on
         
          P_schema(ID) 




         
         select
         
          
         
         *
         
          
         
         from
         
          T



         
         DROP
         
          
         
         TABLE
         
          T

         
         DROP
         
           PARTITION SCHEME  P_schema

         
         DROP
         
          partition 
         
         function
         
           F_Partition
        
        


      
      
      
      


       
       
http://topic.csdn.net/u/20090803/15/225c1920-e2a0-417a-9bd8-658644b9b96c.html?35493