Required indexes during a partitioning operation switch in and switch out ?

Through this example let's see the indexes that are required at the source and the target during the switch out and switch in operation of partitioning.

-- Create a database called partition

CREATE DATABASE Partition;

USE Partition

GO

-- Create a table test

CREATE TABLE Test

(

c1 int identityNOTNULL

,c2 intNOTNULL

,c3 dateNOTNULL

,c4 char(7000)

);



Create the partition function with 2 boundary values. 2 boundary values means 3 partitions

• 1st partition = all values less than GETDATE() (excluding time)
• 2nd partition = all values >= GETDATE() (excluding time) and < GETDATE() + 1 (excluding time)
• 3rd partition = all values >= GETDATE() + 1 (excluding time)

CREATE PARTITION FUNCTION pf_Test(date) AS RANGE RIGHT FOR VALUES (GETDATE(),GETDATE()+1)

-- Create the partition scheme with all partitions mapped to PRIMARY file group

CREATE PARTITION SCHEME ps_Test AS PARTITION pf_Test ALL TO ([PRIMARY]);

-- Create the clustered index on column c3. This will be the partitioning column

CREATE CLUSTERED INDEX idx_c3_Test ON Test(c3) ON ps_Test(c3);

-- Create the primary key on the column c1.

ALTER TABLE Test ADD CONSTRAINT pk_Test PRIMARY KEY (c1,c3) ON ps_Test(c3);

-- Create a non clustered index on column c2

CREATE NONCLUSTERED INDEX idx_c2_Test ON Test(c2) ON ps_Test(c3);

-- Insert data into 2nd and 3rd partition

DECLARE @i int= 1;

WHILE (@i <= 10)

BEGIN

INSERT INTO Test VALUES (@i,GETDATE(),'a');

SET @i = @i + 1;

END



Lets see what sys.partitions table has for us. As can be seen clearly we have 10 rows inserted into the second partition

SELECT * FROM sys.partitions(nolock) WHEREobject_id=OBJECT_ID('Test') AND index_id = 1;



-- Now we will insert another 10 rows in the 3rd partition. Note that I have increased the date by 1

DECLARE @j int= 1;

WHILE (@j <= 10)

BEGIN

INSERT INTO Test VALUES (@j,GETDATE()+ 1,'b');

SET @j = @j + 1;

END

-- sys.partitions shows us. Last partition now has 10 rows

SELECT * FROM sys.partitions(nolock) WHEREobject_id=OBJECT_ID('Test') AND index_id = 1;





-- I will now switch out the data from partition 2 to a temp table. In order to do that I need to create a temp table as follows

SELECT * INTO Temp_Test FROM Test(nolock) WHERE 1 = 0;

-- Now that the temp table is created I need to have the clustered index on this table same as in Test so that the switch out of data can happen.

CREATE CLUSTERED INDEX idx_Temp_Test ON Temp_Test(c3);

-- I now switch out partition 2. For the swich out no primary key or non clustered index was required. Only thing that was required was the clustered index

ALTER TABLE Test SWITCH PARTITION 2 TO Temp_Test;

-- Lets check the data in the Temp_Test table

SELECT * FROM Temp_Test;



-- Lets see how sys.partitions look for Test table. You can see after switch out the data count in partition 2 has gone back to 0

SELECT * FROM sys.partitions(nolock) WHERE object_id=OBJECT_ID('Test')AND index_id = 1;



-- I will now try to switch in this data in the Temp_Test table into another table Test_Arch. Let's create this Test_Arch table

CREATE TABLE Test_Arch

(

c1 intidentityNOTNULL

,c2 intNOTNULL

,c3 dateNOTNULL

,c4 char(7000)

);

-- Since even the archived table is partitioned. I will create the partitioning function partitioning scheme for this table. I will use some old dates for the boundary values

CREATE PARTITION FUNCTION pf_Test_Arch(date)AS RANGE RIGHT FOR VALUES (GETDATE(),GETDATE()+ 1)

CREATE PARTITION SCHEME ps_Test_Arch AS PARTITION pf_Test_Arch ALL TO ([PRIMARY]);



CREATE CLUSTERED INDEX idx_Test_Arch ON Test_Arch(c3) ON ps_Test_Arch(c3);

CREATE NONCLUSTERED INDEX idx_c2_Test_Arch ON Test_Arch(c2) ON [PRIMARY];



Now that we have created the archived table, the only thing that we need before we switch in the data is the constraint on the temp table on partitioning column. I have used the hard coded dates, but you can choose the date when you execute this



ALTER TABLE Temp_Test ADD CONSTRAINT ck_Temp_Test CHECK (c3 >='2010-03-17'AND c3 <'2010-03-18');

-- Finally we are ready to switch in the data. What we want to check is, do we need to disable the non aligned indexes in the Temp_Test before we switch in or it works fine

ALTER TABLE Temp_Test SWITCH PARTITION 1 TO Test_Arch PARTITION 2;

-- No it doesn't work. I get the error that the index in Test_Arch is not partition aligned. This means I will have to align the index idx_c2_Test_Arch to the partition scheme

'ALTER TABLE SWITCH'statement failed. The table'Partition.dbo.Test_Arch'is partitioned whileindex'idx_c2_Test_Arch'isnot partitioned.

-- I drop the index and recreate it aligned to the partitioning scheme. Will I be able to switch in data now

DROP INDEX Test_Arch.idx_c2_Test_Arch;

CREATE NONCLUSTERED INDEX idx_c2_Test_Arch ON Test_Arch(c2) ON ps_Test_Arch(c3);

-- Lets fire the SWITCH IN statement again. I get another error, this time it says that not all the indexes available in the Test_Arch table, are present in the Temp_Test table. Indeed index idx_c2_Test_Arch is not present in Temp_Test

ALTER TABLE Temp_Test SWITCH PARTITION 1 TO Test_Arch PARTITION 2;



-- I will create this index now

CREATE INDEX idx_c2_Temp_Test ON Temp_Test(c2)

-- At this point I also want to see the page allocation for table Temp_Test for both the clustered and the non clustered index as shown below

DBCC IND(Partition, Temp_Test, 1);-- clsutered index

DBCC IND(Partition, Temp_Test, 2);-- non clsutered index









-- Finally I will switch in the data now..

ALTER TABLE Temp_Test SWITCH PARTITION 1 TO Test_Arch PARTITION 2;



Switching happened successfully as can be seen below. First one is the Temp_Test and second Test_Arch.



SELECT * FROM Temp_Test;

SELECT * FROM Test_Arch;



Lets see the page allocation for Test_Arch now. As can be seen below. The pages that were of Temp_Test have now been allocated to Test_Arch (Look at object_id). This shows that during the switch out and switch in operation it is the metadata change only that happens and that is also the reason why switch should happen in the same filegroup

DBCC IND(Partition, Test_Arch, 1);-- clsutered index

DBCC IND(Partition, Test_Arch, 2);-- non clsutered index








Points to note here

• Just like while switching out we need to disable all the nonclustered index not aligned to the
  partitioning scheme, simiarly while switching in we need to disable an non partition aligned indexes in the target table.
• Also while switching in, the temp table (source) should have the same indexes as the target table unless disabled at the target.
• There are lots of other constraints that should be taken care of apart from these e.g. switch operation has to happen within the same filegroup or for xml column any xml schema collection should be the same in the source and the target. I haven't spoken about them here as I wanted to focus more on this aspect. Will talk about it in future blogs