Prepare the sample database for testing.
In real world, you would like to put the partition in the different file group and drive to get more performance and flexibility.
In the example, I am going to create 2 tables. TransactionHistory and TransactionHistoryArchive. TransactionHistory will have 13 partitions and TransactionHistoryArchive will have 2 partitions. After the data load to the tables. I am going to move one of the TransactionHistory partition to TransactionHistoryArchive.
- USE Master;
- GO
- IF EXISTS (
- SELECT name
- FROM sys.databases
- WHERE name = N'PartitoinTest')
- DROP DATABASE PartitoinTest;
- GO
- CREATE DATABASE PartitoinTest
- ON
- Primary
- (NAME='FG1F1',FILENAME= 'C:\Data\PartitoinTest_FG1F1.mdf',SIZE=2, MAXSIZE=100,FILEGROWTH=1 ),
- FILEGROUP FG2
- (NAME = 'FG2F1',FILENAME ='C:\Data\PartitoinTest_FG2F1.ndf',SIZE =2,MAXSIZE=100,FILEGROWTH=1 )
- LOG ON
- ( NAME = N'PartitoinTest_log', FILENAME = N'C:\LOG\PartitoinTest_log.ldf' , SIZE = 10 , FILEGROWTH = 10%)
- GO
Create the partition function,TransactionsPS1 and TransactionArchivePS2
- USE [PartitoinTest]
- GO
- CREATE PARTITION FUNCTION TransactionRangePF1 (datetime)
- AS RANGE RIGHT FOR VALUES ('9/01/2003','10/01/2003', '11/01/2003', '12/01/2003',
- '1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004',
- '5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
- GO
- --Range partition table TransactionHistoryArchive
- CREATE PARTITION FUNCTION TransactionArchivePF2 (datetime)
- AS RANGE RIGHT FOR VALUES ('9/01/2003');
- GO
Create the partition schema
- CREATE PARTITION SCHEME TransactionsPS1
- AS PARTITION TransactionRangePF1
- TO ([PRIMARY], [PRIMARY], [PRIMARY]
- , [PRIMARY], [PRIMARY], [PRIMARY]
- , [PRIMARY], [PRIMARY], [FG2]
- , [FG2], [FG2], [FG2]
- , [FG2], [FG2]);
- GO
- CREATE PARTITION SCHEME TransactionArchivePS2
- AS PARTITION TransactionArchivePF2
- TO ([PRIMARY], [PRIMARY], [PRIMARY]);
- GO
Create tables and bulk insert the data
- CREATE TABLE [Production].[TransactionHistory](
- [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
- [ProductID] [int] NOT NULL,
- [ReferenceOrderID] [int] NOT NULL,
- [ReferenceOrderLineNumber] [smallint] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineNumber] DEFAULT (0),
- [TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate] DEFAULT (GETDATE()),
- [TransactionType] [nchar](1) NOT NULL,
- [Quantity] [int] NOT NULL,
- [ActualCost] [money] NOT NULL,
- [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate] DEFAULT (GETDATE()),
- CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK ([TransactionType] IN ('W', 'S', 'P', 'w', 's', 'p'))
- ) ON TransactionsPS1 (TransactionDate);
- IF OBJECT_ID('[Production].[TransactionHistoryArchive]', 'U') IS NOT NULL
- DROP TABLE [Production].[TransactionHistoryArchive];
- GO
- CREATE TABLE [Production].[TransactionHistoryArchive](
- [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
- [ProductID] [int] NOT NULL,
- [ReferenceOrderID] [int] NOT NULL,
- [ReferenceOrderLineNumber] [smallint] NOT NULL ,
- [TransactionDate] [datetime] NOT NULL ,
- [TransactionType] [nchar](1) NOT NULL,
- [Quantity] [int] NOT NULL,
- [ActualCost] [money] NOT NULL,
- [ModifiedDate] [datetime] NOT NULL
- ) ON TransactionArchivePS2 (TransactionDate);
- DECLARE
- @retcode INT
- ,@data_path NVARCHAR(256)
- EXECUTE @retcode = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
- 'Software\Microsoft\Microsoft SQL Server\Setup',
- 'SQLPath',
- @param = @data_path OUTPUT
- SELECT @data_path = LEFT(@data_path, PATINDEX('%\MSSQL%', @data_path)) + '100\Tools\Samples\AdventureWorks OLTP\'
- EXECUTE (N'BULK INSERT [PartitoinTest].[Production].[TransactionHistoryArchive] FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
- WITH (
- CODEPAGE=''ACP'',
- DATAFILETYPE = ''char'',
- FIELDTERMINATOR= ''\t'',
- ROWTERMINATOR = ''\n'' ,
- KEEPIDENTITY,
- TABLOCK
- )');
- EXECUTE (N'BULK INSERT [PartitoinTest].[Production].[TransactionHistory] FROM ''' + @data_path + N'TransactionHistory.csv''
- WITH (
- CODEPAGE=''ACP'',
- DATAFILETYPE = ''char'',
- FIELDTERMINATOR= ''\t'',
- ROWTERMINATOR = ''\n'' ,
- KEEPIDENTITY,
- TABLOCK
- )');
Exam partition information
- SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistoryArchive')
- SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistory')
Range and its partition
- select boundary_id,value, pf.name AS PartitionFunction,pf.function_id,type_desc from sys.partition_functions pf join SYS.PARTITION_RANGE_VALUES pr on pf.function_id = pr.function_id
Check particular belong to which partition
- SELECT $PARTITION.TransactionRangePF1 ('12/11/2003') ;
The result for the above query is 5.
We can also use $Partition to check the partition information. Below query will display the row counts in each partition on the TransactionDate table.
- SELECT $Partition.TransactionRangePF1(TransactionDate) AS Partition,
- COUNT(*) AS [COUNT] FROM Production.TransactionHistory
- GROUP BY $partition.TransactionRangePF1(TransactionDate)
- ORDER BY Partition ;
To query the row from specific partition
- SELECT * FROM Production.TransactionHistory
- WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;
Add another partition to the TransactionHistoryArchive table.
- ALTER PARTITION FUNCTION TransactionArchivePF2() SPLIT RANGE('10/01/2003')
Select the range value , you will see the new range is been added.
Move TransactionDate partition2 (2003/9/1 – 2003/10/1) to TransactionHistoryArchive partition2.
- ALTER TABLE Production.TransactionHistory SWITCH PARTITION 2 TO Production.TransactionHistoryArchive PARTITION 2
- SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistoryArchive')
- SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistory')
- -- checck after switch
- SELECT * FROM Production.TransactionHistoryArchive WHERE TransactionDate > '9/1/2003'
As you can see from below result. TransactionHistoryArchive has 11155 rows in the Partition 2. The TransactionHistory does not have partition 2 anymore.
From the TransactionHistory, there is no data in the Partition 1( < 2003/9/1) and no partition 2(2003/9/1 ~ 2003/10/1), We can merge those 2 partitions.
- ALTER PARTITION FUNCTION TransactionRangePF1() MERGE RANGE('9/01/2003')
Les check the partition again
- select boundary_id,value, pf.name AS PartitionFunction,pf.function_id,type_desc from sys.partition_functions pf join SYS.PARTITION_RANGE_VALUES pr on pf.function_id = pr.function_id
As you can see the partition boundary from 12 become 11 which means we only have 12 partitions now.
Lets add another partition to the end the TransactionHistory table.
- ALTER PARTITION FUNCTION TransactionRangePF1() SPLIT RANGE('9/01/2004')
As you can see, after the split, the new partition has been added.
In summery, merge will combine the 2 partitions. Split can split the partitions.
Other notes:
if you get the below error message during the split“
Msg 7707, Level 16, State 1, Line 1
The associated partition function 'TransactionArchivePF2' generates more partitions than there are file groups mentioned in the scheme 'TransactionArchivePS2'.”
The solution is try to Set the Next used [FILEGOUP] before you run the split
- ALTER PARTITION SCHEME TransactionArchivePS2 NEXT USED [FG2]
Reference
Partitioned Tables and Indexes in SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
Breitling replica watches is one of the world's most respected Swiss horologists. replica breitling Founded in 1884 in Saint-Imier, the brand cemented its reputation as an aviation specialist, but is now an authority in diving and chronograph categories, too. Specialist watchmaking All COSC certified chronometers.
ReplyDelete