Table Partition Switch partition part 3

 

Switch in and out

Switch partition is move data in and out between a table using switch feature.

Rules:

• To switch partition IN and OUT, the partition or target table must be empty.

• To switch partition we should have 2 different tables, not same table.

• The source and target tables (or partitions) must have identical columns, ordering of column, indexes and use the same partition column.

• The source and target tables (or partitions) must exist on the same filegroup

• To switch a partition table from the partition table to a non partition table we should have check constraint created based on the data boundary.

• The receiving table must already exist in the table, be empty, and have the same column structure, indexes, constraints, and partition scheme the source table.

• Both the source and destination tables or partitions must be in the same file group, the pointer switch which happens behind the scenes is not possible without this.
• There cannot be any XML or Full Text indexes on the source table.

• There can be no foreign key relationships between the source and destination tables, nor can there be any foreign key relationships which reference the source table.

• Tables involved in the partition switch can not be sources of replication.

• Triggers cannot be fired during the switch.

• Full details on the rules of partition switching can be found on MSDN https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191160(v=sql.105)?redirectedfrom=MSDN

 

Here is the General Requirements for Switching tables and partitions.

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191160(v=sql.105)

Learn more about switching.

https://www.cathrinewilhelmsen.net/table-partitioning-in-sql-server-partition-switching/#switch1

 

--1. Switch from Non-Partitioned to Non-Partitioned
ALTER TABLE Source SWITCH TO Target; 

--2. Load data by switching in: Switch from Non-Partitioned to Partition
ALTER TABLE Source SWITCH TO Target PARTITION 1; 

--3. Archive data by switching out: Switch from Partition to Non-Partitioned
ALTER TABLE Source SWITCH PARTITION 1 TO Target; 

--4. Switch from Partition to Partition
ALTER TABLE Source SWITCH PARTITION 1 TO Target PARTITION 1; 

Test Code:

Use the same script used in part 1 for creating database and table.

 

use Test_Tbl_Partition

--============
-- Switch
--============

-- Switch OUT

-- create target table to switch out data (Move data from partition table to other table)
-- drop table tbl_Target
CREATE TABLE tbl_Target (
ID Int identity NOT NULL,
Date_Col datetime2(7) NOT NULL,
 CONSTRAINT [PK_C1810429_tbl_Target] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date_Col] ASC
)
) ON [PRIMARY];

-- create CLUSTERED index in the PS_myPartitionScheme with drop existing ON, this will drop and create/move the table and CI to PS
CREATE UNIQUE CLUSTERED INDEX [PK_C1810429_tbl_Target] ON [tbl_Target]
(
    [ID] ASC,
    [Date_Col] ASC
) 
WITH (DROP_EXISTING=ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON PS_myPartitionScheme([Date_Col]);
GO


select * from [tbl_partition_test]
select * from tbl_Target


-- Now switch from [tbl_partition_test] only partition 6 data to [tbl_Target] table this will be switched easy
ALTER TABLE [tbl_partition_test] SWITCH PARTITION 1 TO tbl_Target PARTITION 1; 

select * from [tbl_partition_test]
select * from tbl_Target


-- Switch IN
-- Now switch back in from data another non partition table [tbl_partition_Staging] to partition table [tbl_partition_test]

--drop table [tbl_partition_Staging]

CREATE TABLE [tbl_partition_Staging](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date_Col] [datetime2](7) NOT NULL,
     CONSTRAINT [PK_C1810429_tbl_partition_Staging] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [Date_Col] ASC
)
)ON [Primary];
GO


select * from [tbl_partition_test]
select * from [tbl_partition_Staging]


--we will get error Check constraints is missing
ALTER TABLE [tbl_partition_Staging] SWITCH TO [tbl_partition_test] PARTITION 1;

-- we do not have any data in partition 1
select [Date_Col] , $partition.PF_myDateRange([Date_Col]) as [partition]
from [tbl_partition_test] 
where $partition.PF_myDateRange([Date_Col]) in (1)

--create check constraint pre task for switch

ALTER TABLE [tbl_partition_Staging]
WITH CHECK ADD CONSTRAINT ck_Max_Date 
 /*create out of boundary check constaraint and insert higher data see the switch errror*/
CHECK (Date_Col IS NOT NULL AND Date_Col < '2025-01-01 00:00:00.0000000');

-- insert out of boundary data i.e. upper data to make an error
--delete from [tbl_partition_Staging] where [Date_Col]='2024-12-31 00:00:00.0000000'
insert into [tbl_partition_Staging] ([Date_Col]) values ('2024-12-31 00:00:00.0000000') -- higher value to make error

-- we will get an error values that are not allowed by check constraints or
-- partition function on target table 'Test_Tbl_Partition.dbo.tbl_partition_test'.
ALTER TABLE [tbl_partition_Staging] SWITCH TO [tbl_partition_test] PARTITION 1;

--drop CHECK if exists
ALTER TABLE [tbl_partition_Staging] DROP CONSTRAINT IF EXISTS ck_Max_Date
truncate table [tbl_partition_Staging]

--create check constraint pre task for switch
ALTER TABLE [tbl_partition_Staging]
WITH CHECK ADD CONSTRAINT ck_Max_Date 
CHECK (Date_Col IS NOT NULL AND Date_Col < '2024-01-01 00:00:00.0000000');
 /*create out of boundary check constaraint and see the switch errror*/
--CHECK (Date_Col IS NOT NULL AND Date_Col < '2025-01-01 00:00:00.0000000');


insert into [tbl_partition_Staging] ([Date_Col]) values ('2023-12-31 00:00:00.0000000')
insert into [tbl_partition_Staging] ([Date_Col]) values ('2020-12-31 00:00:00.0000000') 

-- insert out of boundary data i.e. upper data to make an error
--delete from [tbl_partition_Staging] where [Date_Col]='2024-12-31 00:00:00.0000000'
--insert into [tbl_partition_Staging] ([Date_Col]) values ('2024-12-31 00:00:00.0000000') -- higher value to make error

ALTER TABLE [tbl_partition_Staging] SWITCH TO [tbl_partition_test] PARTITION 1;


select [Date_Col] , $partition.PF_myDateRange([Date_Col]) as [partition]
from [tbl_partition_test] 
where $partition.PF_myDateRange([Date_Col]) in (1) 

select * from [tbl_partition_test] order by Date_Col
select * from [tbl_partition_Staging]

 

 

When we automate table partition switch in we need to be very careful the data are coming within the boundary. Mostly from partition table to non partition table with check constraint.