--1第一步：建立我们要使用的数据库，最重要的是建立多个文件组(3个)。
CREATE DATABASE test ON PRIMARY
(
NAME = N'test',
FILENAME = N'D:\test.mdf',
SIZE = 3MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
),
FILEGROUP FG1
(
NAME = N'File1',
FILENAME = N'D:\File1.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
),
FILEGROUP FG2
(
NAME = N'File2',
FILENAME = N'D:\File2.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
),
FILEGROUP FG3
(
NAME = N'File3',
FILENAME = N'D:\File3.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = N'test_Log',
FILENAME = N'D:\test_Log.ldf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 10%
)
GO

--2第二步：建立分区函数，这里我们建立三个分区。

USE test
GO
CREATE PARTITION FUNCTION pf_TestDate (datetime)
AS RANGE RIGHT
FOR VALUES ('2010/01/01', '2011/01/01')

GO

--3第三步：创建分区方案，关联到分区函数。

USE test
GO
CREATE PARTITION SCHEME ps_TestDate
AS PARTITION pf_TestDate
TO (FG1, FG2, FG3)
GO

--4第四步：创建分区表。创建表并将其绑定到分区方案。这里我们建立2个表，表的结构一样。其中TestsHistory表用于保存归档数据。
USE test
GO
CREATE TABLE dbo.Tests
(
TestID int identity(10000,1),
TestDate datetime NOT NULL,
HireID int NOT NULL,
CONSTRAINT PK_Tests PRIMARY KEY (TestID, TestDate)
)
ON ps_TestDate (TestDate)
GO

CREATE TABLE dbo.TestsHistory
(
TestID int identity(10000,1),
TestDate datetime NOT NULL,
HireID int NOT NULL,
CONSTRAINT PK_TestsHistory PRIMARY KEY (TestID, TestDate)
)
ON ps_TestDate (TestDate)
GO

--5向数据表中写入2009年的范例数据
USE test
GO
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2009/6/25', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2009/8/13', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2009/8/25', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2009/9/23', 1000)
GO

--6向数据表中写入2010年的范例数据
USE test
GO
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2010/6/25', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2010/8/13', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2010/8/25', 1000)
INSERT INTO dbo.Tests (TestDate, HireID) VALUES ('2010/9/23', 1000)
GO

--插入完数据后，我们来做如下实验：

--7查询某个分区

--2009年数据
SELECT *
FROM dbo.Tests
WHERE $PARTITION.pf_TestDate(TestDate) = 1

--2010年数据
SELECT *
FROM dbo.Tests
WHERE $PARTITION.pf_TestDate(TestDate) = 2

--查询某个分区有多少条数据
SELECT $PARTITION.pf_TestDate(TestDate) AS Partition,
COUNT(*) AS [COUNT]
FROM dbo.Tests
GROUP BY $PARTITION.pf_TestDate(TestDate)
ORDER BY Partition ;

--通过$PARTITION 函数获得一组分区标示列值的分区号，例如获得2009属于哪个分区
SELECT test.$PARTITION.pf_TestDate('2009')

--8归档数据

--假如现在是2010年年初，那么我们就可以把2009年所有的记录归档到历史表HistoryOrder中
USE test
GO
ALTER TABLE dbo.Tests SWITCH PARTITION 1 TO dbo.TestsHistory PARTITION 1
GO

--查询结果
SELECT * FROM dbo.Tests
SELECT * FROM dbo.TestsHistory

--当然如果到了2011年年初，我们也可以归档2010年的所有交易数据
USE test
GO
ALTER TABLE dbo.Tests SWITCH PARTITION 2 TO dbo.TestsHistory PARTITION 2
GO

--9添加分区
--假如到了2012年年初，我们需要为2012年的交易记录准备分区
USE test
GO
ALTER PARTITION SCHEME ps_TestDate NEXT USED FG2
ALTER PARTITION FUNCTION pf_TestDate() SPLIT RANGE ('2012/01/01')
GO

--10删除分区或合并分区
USE test
GO
ALTER PARTITION FUNCTION pf_TestDate() MERGE RANGE ('2010/01/01')
GO

--查询结果
SELECT test.$PARTITION.pf_TestDate('2010')

SELECT *
FROM dbo.TestsHistory
WHERE $PARTITION.pf_TestDate(TestDate) = 2

SELECT *
FROM dbo.TestsHistory
WHERE $PARTITION.pf_TestDate(TestDate) = 1

--11查看元数据
select * from sys.partition_functions
select * from sys.partition_range_values
select * from sys.partition_schemes

ITPub博客

sql server 2005表分区(partition table)

最新文章