Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Use of Switch partition in real world

Answered Use of Switch partition in real world

  • Tuesday, December 04, 2012 11:51 AM
     
     
    Sign In to Vote
    0

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

     

All Replies

  • Tuesday, December 04, 2012 3:12 PM
     
     
    Sign In to Vote
    0

    Note that switch partition is available in Enterprise Edition.

    These articles may help you:

     
  • Tuesday, December 04, 2012 6:15 PM
     
      Has Code
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    0

    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
     
     Answered Has Code
    Sign In to Vote
    0

    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

    SELECT $Partition.partFn_TestPartition('2012-11-14')

     
  • Tuesday, December 11, 2012 2:38 PM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi,

    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.

    http://dattatraynale.blogspot.com/2012/11/sql-server-partitioning-design-patterns.html

    http://dattatraynale.blogspot.com/2012/11/sql-server-partitioning-design-4.html

    • Proposed As Answer by Dattatray Nale Tuesday, December 11, 2012 2:38 PM
    •