[MS SQL]Patition

https://dotblogs.com.tw/jamesfu/archive/2012/12/25/partitiontable.aspx?fid=77758
https://dotblogs.com.tw/ricochen/2012/05/04/71971

https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-partition-scheme-transact-sql

-- 依據 Partition Function 建立 Partition Schema

create partition scheme psPartitionTest

as partition pfPartitionTest

ALL TO ([ PRIMARY ] );

--create partition function

CREATE PARTITION FUNCTION myRangePF1 (int)  

AS RANGE LEFT FOR VALUES (1, 100, 1000);  

GO  

--create partition scheme

CREATE PARTITION SCHEME myRangePS1  

AS PARTITION myRangePF1  

ALL TO ( [PRIMARY] );  

--檢查是否建立成功

select * from sys.partition_functions;

select * from sys.partition_schemes;

-- 建立 Table 一開始就使用 Partition Schema

CREATE TABLE [apuser].[A](

    [MOUDLE] [nvarchar](4) NOT NULL,

    [SEQ] int NOT NULL,

[CONTENT] [nvarchar](100),

 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 

(

    [MOUDLE] ASC,

    [SEQ] ASC

)

) ON myRangePS1(SEQ);

--insert 測試資料

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',1,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',100,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',101,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',999,'testtest');

insert into [apuser].A(MOUDLE,SEQ,CONTENT)

values('RB',999999,'testtest');

--檢查是否建於正確的partition

SELECT t.name,p.object_id,p.partition_id,p.rows 

FROM sys.partitions AS p

JOIN sys.tables AS t

ON  p.object_id = t.object_id

WHERE p.partition_id IS NOT NULL

AND t.name = 'A';

--取得某一資料的資料分割編號

SELECT $PARTITION.myRangePF1 (988) ;

--取得每個資料分割筆數(is not null)

SELECT $PARTITION.myRangePF1(SEQ) AS Partition, 

COUNT(*) AS [COUNT] FROM A 

GROUP BY $PARTITION.myRangePF1(SEQ)

ORDER BY Partition ;

--取得 partition table 分割界限值

SELECT t.name AS TableName, i.name AS IndexName,r.value AS BoundaryValue , p.partition_number, 

p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.partitions AS p

    ON i.object_id = p.object_id AND i.index_id = p.index_id 

JOIN  sys.partition_schemes AS s 

    ON i.data_space_id = s.data_space_id

JOIN sys.partition_functions AS f 

    ON s.function_id = f.function_id

LEFT JOIN sys.partition_range_values AS r 

    ON f.function_id = r.function_id and r.boundary_id = p.partition_number

WHERE t.name = 'A' AND i.type <= 1

ORDER BY p.partition_number;

--取得 partition column

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, 

c.name AS PartitioningColumnName 

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.columns AS c

    ON t.object_id = c.object_id

JOIN sys.partition_schemes AS ps

    ON ps.data_space_id = i.data_space_id

JOIN sys.index_columns AS ic

ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0 

WHERE t.name = 'A'

AND i.type <= 1

AND c.column_id = 1;

--傳回資料分割=2 相關資料

SELECT * FROM A

WHERE $PARTITION.myRangePF1(SEQ) = 2 ;

--取得所有 partition table的 partition function 、scheme和column name

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,    

c.name PartColumn,      

ps.name PartScheme,    

pf.name PartFunction 

FROM sys.data_spaces  d JOIN      

sys.indexes i JOIN   

(SELECT DISTINCT OBJECT_ID     

FROM sys.partitions       

WHERE partition_number > 1) p    

ON i.OBJECT_ID = p.OBJECT_ID      

ON d.data_space_id = i.data_space_id     

JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id    

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id      

JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID  

JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id;