Step 1
CREATE PARTITION FUNCTION PF_Fact_test (int)
AS RANGE
LEFT FOR VALUES
(20140101,20150101,20160101);
]
Step 2
CREATE PARTITION SCHEME [PS_iGPT_FACT] AS PARTITION [PF_iGPT_FACT] TO ([iGPT2014], [iGPT2015], [iGPT2016],[iGPT2017])
Step 3
Assign Partition Scheme and Partition Funtion to a table
USE [iGPT]
GO
BEGIN TRANSACTION
CREATE CLUSTERED INDEX [ClusteredIndex_on_PS_Fact_Test_636351103815297616] ON [dbo].[iGPTextraInfo]
([MaxDateAces])WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PS_Fact_Test]([MaxDateAces])
DROP INDEX [ClusteredIndex_on_PS_Fact_Test_636351103815297616] ON [dbo].[iGPTextraInfo]
COMMIT TRANSACTION
Step 4
Load data into the partitioned table
–The following query returns one or more rows if the table PartitionTable is partitioned
SELECT *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] IN (0,1)
JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = ‘GPTClaimPendedFact’;
— The following query returns the boundary values for each partition in the PartitionTable table.
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number,p.rows AS Rows, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue
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 = ‘iGPTInquiryFact_WGS’ AND i.type <= 1
ORDER BY p.partition_number;
–The following query returns the name of the partitioning column for table. PartitionTable.
SELECT
t.[object_id] AS ObjectID
, 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] AND i.[type] <= 1 — clustered index or a heap
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 >= 1 — because 0 = non-partitioning column
JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id
WHERE t.name = ‘GPTClaimPendedFact’ ;
SELECT row_count ,*
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(‘dbo.GPTClaimPendedFact’) –and row_count > 0
order by partition_number
SELECT * FROM sys.dm_db_partition_stats;
SELECT SUM(used_page_count) AS total_number_of_used_pages,
SUM (row_count) AS total_number_of_rows
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(‘dbo.GPTClaimPendedFact’) AND (index_id=0 or index_id=1);
Leave a Reply