表分区
--1.创建文件组ALTER DATABASE TEST
ADD FILEGROUPFILEGROUP1
ALTER DATABASE TEST
ADD FILEGROUPFILEGROUP2
ALTER DATABASE TEST
ADD FILEGROUPFILEGROUP3
--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,,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
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
--或者删除
DROP INDEX CT_SALE ON ORDERRECORDS
--6删除分区方案
DROP PARTITION SCHEME SCHEMAFORPARIRION
--7删除分区函数
DROP PARTITION FUNCTION FNPARTITION
--8.删除文件
ALTER DATABASE TESTREMOVE FILE FILE1
ALTER DATABASE TESTREMOVE FILE FILE2
ALTER DATABASE TESTREMOVE 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 * FROMORDERRECORDS 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
支持一下 ！ hq2008 发表于 2015-10-12 08:12
支持一下 ！
表数据量大的时候,做表分区很有用的:lol
页:
[1]