|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有账号?注册账号
×
--1.创建文件组
ALTER DATABASE TEST
ADD FILEGROUP FILEGROUP1
ALTER DATABASE TEST
ADD FILEGROUP FILEGROUP2
ALTER DATABASE TEST
ADD FILEGROUP FILEGROUP3
--2.添加文件
ALTER DATABASE TEST
ADD FILE (
NAME = N'FILE1',
FILENAME = N'E:\ERP\FILE1.NDF' ,
SIZE = 3072KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP FILEGROUP1
ALTER DATABASE TEST
ADD FILE (
NAME = N'FILE2' ,
FILENAME = N'E:\ERP\FILE2.NDF' ,
SIZE = 3072KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP FILEGROUP2
ALTER DATABASE TEST
ADD FILE (
NAME = N'FILE3' ,
FILENAME = N'E:\ERP\FILE3.NDF' ,
SIZE = 3072KB ,
FILEGROWTH = 1024KB
)
TO FILEGROUP FILEGROUP3
--3.1创建分区函数
CREATE PARTITION FUNCTION FNPARTITION(INT)
AS RANGE RIGHT
FOR VALUES(333333333,666666666)
--4.1创建分区方案
CREATE PARTITION SCHEME SCHEMAFORPARIRION
AS PARTITION FNPARTITION
TO(FILEGROUP1,[PRIMARY],FILEGROUP2)
--4.2 添加文件组
ALTER PARTITION SCHEME SCHEMAFORPARIRION
NEXT USED FILEGROUP3;
--3.2拆分分区
ALTER PARTITION FUNCTION FNPARTITION ()
SPLIT RANGE (500);
--3.3合并分区
ALTER PARTITION FUNCTION FNPARTITION ()
MERGE RANGE (500);
--5.1新建分区表
CREATE TABLE ORDERRECORDS
(
ID INT
)
ON SCHEMAFORPARIRION(ID)
DROP TABLE ORDERRECORDS
--5.2普通表变为分区表
--如果原来就主键,先删除
ALTER TABLE ORDERRECORDS DROP CONSTRAINT PK_SALE
ALTER TABLE ORDERRECORDS ADD CONSTRAINT PK_SALE PRIMARY KEY
(
ID ASC
) ON SCHEMAFORPARIRION(ID)
--或者创建聚集索引,如果原来有主键,要先改成非聚集索引
ALTER TABLE ORDERRECORDS DROP CONSTRAINT PK_SALE
--创建主键,但不设为聚集索引
ALTER TABLE ORDERRECORDS ADD CONSTRAINT PK_SALE PRIMARY KEY NONCLUSTERED
(
ID ASC
) ON [PRIMARY]
CREATE CLUSTERED INDEX CT_SALE ON ORDERRECORDS(ID)
ON PARTSCHSALE(ID)
--5.3分区表变为普通表
ALTER TABLE ORDERRECORDS DROP CONSTRAINT PK_SALE
ALTER TABLE ORDERRECORDS ADD CONSTRAINT PK_SALE PRIMARY KEY
(
ID ASC
) ON [PRIMARY]
--或者删除
DROP INDEX CT_SALE ON ORDERRECORDS
--6删除分区方案
DROP PARTITION SCHEME SCHEMAFORPARIRION
--7删除分区函数
DROP PARTITION FUNCTION FNPARTITION
--8.删除文件
ALTER DATABASE TEST REMOVE FILE FILE1
ALTER DATABASE TEST REMOVE FILE FILE2
ALTER DATABASE TEST REMOVE FILE FILE3
--9.删除文件组
ALTER DATABASE TEST REMOVE FILEGROUP FILEGROUP1
ALTER DATABASE TEST REMOVE FILEGROUP FILEGROUP2
ALTER DATABASE TEST REMOVE FILEGROUP FILEGROUP3
--10查看分区函数
SELECT * FROM SYS.PARTITION_FUNCTIONS
SELECT * FROM SYS.PARTITION_RANGE_VALUES
SELECT * FROM SYS.PARTITION_SCHEMES
--11.查看每个分区的数据
SELECT * FROM ORDERRECORDS WHERE $PARTITION.FNPARTITION(ID)=4
--12.查看分区对应的文件
SELECT FG.NAME
FROM SYS.DESTINATION_DATA_SPACES SP
INNER JOIN SYS.PARTITION_SCHEMES SCM ON SP.PARTITION_SCHEME_ID = SCM.DATA_SPACE_ID
INNER JOIN SYS.FILEGROUPS FG ON FG.DATA_SPACE_ID = SP.DATA_SPACE_ID
WHERE SCM.NAME='SCHEMAFORPARIRION' AND SP.DESTINATION_ID=4
|
|