Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Need examples related to partition switching

Discussion Need examples related to partition switching

  • Tuesday, December 04, 2012 2:28 PM
     
     
    Sign In to Vote
    0

    I come across partition switching in below path of MSDN Library. 

    http://msdn.microsoft.com/en-us/library/ms191174(v=sql.105).aspx

     I am interested to see some examples related the same. Please Help me out if you come across such examples and if you have any comments on that please share with me. I am waiting for your valuable suggestions and replies.

    Thanks

     

All Replies

  • Tuesday, December 04, 2012 6:21 PM
     
      Has Code
    Sign In to Vote
    0
    Same answer as your other question on the subject Use of Switch partition in real world

    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

    • Edited by Daryl Wenman-Bateson Thursday, December 06, 2012 11:09 AM tie example solution to question
    •