SQL Server 分區表的一些操作
use fenqutest
--------------------
-- 準備分區用的文件組、文件、分區函數、分區方案
--添加文件分組
ALTER DATABASE fenqutest ADD FILEGROUP [test2010]
ALTER DATABASE fenqutest ADD FILEGROUP [test2011]
ALTER DATABASE fenqutest ADD FILEGROUP [test2012]
ALTER DATABASE fenqutest ADD FILEGROUP [test2013]
--ALTER DATABASE fenqutest ADD FILEGROUP [test]
--添加物理文件
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2010',FILENAME = N'D:\sqlserver test\test2010.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2010]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2011',FILENAME = N'D:\sqlserver test\test2011.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2011]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2012',FILENAME = N'D:\sqlserver test\test2012.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2012]
--
ALTER DATABASE fenqutest ADD FILE
(NAME = N'test2013',FILENAME = N'D:\sqlserver test\test2013.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
TO FILEGROUP [test2013]
go
-- 創建分區函數
create partition function fnFenQu(datetime)
as
range right for values (
'2011-01-01',
'2012-01-01',
'2013-01-01')
go
-- right，2010-01-01(不含)前為一組，2010-01-01(含)~2010-12-31一組，2011-01-01(含)~2011-12-31一組，2012-01-01之后一組
-- 創建分區方案，不能使用一個文件組(含多個文件)：分區函數生成的分區多于方案中提到的文件組。
create partition scheme FenQu_Orders
as
partition fnFenQu
to (test2010, test2011, test2012, test2013)
go
--------------------
-- 直接創建分區表
-- 創建分區表
create table dbo.OrdersTest(
OrderID int not null,
CustomerID varchar(10) not null,
EmployeeID int not null,
OrderDate datetime not null
)
on FenQu_Orders(OrderDate)
go
-- 創建聚集分區索引
create clustered index IXC_OrdersTest on dbo.OrdersTest(OrderDate)
go
--清空表數據
truncate table OrdersTest
--插入數據
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (7 ,'gggg',71 ,'2015-10-10 12:20:23')
--查詢數據
select from OrdersTest
--查看每個分區的數據分布情況，分區編號、記錄數、(分區字段)最小值、(分區字段)最大值
--這個查看方法只是關聯查詢(表和分區函數)，并不能保證是分區成功了
SELECT
partition = $partition.fnFenQu(OrderDate),
rows = count(),
minval = min(OrderDate),
maxval = max(OrderDate)
FROM dbo.OrdersTest
GROUP BY $partition.fnFenQu(OrderDate)
ORDER BY partition
--------------------
-- 將普通表轉換成分區表
-- 創建普通表
create table dbo.OrdersTest(
OrderID int not null,
CustomerID varchar(10) not null,
EmployeeID int not null,
OrderDate datetime not null,
CONSTRAINT [PK_OrdersTest_OrderID] PRIMARY KEY CLUSTERED ( --創建主鍵
[OrderID] ASC
)
)
--插入數據
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1 ,'aaaa',11 ,'2010-01-15 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (2 ,'bbbb',21 ,'2010-05-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (3 ,'cccc',31 ,'2011-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (4 ,'dddd',41 ,'2012-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (5 ,'eeee',51 ,'2013-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (6 ,'ffff',61 ,'2010-10-10 12:20:23')
INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (7 ,'gggg',71 ,'2015-10-10 12:20:23')
--查詢數據
select from [fenqutest].[dbo].[OrdersTest]
--開始轉換
--刪掉主鍵
ALTER TABLE OrdersTest DROP CONSTRAINT PK_OrdersTest_OrderID
--重新創建主鍵，但不設為聚集索引
ALTER TABLE OrdersTest ADD CONSTRAINT PK_OrdersTest_OrderID PRIMARY KEY NONCLUSTERED (
[OrderID] ASC
) ON [PRIMARY]
--創建一個新的聚集索引，在該聚集索引中使用分區方案(分區方案的文件組有文件后才能引用分區方案)
CREATE CLUSTERED INDEX IXC_OrdersTest ON dbo.OrdersTest([OrderDate])
ON FenQu_Orders([OrderDate])
--查看每個分區的數據分布情況，分區編號、記錄數、(分區字段)最小值、(分區字段)最大值
SELECT
partition = $partition.fnFenQu(OrderDate),
rows = count(),
minval = min(OrderDate),
maxval = max(OrderDate)
FROM dbo.OrdersTest
GROUP BY $partition.fnFenQu(OrderDate)
ORDER BY partition
--------------------
-----合并表空間-----
-- INSERT INTO [fenqutest].[dbo].OrdersTest VALUES (1111 ,'asdgsfd',113241 ,'2009-01-15 12:20:23')
-- 合并(刪除)分區函數中的臨界值，以此合并分區，數據會放在前一個空間里，如1、2，2的合進1里。
ALTER PARTITION FUNCTION fnFenQu()
MERGE RANGE ('2011-01-01')
--------------------
-- 對分區表的其它操作
-----添加表空間-----
-- ALTER DATABASE fenqutest ADD FILEGROUP [test2014]
-- ALTER DATABASE fenqutest ADD FILE
-- (NAME = N'test2014',FILENAME = N'D:\sqlserver test\test2014.ndf',SIZE = 1MB,MAXSIZE = 10MB,FILEGROWTH = 1MB)
-- TO FILEGROUP [test2014]
-- 分區方案添加下一個文件組，要先準備好文件組(及其文件)
ALTER PARTITION SCHEME FenQu_Orders
NEXT USED [test2014]
-- 分區函數分割(添加)臨界值
ALTER PARTITION FUNCTION fnFenQu()
SPLIT RANGE ('2014-01-01')
-- 改變分區方案和分區函數后，通過查看它們的源碼，發現它們的源碼并沒有改變。
-- 但通過上面的查詢我們發現，合并和添加分區的效果已經產生了了
-- 以上操作使用 SQL Server 2012(數據庫) - Toad for SQL Server(查詢分析器)
--------------------
--------------------
-- 刪除表
DROP TABLE [fenqutest].[dbo].[OrdersTest];
-- 刪除分區方案，刪除分區表(引用關系？)后
DROP PARTITION SCHEME [FenQu_Orders];
-- 刪除分區函數，刪除分區方案后
DROP PARTITION FUNCTION [fnFenQu];
-- 刪除文件，刪除表數據后
ALTER DATABASE fenqutest REMOVE FILE [test2010]
ALTER DATABASE fenqutest REMOVE FILE [test2011]
ALTER DATABASE fenqutest REMOVE FILE [test2012]
ALTER DATABASE fenqutest REMOVE FILE [test2013]
-- 刪除文件組，刪除分區方案及(物理)文件后
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2010]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2011]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2012]
ALTER DATABASE fenqutest REMOVE FILEGROUP [test2013]
--ALTER DATABASE fenqutest REMOVE FILEGROUP [test]