1,
把原表转为分区表,
ALTER TABLE table DROP CONSTRAINT PK_table
WITH(MOVE TO PS(table.column))
这个可行,但现在我的表没有主建和一些其他的约束,
drop部分写不了,
要怎么样写这个语句?2,
把分区表转成非分区表
用什么语句?
把原表转为分区表,
ALTER TABLE table DROP CONSTRAINT PK_table
WITH(MOVE TO PS(table.column))
这个可行,但现在我的表没有主建和一些其他的约束,
drop部分写不了,
要怎么样写这个语句?2,
把分区表转成非分区表
用什么语句?
CREATE DATABASE [Data partition DB5]
ON PRIMARY(
NAME='Data PARTITION DB5',
FILEName='D:\tmp\Data_paratition_DB5.mdf',
size=5,
maxsize=100,
filegrowth=1),
filegroup [Data paratition DB5 FG1](
NAME='Data PARTITION DB5 FG1',
filename='D:\tmp\Data_Paratition_DB5_fg1.ndf',
size=5,
maxsize=100,
filegrowth=1),
filegroup [Data partition DB5 FG2](
NAME='Data PARTITION DB5 FG2',
filename='D:\tmp\Data_Paratition_DB5_fg2.ndf',
size=5,
maxsize=100,
filegrowth=1),
filegroup [Data partition DB5 FG3](
NAME='Data PARTITION DB5 FG3',
filename='D:\tmp\Data_Paratition_DB5_fg3.ndf',
size=5,
maxsize=100,
filegrowth=1),
filegroup [Data partition DB5 FG4](
NAME='Data PARTITION DB5 FG4',
filename='D:\tmp\Data_Paratition_DB5_fg4.ndf',
size=5,
maxsize=100,
filegrowth=1)---建立非分区表
USE [Data Partition DB5]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money ) on [primary]
CREATE TABLE MyTable_history
(ID INT NOT NULL,
Date DATETIME,
Cost money ) on [Data paratition DB5 FG1]
---插入测试数据
USE [Data Partition DB5]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID) on [PRIMARY]
USE [Data Partition DB5]
go
declare @count int
set @count =-25
while @count <=100
begin
insert into MyTable select @count,getdate(),100.00
set @count=@count+1
end
set @count =101
while @count <=200
begin
insert into MyTable select @count,getdate(),200.00
set @count=@count+1
end
set @count =201
while @count <=300
begin
insert into MyTable select @count,getdate(),300.00
set @count=@count+1
end
set @count =301
while @count <=400
begin
insert into MyTable select @count,getdate(),400.00
set @count=@count+1
end
set @count =401
while @count <=800
begin
insert into MyTable select @count,getdate(),500.00
set @count=@count+1
end
--建立分区函数
use [Data Partition DB5]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
--查看分区范围
SELECT * FROM sys.partition_range_values--建立分区模式
USE [Data Partition DB5]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data paratition DB5 FG1], [Data partition DB5 FG2], [Data partition DB5 FG3],[Data partition DB5 FG4]);
--将原非分区表改为分区表
DROP INDEX MyTable_IXC ON myTable WITH (move TO[Data Partition Scheme](id))--查看分区数据
SELECT * FROM sys.partitions WHERE OBJECT_NAME(object_id)='MyTable'--将分区1中的数据移到myTable_history中,MyTable_history必须和分区1在同一个文件组中
ALTER TABLE MyTable SWITCH PARTITION 1 TO MyTable_historySELECT * FROM myTable_history;--将myTable_history中的数据更改后移回分区表myTable
update myTable_history SET date='2009-10-01';--注:要把普通表的数据移回到分区表,必须要在普通表上建离同要移入的分区相同的约束。并且目标分区一定要为空
ALTER TABLE myTable_history ADD CONSTRAINT ck_myTableHistory_id CHECK (id <=100)ALTER TABLE dbo.myTable_history
SWITCH TO myTable PARTITION 1;
--合并分区
ALTER PARTITION FUNCTION [Data Partition Range]()
MERGE range(100)
--拆分分区
Alter database [Data Partition DB5] add FILEGROUP [Data Partition DB5 FG5]
go
alter database [Data Partition DB5]
ADD FILE
(NAME = 'Data Partition DB5 FG5',
FILENAME =
'D:\Data\FG4\Data_Partition_DB5_FG5.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
TO FILEGROUP [Data Partition DB5 FG5]
GO
Use [Data Partition DB5]
go
ALTER PARTITION SCHEME [Data Partition Scheme]
NEXT USED [Data Partition DB5 FG5]
GO
ALTER PARTITION FUNCTION [Data Partition Range]()
split range(500)
CREATE CLUSTERED INDEX INDEX_NAME ON PT_Date([Month]) WITH (DROP_EXISTING=ON)
ON [Month_Scheme]([Month])