Hi All,
This time I am going to share my experience with Partitioning. I have huge project lined up where I need to perform partitioning and as part of it I learned some interesting things. I am blogging here some of them.
Case 1: I have a table with Primary key on an integer column now I need to partition on Datetime Column.
How to do this? As you know we need to have clustered index on the partition column hence need to drop the primary key constraint and recreate it.
Here is the link that I made use of for testing on existing table
As you can see below here are the indexes on the table and the partition column is residing on the partition scheme and the others are on Primary file group. The other indexes are called as Non Aligned indexes
Let's say we have a requirement where we need to archive the data from the first partition to another table.
Note: There is subtle difference between Archiving and purging as you know. Also from SQL 2016 we have an option to truncate the partition too. But in my case I need to move the data to another table.
I created a Auxiliary/Staging table and tried to perform switch out. And to do this I need to create clustered index on the staging table otherwise you will get the error
Msg 4913, Level 16, State 1, Line 7
ALTER TABLE SWITCH statement failed. The table 'tempdb.dbo.table1' has clustered index 'IX_TABLE1_partitioncol' while the table 'tempdb.dbo.table_aux' does not have clustered index.
As you can see the staging table is placed on same partition scheme to that of Main table and the index has been created too.
Now I need to export the data/Switch out from partition 1 of the Main table to Auxiliary table so we can use
alter table table1 switch partition 1 to table1_aux partition 1 when I fired this got the below error
Msg 7733, Level 16, State 4, Line 7
'ALTER TABLE SWITCH' statement failed. The table 'tempdb.dbo.table1' is partitioned while index 'IX_TABLE1_col2col3' is not partitioned.
How to Overcome this error? This has happened because we have non aligned indexes in our main table so I thought we need to align them to overcome this and did create the index like below
/****** Object: Index [IX_TABLE1_col2col3_aux] Script Date: 14/05/2020 11:15:28 ******/
CREATE NONCLUSTERED INDEX [IX_TABLE1_col2col3] ON [dbo].[table1]
(
[datacol1] ASC,
[datacol2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [myPartitionScheme]
GO
Msg 2726, Level 16, State 1, Line 5
Partition function 'myDateRangePF' uses 1 columns which does not match with the number of partition columns used to partition the table or index.
What went wrong with the above syntax? Because we have not specified partition col post the file group so it should be like below
CREATE NONCLUSTERED INDEX [IX_TABLE1_col2col3] ON [dbo].[table1]
(
[datacol1] ASC,
[datacol2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [myPartitionScheme](partitioncol)
GO
Okay now we need to do the same step for Primary key Non clustered index so we need to drop the constraint and recreate it
alter table table1 drop constraint PK_TABLE1
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (pkcol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [myPartitionScheme](partitioncol)
Msg 1908, Level 16, State 1, Line 3
Column 'partitioncol' is partitioning column of the index 'PK_TABLE1'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 1, Line 3
Could not create constraint or index. See previous errors.
So Again an error which says you need to have your partitioning key to be part of the index
Note: This is required only if you are creating a UNIQUE or PRIMARY KEY
ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED (pkcol,partitioncol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [myPartitionScheme](partitioncol)
After doing this I was able to move the data from Main table to staging table and this is how the data looks
Okay now the question is do we really have to do this? Yes if we don't the basics like me and NO if you know the fundamentals.
To achieve the same results what we need to do is just disable the non clustered indexes on the base table and perform the switch that's it.
Comments