SQL Server 2005 中的分區表和索引應用
SQL Server 2005引入的表分區
表分區步驟：
1. 創建文件組
下面建了三個分區，分別表示2008-2009年的訂單數據，分屬不同的磁盤上面：
2. 為范圍分區創建分區函數
不過這種情況我們可以再程序中處理避免。
現在可以插入數據了
可以改變時間@date的值，然后會發現原來數據庫中的文件并沒有變化，而剛才新建在其他磁盤下的文件會根據你插入的數據的變化。
分區表的操作采用了CPU和I/O的并行操作，檢索數據的數據量也變小了，定位數據所耗時間變短。
查看分區表數據：
如何修改分區表：
比如現在是2010年了，接下來又要新增一個新的分區來保存2011年的數據了，或者保存將來幾年的數據；
1. 新建將來幾年的文件組（方法和上面新建文件組的方法一下）
2. 首先修改分區架構
3. 修改分區函數
在已經存在的表上新建表分區：
目前我的辦法是新建一個表分區，然后把數據導入過去，不知道這個辦法好不好，研究中......
表分區步驟：
1. 創建文件組
下面建了三個分區，分別表示2008-2009年的訂單數據，分屬不同的磁盤上面：
ALTER DATABASE FrameWork ADD FILEGROUP [2009_Order]
ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2009_Order',
FILENAME = N'E:\FrameWork\2009_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2009_Order]
ALTER DATABASE FrameWork ADD FILEGROUP [2008_Order]
ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2008_Order',
FILENAME = N'F:\FrameWork\2008_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2008_Order]
ALTER DATABASE FrameWork ADD FILEGROUP [2010_Order]
ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2010_Order',
FILENAME = N'G:\FrameWork\2010_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2010_Order]
FrameWork 是數據庫名稱 [2009_Order]表示文件名稱 FILENAME 表示文件路徑 ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2009_Order',
FILENAME = N'E:\FrameWork\2009_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2009_Order]
ALTER DATABASE FrameWork ADD FILEGROUP [2008_Order]
ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2008_Order',
FILENAME = N'F:\FrameWork\2008_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2008_Order]
ALTER DATABASE FrameWork ADD FILEGROUP [2010_Order]
ALTER DATABASE FrameWork
ADD FILE
(NAME = N'2010_Order',
FILENAME = N'G:\FrameWork\2010_Order.ndf',
SIZE = 20G,
MAXSIZE = 100G,
FILEGROWTH = 5G)
TO FILEGROUP [2010_Order]
2. 為范圍分區創建分區函數
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20081231 23:59:59.997',
'20091231 23:59:59.997')
使用 datetime 數據類型確實增加了一定的復雜性,復雜性增加是因為 datetime 數據類型具有精度。必須選擇 23:59:59.997 的原因在于，datetime 數據無法保證毫秒級別的精度。相反，datetime 數據的精度在 3.33 毫秒內。使用 23:59:59.999 這個確切的時間值是不行的，因為該值將被舍入到最接近的時間值，即第二天的 12:00:00.000 A.M。由于進行了這種舍入，將無法正確定義邊界。對于 datetime 數據，必須對明確提供的毫秒值加倍小心。 AS
RANGE LEFT FOR VALUES ('20081231 23:59:59.997',
'20091231 23:59:59.997')
不過這種情況我們可以再程序中處理避免。
第一個分區將包含所有小于或等于 '20081231 23:59:59.997' 的值
第二個分區將包含所有小于或等于 '20091231 23:59:59.997' 而且 大于 ‘20081231 23:59:59.997’
第三個分區將包含所有大于或者等于 '20091231 23:59:59.997' 的值
3. 創建分區架構CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([2008_Order], [2009_Order],[2010_Order])
4. 創建分區表 AS
PARTITION OrderDateRangePFN
TO ([2008_Order], [2009_Order],[2010_Order])
--創建分區表
CREATE TABLE [dbo].[OrdersRange]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderName] [nvarchar](100) NULL,
[OrderNumber] [int] NULL,
[OrderTime] [datetime] NULL,
[OrderText] [ntext] NULL
)
ON OrderDatePScheme ([OrderTime])
GO
CREATE TABLE [dbo].[OrdersRange]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderName] [nvarchar](100) NULL,
[OrderNumber] [int] NULL,
[OrderTime] [datetime] NULL,
[OrderText] [ntext] NULL
)
ON OrderDatePScheme ([OrderTime])
GO
現在可以插入數據了
DECLARE @Counter INT
declare @date datetime
SET @Counter = 0
SET @date = '2010-10-1'
WHILE (@Counter < 50000000)
BEGIN
insert into [OrdersRange] ([OrderName],[OrderNumber],[OrderTime],[OrderText])
values('2009 年數據',100,@date,'測試數據測試數據測試數據測試數據')
SET @Counter = @Counter + 1
END
declare @date datetime
SET @Counter = 0
SET @date = '2010-10-1'
WHILE (@Counter < 50000000)
BEGIN
insert into [OrdersRange] ([OrderName],[OrderNumber],[OrderTime],[OrderText])
values('2009 年數據',100,@date,'測試數據測試數據測試數據測試數據')
SET @Counter = @Counter + 1
END
可以改變時間@date的值，然后會發現原來數據庫中的文件并沒有變化，而剛才新建在其他磁盤下的文件會根據你插入的數據的變化。
分區表的操作采用了CPU和I/O的并行操作，檢索數據的數據量也變小了，定位數據所耗時間變短。
查看分區表數據：
SELECT $PARTITION.OrderDateRangePFN(OrderTime) AS OrderTime, count(*) AS [Rows In Partition]
FROM [OrdersRange] GROUP BY $partition.OrderDateRangePFN(OrderTime)
FROM [OrdersRange] GROUP BY $partition.OrderDateRangePFN(OrderTime)
如何修改分區表：
比如現在是2010年了，接下來又要新增一個新的分區來保存2011年的數據了，或者保存將來幾年的數據；
1. 新建將來幾年的文件組（方法和上面新建文件組的方法一下）
2. 首先修改分區架構
PARTITION SCHEME OrderDatePScheme
NEXT USED [2011_Order]
上面修改分區架構表示新增了一個分區NEXT USED [2011_Order]
3. 修改分區函數
ALTER PARTITION FUNCTION OrderDateRangePFN()
SPLIT RANGE ('20101231 23:59:59.997')
完成，注意修改完成后要檢查書序是否正確，在存儲-》分區方案 和 分區函數關系要對應SPLIT RANGE ('20101231 23:59:59.997')
在已經存在的表上新建表分區：
目前我的辦法是新建一個表分區，然后把數據導入過去，不知道這個辦法好不好，研究中......
浙公網安備 33010602011771號