有幾個關於分區的問題

有幾個關於分區的問題，希望大家幫忙了
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 TEMPDBgocreate table t(ID int identity constraint PK_T primary key,Num int not null )--只有一個IDcreate index IX_T_Num on T(Num)gocreate partition function F_Partition(int)as range right for values(1,100,1000)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))--這裡改為IDalter table t alter column ID int  not nullalter table T add constraint PK_T primary key (ID)--改為IDselect * from TDROP TABLE TDROP  PARTITION SCHEME  P_schemaDROP partition function  F_Partition

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

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

   

    

    

     
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