SQL Server 2005 中的分割槽表和索引應用

iSQlServer發表於2009-08-14
SQLServer索引
 SQL Server 2005引入的表分割槽技術，讓使用者能夠把資料分散存放到不同的物理磁碟中，提高這些磁碟的並行處理效能以優化查詢效能。 
      
       表分割槽步驟：
       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 表示檔案路徑       
　　 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 資料，必須對明確提供的毫秒值加倍小心。  
     不過這種情況我們可以再程式中處理避免。

      第一個分割槽將包含所有小於或等於 '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. 建立分割槽表 
--建立分割槽表
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

可以改變時間@date的值，然後會發現原來資料庫中的檔案並沒有變化，而剛才新建在其他磁碟下的檔案會根據你插入的資料的變化。
分割槽表的操作採用了CPU和I/O的並行操作，檢索資料的資料量也變小了，定位資料所耗時間變短。

 檢視分割槽表資料：
SELECT $PARTITION.OrderDateRangePFN(OrderTime) AS OrderTime, count(*) AS [Rows In Partition]
       
FROM [OrdersRange] GROUP BY $partition.OrderDateRangePFN(OrderTime)

如何修改分割槽表：
比如現在是2010年了，接下來又要新增一個新的分割槽來儲存2011年的資料了，或者儲存將來幾年的資料；
     1. 新建將來幾年的檔案組（方法和上面新建檔案組的方法一下）
     2. 首先修改分割槽架構
 PARTITION SCHEME OrderDatePScheme
NEXT USED [2011_Order]
       上面修改分割槽架構表示新增了一個分割槽
     3. 修改分割槽函式
ALTER PARTITION FUNCTION OrderDateRangePFN() 
SPLIT RANGE ('20101231 23:59:59.997')
       完成，注意修改完成後要檢查書序是否正確，在儲存-》分割槽方案  和 分割槽函式關係要對應

在已經存在的表上新建表分割槽：
目前我的辦法是新建一個表分割槽，然後把資料匯入過去，不知道這個辦法好不好，研究中......

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-612309/，如需轉載，請註明出處，否則將追究法律責任。

相關文章