關於SQL Server的分割槽表

關於SQL Server資料分割槽技術是在SQL Server 2005的時候就引入的,這裡我只是想從測試的角度說明怎樣去檢查一個資料庫的分割槽是否正確.

 

我們可以利用下面的程式碼進行資料分割槽的檢查.

SELECT CONVERT( VARCHAR(16), PS.[name] )AS Partition_Scheme,
  P.partition_number,
  CONVERT( VARCHAR(10), DS2.[name])AS PartionFileGroup,
  CONVERT( VARCHAR(19), COALESCE( V.[Value], '' )) AS Range_Boundry,
  STR( P.Rows, 9 ) AS Rows
FROM sys.Indexes I
INNER JOIN sys.partition_schemes  PS ON I.data_space_id  = PS.data_space_id
INNER JOIN sys.destination_data_spaces DDS ON PS.data_space_id = DDS.partition_scheme_id
INNER JOIN sys.data_spaces    DS2 ON DDS.data_space_id = DS2.data_space_id
INNER JOIN sys.partitions    P ON DDS.destination_id = P.partition_number
AND P.[Object_id]  = I.[Object_ID] AND P.index_id    = I.index_id
INNER JOIN sys.partition_functions  PF ON PS.function_id  = PF.function_id
LEFT  JOIN sys.partition_range_values V ON PF.function_id  = V.function_id
AND V.boundary_id  = P.partition_number - pf.boundary_value_on_right
WHERE i.[object_id] = OBJECT_ID( 'PartitionTable' ) AND  i.index_id  = 0
ORDER BY P.partition_number

 

舉個例子:

--Creatinmg Partition Function
CREATE PARTITION FUNCTION PartiFunc( INT )
AS RANGE LEFT FOR VALUES
( 1, 10, 100, 1000, 10000, 100000, 1000000 )

--Creatinmg Partition Scheme
CREATE PARTITION SCHEME PartiSche
AS PARTITION PartiFunc ALL TO ( [PRIMARY] )

--Creatinmg Partition Table
CREATE TABLE dbo.PartitionTable
( RowID INT  IDENTITY(1, 1),
 Number INT
)ON PartiSche ( RowID )

INSERT dbo.PartitionTable
(  Number )
SELECT ROW_NUMBER() OVER( ORDER BY [name] ) AS RID
FROM sys.all_objects

--View of partiton Recrds
SELECT CONVERT( VARCHAR(16), PS.[name] )AS Partition_Scheme, P.partition_number,
  CONVERT( VARCHAR(10), DS2.[name])AS PartionFileGroup,
  CONVERT( VARCHAR(19), COALESCE( V.[Value], '' )) AS Range_Boundry,
  STR( P.Rows, 9 ) AS Rows
FROM sys.Indexes I
  INNER JOIN sys.partition_schemes  PS ON I.data_space_id  = PS.data_space_id
  INNER JOIN sys.destination_data_spaces DDS ON PS.data_space_id = DDS.partition_scheme_id
  INNER JOIN sys.data_spaces    DS2 ON DDS.data_space_id = DS2.data_space_id
  INNER JOIN sys.partitions    P ON DDS.destination_id = P.partition_number AND
              P.[Object_id]  = I.[Object_ID] AND
              P.index_id   = I.index_id
  INNER JOIN sys.partition_functions  PF ON PS.function_id  = PF.function_id
  LEFT  JOIN sys.partition_range_values V ON PF.function_id  = V.function_id AND
              V.boundary_id  = P.partition_number - pf.boundary_value_on_right
WHERE i.[object_id] = OBJECT_ID( 'PartitionTable' ) AND
  i.index_id  = 0
ORDER BY P.partition_number

CREATE PARTITION FUNCTION myPartFn ( NUMERIC (18,2) ) AS RANGE LEFT FOR VALUES( 5000, 10000, 15000, 20000, 25000 )
/* <=5000, >5000 AND <=10000, >10000 AND <=15000, >15000 AND <=20000, >20000 AND <=25000, >25000 */

--CREATE PARTITION FUNCTION myPartFn ( NUMERIC (18,2) ) AS RANGE RIGHT FOR VALUES( 5000, 10000, 15000, 20000, 25000 )
/* <5000, >=5000 AND <10000, >=10000 AND <15000, >=15000 AND <20000, >=20000 AND <25000, >=25000 */

CREATE PARTITION SCHEME myPartSchm AS PARTITION myPartFn ALL TO ([PRIMARY])

SELECT * FROM sys.partition_functions; SELECT * FROM sys.partition_schemes;

CREATE TABLE dbo.myPartitionTable
( EmployeeID INT IDENTITY(1,1), SalaryAmt NUMERIC(18, 2) ) ON myPartSchm( SalaryAmt )

CREATE CLUSTERED INDEX C_INDX_myPartitionTable_00001 ON dbo.myPartitionTable( EmployeeID )
CREATE NONCLUSTERED INDEX NC_INDX_myPartitionTable_00001 ON dbo.myPartitionTable( SalaryAmt )

SELECT * FROM sys.partitions WHERE [object_id] = OBJECT_ID(N'dbo.myPartitionTable')

INSERT INTO dbo.myPartitionTable( SalaryAmt )
SELECT 2000 UNION ALL SELECT 2500 UNION ALL SELECT 3000 UNION ALL SELECT 3500 UNION ALL SELECT 5000 UNION ALL
SELECT 6000 UNION ALL SELECT 7300 UNION ALL SELECT 7500 UNION ALL SELECT 9800 UNION ALL SELECT 8900 UNION ALL
SELECT 11000 UNION ALL SELECT 7890 UNION ALL SELECT 15200 UNION ALL SELECT 17500 UNION ALL SELECT 2500 UNION ALL
SELECT 25000 UNION ALL SELECT 78000 UNION ALL SELECT 65000 UNION ALL SELECT 22000 UNION ALL SELECT 20000

SELECT * FROM dbo.myPartitionTable WHERE SalaryAmt <= 5000

SELECT * FROM sys.partitions WHERE [object_id] = OBJECT_ID(N'dbo.myPartitionTable')

DROP TABLE dbo.myPartitionTable
DROP PARTITION SCHEME myPartSchm
DROP PARTITION FUNCTION myPartFn