0 Partition Table Example

 

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.

1. USE Master;
2. GO
3. IF EXISTS (
4. SELECT name
5. FROM sys.databases
6. WHERE name = N'PartitoinTest')
7. DROP DATABASE PartitoinTest;
8. GO
9. CREATE DATABASE PartitoinTest
10. ON
11. Primary
12. (NAME='FG1F1',FILENAME= 'C:\Data\PartitoinTest_FG1F1.mdf',SIZE=2, MAXSIZE=100,FILEGROWTH=1 ),
13. FILEGROUP FG2
14. (NAME = 'FG2F1',FILENAME ='C:\Data\PartitoinTest_FG2F1.ndf',SIZE =2,MAXSIZE=100,FILEGROWTH=1 )
15. LOG ON
16. ( NAME = N'PartitoinTest_log', FILENAME = N'C:\LOG\PartitoinTest_log.ldf' , SIZE = 10 , FILEGROWTH = 10%)
17. GO

Create the partition function,TransactionsPS1 and TransactionArchivePS2

1. USE [PartitoinTest]
2. GO
3. CREATE PARTITION FUNCTION TransactionRangePF1 (datetime)
4. AS RANGE RIGHT FOR VALUES ('9/01/2003','10/01/2003', '11/01/2003', '12/01/2003',
5.                '1/01/2004', '2/01/2004', '3/01/2004', '4/01/2004',
6.                '5/01/2004', '6/01/2004', '7/01/2004', '8/01/2004');
7. GO
8. --Range partition table TransactionHistoryArchive
9. CREATE PARTITION FUNCTION TransactionArchivePF2 (datetime)
10. AS RANGE RIGHT FOR VALUES ('9/01/2003');
11. GO

Create the partition schema

1. CREATE PARTITION SCHEME TransactionsPS1
2. AS PARTITION TransactionRangePF1
3. TO ([PRIMARY], [PRIMARY], [PRIMARY]
4. , [PRIMARY], [PRIMARY], [PRIMARY]
5. , [PRIMARY], [PRIMARY], [FG2]
6. , [FG2], [FG2], [FG2]
7. , [FG2], [FG2]);
8. GO
9. CREATE PARTITION SCHEME TransactionArchivePS2
10. AS PARTITION TransactionArchivePF2
11. TO ([PRIMARY], [PRIMARY], [PRIMARY]);
12. GO

Create tables and bulk insert the data

1. CREATE TABLE [Production].[TransactionHistory](
2.     [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
3.     [ProductID] [int] NOT NULL,
4.     [ReferenceOrderID] [int] NOT NULL,
5.     [ReferenceOrderLineNumber] [smallint] NOT NULL CONSTRAINT [DF_TransactionHistory_ReferenceOrderLineNumber] DEFAULT (0),
6.     [TransactionDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_TransactionDate] DEFAULT (GETDATE()),
7.     [TransactionType] [nchar](1) NOT NULL,
8.     [Quantity] [int] NOT NULL,
9.     [ActualCost] [money] NOT NULL,
10.     [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_TransactionHistory_ModifiedDate] DEFAULT (GETDATE()),
11.     CONSTRAINT [CK_TransactionHistory_TransactionType] CHECK ([TransactionType] IN ('W', 'S', 'P', 'w', 's', 'p'))
12. ) ON TransactionsPS1 (TransactionDate);
13.  
14.  
15. IF OBJECT_ID('[Production].[TransactionHistoryArchive]', 'U') IS NOT NULL
16.     DROP TABLE [Production].[TransactionHistoryArchive];
17. GO
18.  
19. CREATE TABLE [Production].[TransactionHistoryArchive](
20.     [TransactionID] [int] IDENTITY (1, 1) NOT NULL,
21.     [ProductID] [int] NOT NULL,
22.     [ReferenceOrderID] [int] NOT NULL,
23.     [ReferenceOrderLineNumber] [smallint] NOT NULL ,
24.     [TransactionDate] [datetime] NOT NULL ,
25.     [TransactionType] [nchar](1) NOT NULL,
26.     [Quantity] [int] NOT NULL,
27.     [ActualCost] [money] NOT NULL,
28.     [ModifiedDate] [datetime] NOT NULL
29. ) ON TransactionArchivePS2 (TransactionDate);
30.  
31.  
32. DECLARE
33.      @retcode INT
34.     ,@data_path NVARCHAR(256)
35. EXECUTE @retcode = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
36.     'Software\Microsoft\Microsoft SQL Server\Setup',
37.     'SQLPath',
38.     @param = @data_path OUTPUT
39. SELECT @data_path = LEFT(@data_path, PATINDEX('%\MSSQL%', @data_path)) + '100\Tools\Samples\AdventureWorks OLTP\'
40.  
41. EXECUTE (N'BULK INSERT [PartitoinTest].[Production].[TransactionHistoryArchive] FROM ''' + @data_path + N'TransactionHistoryArchive.csv''
42. WITH (
43.    CODEPAGE=''ACP'',
44.    DATAFILETYPE = ''char'',
45.    FIELDTERMINATOR= ''\t'',
46.    ROWTERMINATOR = ''\n'' ,
47.    KEEPIDENTITY,
48.    TABLOCK   
49. )');
50. EXECUTE (N'BULK INSERT [PartitoinTest].[Production].[TransactionHistory] FROM ''' + @data_path + N'TransactionHistory.csv''
51. WITH (
52.    CODEPAGE=''ACP'',
53.    DATAFILETYPE = ''char'',
54.    FIELDTERMINATOR= ''\t'',
55.    ROWTERMINATOR = ''\n'' ,
56.    KEEPIDENTITY,
57.    TABLOCK   
58. )');

Exam partition information

1. SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistoryArchive')

1. SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistory')

Range and its partition

1. 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

1. 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.

1. SELECT $Partition.TransactionRangePF1(TransactionDate) AS Partition,
2. COUNT(*) AS [COUNT] FROM Production.TransactionHistory
3. GROUP BY $partition.TransactionRangePF1(TransactionDate)
4. ORDER BY Partition ;

To query the row from specific partition

1. SELECT * FROM Production.TransactionHistory
2. WHERE $PARTITION.TransactionRangePF1(TransactionDate) = 5 ;

Add another partition to the TransactionHistoryArchive table.

1. 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.

1. ALTER TABLE Production.TransactionHistory SWITCH PARTITION 2 TO Production.TransactionHistoryArchive PARTITION 2

 

1. SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistoryArchive')
2. SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Production.TransactionHistory')
3. -- checck after switch
4. 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.

1. ALTER PARTITION FUNCTION TransactionRangePF1() MERGE RANGE('9/01/2003')

Les check the partition again

1. 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.

1. 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

1. 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

http://www.sqlskills.com/resources/whitepapers/partitioning%20in%20sql%20server%202005%20beta%20ii.htm