There are several questions about partitions.

I have a few questions about partition. I hope you can help me.
1. If the table already exists, can we run the partition scheme? If so, how can we use it?
2. Can I directly execute select * into table from table 2 in sqlserver with the partition scheme?

 

 

Yes
1: Except common indexes
2: Except for the primary index and the partition table
3: Restore the primary index
4: restore common indexes

 

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,100 0)
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

 

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

If the primary key has only one column, change it directly.

   Use tempdbgocreate table t (ID int identity constraint pk_t primary key, num int not null) -- only one idcreate index ix_t_num on T (Num) gocreate Partition Function f_partition (INT) as range right for values (1,100,100 0) gocreate Partition Scheme p_schemaas partition f_partition all to ([primary]) godrop index ix_t_num on talter table t drop constraint pk_t with (move to p_schema (ID )) -- change this to idalter table t alter column ID int not nullalter table t add constraint pk_t primary key (ID) -- change to idselect * From tdrop table tdrop Partition Scheme p_schemadrop partition f_function Partition

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

If the table is generated by into, use

   
   

    
    

    
    
 
     
      use TEMPDBgoselect * into T from sysobjectsgocreate partition function F_Partition(int)as range right for values(1,100,1000)goCREATE PARTITION SCHEME  P_schemaas partition F_Partition  ALL to ([PRIMARY])goalter table T alter column ID int not nullalter table T add constraint PK_T primary key (ID) on P_schema(ID) select * from TDROP TABLE TDROP  PARTITION SCHEME  P_schemaDROP partition function  F_Partition
 
    
    
    
 
     
http://topic.csdn.net/u/20090803/15/225c1920-e2a0-417a-9bd8-658644b9b96c.html?35493