The partition table

The basic operation of the partition table and the partition switching to operate the partition table

1 to create the database and add file group

IF DB_ID('TestDB_PT') IS NOT NULL DROP DATABASE TestDB_PT; GO CREATE DATABASE TestDB_PT ON PRIMARY (Name=TestDB_PT, FILENAME='C:\DB\TestDB_PT\TestDB_PT.mdf', SIZE=2MB,MAXSIZE=1024MB,FILEGROWTH=10MB ) LOG ON ( Name=TestDB_PT_LOG, FILENAME='C:\DB\TestDB_PT\TestDB_PT_LOG.ldf', SIZE=1MB,MAXSIZE=1024MB,FILEGROWTH=10MB ) GO USE TestDB_PT GO

Add files to the database TestDB_PT - group

DECLARE @Count INT =4; -- Add to4A file group DECLARE @i INT =0; DECLARE @SQL varchar(1000)=''; WHILE (@i<@Count) BEGIN SET @SQL='ALTER DATABASE TestDB_PT ADD FILEGROUP FG'+convert(varchar(10),@i+1)+';' Exec (@SQL); SET @i=@i+1; END -- Add files to the file group SET @i =0; SET @SQL ='' WHILE (@i<@Count) BEGIN SET @SQL='ALTER DATABASE TestDB_PT ADD FILE( NAME=FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1,FILENAME=''C:\DB\TestDB_PT\FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1.ndf'' ,SIZE=1MB,MAXSIZE=100MB,FILEGROWTH=1MB ) TO FILEGROUP FG'+CONVERT(VARCHAR(10),@i+1) Exec (@SQL) SET @i=@i+1; END

2 create partition function

CREATE PARTITION FUNCTION PFMonthly(int) AS RANGE RIGHT –It is RANGE LEFT. a detailed understanding of RANGE RIGHT enough FOR VALUES(20130601,20130701,20130801)

    The distribution of the data are shown as follows.:

3 create a partition scheme

CREATE PARTITION SCHEME PSMonthly AS PARTITION PFMonthly - partition function TO (FG1, FG2, FG3, FG4) - the corresponding file group number plus 1 partition function for the range of values

The distribution of the data are shown as follows.:

4 create a partitioned table

CREATE TABLE [dbo].[TB]( [DateKey] [int] NOT NULL, [number] [int] NULL ) ON PSMonthly([DateKey]) - here, the corresponding partition scheme and the partitioning column

The partition function, partition, partition table as below:

The 5 switching partition way to fill the partition table

-- Temporary tables need to create when switching IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL DROP TABLE TestDB_PT.dbo.Stage_TB GO CREATE TABLE [dbo].[Stage_TB]( [DateKey] [int] NOT NULL, [number] [int] NULL ) ON FG1 --Must be a file group will load the data corresponding to the -- Add toDateKeyConstraint. This must be added, otherwise it will report a syntax error at the time of handover. This constraint is to prevent switching when the need to switch the data in a temporary table to a different partition. ALTER TABLE [dbo].[Stage_TB] ADD CONSTRAINT CK_Stage_TB_DateKey CHECK(DateKey>= 20130501and DateKey<20130601) -- Add dataStage_TB insert into dbo.Stage_TB SELECT Convert(varchar(20), Dateadd(DAY,number,'2013-05-21'),112 ) DateKey ,number FROM master.dbo.spt_values where type='p' and Dateadd(DAY,number,'2013-05-21') <'20130601' -- ThroughswitchSwitch statement ALTER TABLE Stage_TB SWITCH TO TB PARTITION 1 -- The local partition number can see in front of the query

Before the switch as shown below:

After switching into the:

                

The other partition data loading, and so on. After loading the 5, 6, July data, as shown below:

-- The actual data partition table in the query select $partition.PFMonthly(DateKey) as [Partition#] ,count(*) RowCnt ,Min(DateKey) AS MinDate ,Max(DateKey) AS MaxDate from TB group by $partition.PFMonthly(DateKey) order by [Partition#] -- Delete the temporary table DROP TABLE Stage_TB

The 6 switching partition way to delete the first partition data

-- Delete20130501To20130531Data -- Temporary tables need to create when switching IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL DROP TABLE TestDB_PT.dbo.Stage_TB GO CREATE TABLE [dbo].[Stage_TB]( [DateKey] [int] NOT NULL, [number] [int] NULL ) ON FG1 --Must be a file group will load the data corresponding to the -- ThroughswitchSentence, Switching data to a temporary table ALTER TABLE TB SWITCH PARTITION 1 TO Stage_TB -- The local partition number can see in front of the query -- Check out the corresponding partition value SELECT SPS.name AS PartitionSchemeName , CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id ELSE NULL END AS PartitionID , SPF.name AS PartitionFunctionName , SPRV.value AS BoundaryValue , CASE WHEN SDD.destination_id > SPF.fanout THEN 1 ELSE 0 END AS NextUsed , SF.name AS FileGroup FROM sys.partition_schemes AS SPS JOIN sys.partition_functions AS SPF ON SPS.function_id = SPF.function_id JOIN sys.destination_data_spaces AS SDD ON SDD.partition_scheme_id = SPS.data_space_id JOIN sys.filegroups AS SF ON SF.data_space_id = SDD.data_space_id LEFT JOIN sys.partition_range_values AS SPRV ON SPRV.function_id = SPF.function_id AND SDD.destination_id = CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id ELSE SPRV.boundary_id + 1 END WHERE SPS.name = 'PSMonthly'; -- Removed from the corresponding values in the partition function ALTER PARTITION FUNCTION PFMonthly() MERGE RANGE(20130601) -- Delete the temporary table DROP TABLE Stage_TB

Remove front as shown below:

                

After delete as below:

Note: the RANGE RIGHT partition table. The merger of the two adjacent partition, data stored in the left partition corresponding data file group. The partition 1 and division 2 after the merger is stored in the FG1 .

The 7 switching partition way to add data in August

- set FG2 to NEXT USED, add a new split partition, the new partition data will be stored in the FG2 ALTER PARTITION SCHEME [PSMonthly] NEXT USED FG2
Add a new partition value --
ALTER PARTITION FUNCTION PFMonthly()
SPLIT RANGE(20130901)

Add a new partition value:

Add a new partition value:

The temporary table switching partition load data in August, according to “ 5 for switching partition way to fill the partition table”.

For more information see

Posted by Norman at November 25, 2013 - 11:50 AM