sqlserver 2008表分區操作

表分區操作步驟

1、設計表進行分區的方案，水準分區、垂直分區

       a、水準切割将減少表的行數，這樣可以将曆史資料歸檔，減少表大小，提高通路速度。

       b、垂直切割将分為主表和從表方式，将主要的列字段存放在主表中，次要的列字段存放在從表中，減少對不必要字段的通路和存放，隻在需要的時候進行聯表查詢

2、根據業務規則确定按照日期或其他的分區原則選擇分區列

3、根據選擇的分區列，确定分區數，建立對應的檔案組和資料檔案數

      最好一個分區對應一個檔案組和一個資料檔案，分區資料檔案最好分布在不同的磁盤上，這樣有利于資料庫并行操作，特别是多核cpu對I/O的通路

4、建立分區函數

      參見下面說明

5、建立分區架構

      參見下面說明

6、建立分區表

      參見下面說明

7、建立分區索引，分區聚集唯一索引

       a、分區索引可以使用不同的分區架構，但必須使用相同的分區函數，存儲在不同的檔案組中，但是這樣講導緻索引與資料表無法對齊。

       b、建議盡量使用相同的分區架構，確定索引和資料表對齊，這樣特别有利于滑動視窗方式的查詢操作。

       c、大型的分區表要有唯一聚集索引或唯一索引

             唯一索引必須包含分區列，這樣才能讓sqlserver隻通路需要的分區

表分區與分表操作的差別

• 在sqlserver 2005之前，隻能使用分表方式進行分區操作，将大表拆分成多個小表，然後通過union 方式拼接成一個視圖。這種方式将給開發人員新增工作量。當需要插入更新表記錄時，需要查找記錄所在的資料表。

• 在sqlserver 2005之後，微軟提供表分區操作，将大表拆分成多個實際小表，大表變成邏輯表，對開發人員而言操作邏輯表與原來大表是一樣的，不受影響。

建立表分區架構

CREATE PARTITION SCHEME [Sln_Partition_Tbl_IntegeralLog]
AS PARTITION [Fn_Partition_Tbl_IntegeralLog] 
TO ([檔案組名1], [檔案組名2], [檔案組名3])
GO
           

修改表分區架構

删除表分區架構

IF  EXISTS 
(
    SELECT * 
    FROM sys.partition_schemes WHERE name = N'Sln_Partition_Tbl_IntegeralLog'
)
DROP PARTITION SCHEME [Sln_Partition_Tbl_IntegeralLog]
GO

           

建立表分區函數

CREATE PARTITION FUNCTION [Fn_Partition_Tbl_IntegeralLog](datetime) 
AS RANGE LEFT 
FOR VALUES (N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2014-01-01T00:00:00.000')
GO
           

修改表分區函數

删除表分區函數

IF  EXISTS 
(
    SELECT * 
    FROM sys.partition_functions 
    WHERE name = N'Fn_Partition_Tbl_IntegeralLog'
)
DROP PARTITION FUNCTION [Fn_Partition_Tbl_IntegeralLog]
GO
           

查詢表分區資料分布情況

select  $partition.Fn_Partition_Tbl_IntegeralLog(tC_Time) [分區編号],
          count(*) [分區編号],
          min(tC_Time) [起始分界],
          max(tC_Time) [終止分界]
  from dbo.Tbl_IntegeralLog
 group by $partition.Fn_Partition_Tbl_IntegeralLog(tC_Time)
           

建立分區表

CREATE TABLE Tbl_IntegeralLog
(
    tC_Id   INT is not null,
    tC_Time datetime default getdate(),
    PRIMARY KEY (ID)
) ON Fn_Partition_Tbl_IntegeralLog(tC_Time)
GO
           

查詢表記錄配置設定到哪個分區中

SELECT *, $PARTITION.Fn_Partition_Tbl_IntegeralLog(tC_Time)
FROM Tbl_IntegeralLog
           

查詢分區2中的記錄資訊

select * 
from Tbl_IntegeralLog 
where $PARTITION.Fn_Partition_Tbl_IntegeralLog([tC_Time])= 2
           

查詢分區函數、分區邊界值、分區架構

select * from sys.partition_functions
select * from sys.partition_range_values
select * from sys.partition_schemes
           

根據分區字段的值查詢出分區号

SELECT $PARTITION.Fn_Partition_Tbl_IntegeralLog('2011-01-01')
           

以下目錄視圖包含資料庫、表和索引級别的分區資訊，以及有關單個分區函數和分區方案的資訊。

擷取有關單個分區函數的資訊

• sys.partition_functions (Transact-SQL)

擷取有關分區函數的單個參數的資訊

• sys.partition_parameters (Transact-SQL)

擷取有關分區函數邊界值的資訊

• sys.partition_range_values (Transact-SQL)

擷取有關資料庫中所有分區方案的資訊

• sys.partition_schemes (Transact-SQL)
• sys.data_spaces (Transact-SQL)

擷取有關單個分區方案的資訊

• sys.destination_data_spaces (Transact-SQL)

擷取有關資料庫中所有分區的資訊

• sys.partitions (Transact-SQL)

擷取有關表或索引的分區資訊

• sys.tables (Transact-SQL)
• sys.indexes (Transact-SQL)
• sys.index_columns (Transact-SQL)