Tuesday, December 04, 2012 11:51 AM
Can anyone help me out about usage of switch partition in sql server 2008 R2. I don't have much idea on this.
Actually, I need to perform incremental load on the target table. I am loading data into staging table and need to switch data from stage table to fact table.
My client asked to implement switch partition concept. I am not aware of how it is implemented.
Any help/suggestions are welcome.
thanks in advance..
Tuesday, December 04, 2012 3:12 PM
Note that switch partition is available in Enterprise Edition.
These articles may help you:
Tuesday, December 04, 2012 6:15 PM
1. Create Partition Function
CREATE PARTITION FUNCTION partFn_TestPartition (DateTime) as RANGE RIGHT FOR VALUES ( N'2012-11-12T00:00:00.000',N'2012-11-13T00:00:00.000',N'2012-11-14T00:00:00.000',N'2012-11-15T00:00:00.000',N'2012-11-16T00:00:00.000') GO
2. Create Partition Scheme (with one additional slot than the Function)
CREATE PARTITION SCHEME [partSchm_TestPartition] AS PARTITION partFn_TestPartition TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY] [PRIMARY]) GO
3. Create Switch Table for moving data your partitions into the target ensuring that the definition is the same and the partition column is not null
CREATE TABLE [Parition].[TestPartition_PartitionSwitch]( [AccessDate] [datetime] NOT NULL, [Country] varchar(50) not null, [Amount] int not null, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE CLUSTERED INDEX [IX_TestPartition_PartDate] ON [Parition].[TestPartition_PartitionSwitch] ( AccessDate ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
4. Create target table
CREATE TABLE [dbo].[TestPartition_Target]( [AccessDate] [datetime] NOT NULL, [Country] varchar(50) not null, [Amount] int not null, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] CREATE CLUSTERED INDEX [IX_TestPartition_PartDate] ON dbo.[TestPartition_Target] ( AccessDate ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [partSchm_TestPartition](AccessDate) GO
You will need to repeat the following per parition
5. To insert data, load the PartitionSwitch table with one partitions worth of data from stage
INSERT INTO [Parition].[TestPartition_PartitionSwitch] ( [AccessDate],Country,Amount) SELECT [AccessDate],Country, Amount FROM [dbo].StageTable WHERE ControlId=4
6. Create a Constraint on the ParitionSwitch to validate the partition changing the partition check values (AccessDate) accordingly
ALTER TABLE [Parition].[TestPartition_PartitionSwitch] ADD CONSTRAINT [DF_TestPartition_PartitionSwitch_cc_AccessDate] CHECK ([AccessDate] >= '2012-11-12T00:00:00.000' and [AccessDate]<'2012-11-13T00:00:00.000') ALTER TABLE [Parition].[TestPartition_PartitionSwitch] WITH CHECK CHECK CONSTRAINT [DF_TestPartition_PartitionSwitch_cc_AccessDate]
7. Switch the data into your target table changing the Partition number accordingly
BEGIN TRANSACTION ALTER TABLE [Parition].[TestPartition_PartitionSwitch] SWITCH TO [dbo].[TestPartition_Target]PARTITION 2 COMMIT TRANSACTION
You should remove the check constraint and repeat 5, 6 & 7 per partition
Wednesday, December 05, 2012 5:23 AM
Thanks for the response.
From the Above Example, I need to specify the partition number of the stage table and partition number of target table(from and to). But, here I want to perform switch operation dynamically.
Depending up on the data exist in the stage table I need to switch that data to the fact table. But from the above examples I am able to switch by specifying partition number.
I Understood that I need to write procedure... I am not how it works..
Please help me if you come across any such scenario's.
Actual requirement of this is- when we process data of dec 4th and target table will be monthly partition and data loads from dec 1st to dec 3rd. This will be loaded to staging table.
Next, Need to switch staging data with dec partition of fact table.
In next run, i.e., on dec 5th- data process from dec 1st - dec 4th to staging table. Now need to remove dec 1st to dec 3rd data in fact table and need to switch staging table to fact table
This need to be done by using switch partition dynamically
Thursday, December 06, 2012 11:07 AM
You can select the partition number based on your date using $Partition. Amend the following so that the date is selected from the ETL routine as you load each day or month and store the results in a variable. Use that variable as the partition number
Tuesday, December 11, 2012 2:38 PM
Switching is just one part of it. Looks like you want to understand partitioning and the implementations. The below blog links will definately help you. Please comment if you would have any questions.
- Proposed As Answer by Dattatray Nale Tuesday, December 11, 2012 2:38 PM