Hi,
This post is very simple and will make you understand the difference between Aligned and Non-Aligned Indexes while performing Switch in & Switch out.
I already covered most of the errors and scenarios in my blog and below is the link
This post covers what happens with Aligned Indexes and below is the script
CREATE TABLE [dbo].[TABLE1]
([pkcol] [int] identity NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] [varchar](50) NULL,
[partitioncol] datetime not null)
GO
Now Let's imagine a case where you need to perform partitioning on the existing table where we have primary key on a different column than the column on which you are intending to partition with .
In this scenario we have 2 options
1) Drop the primary key and create it as NON Clustered primary key
2) Add the partition column to be part of existing primary key
There are many excellent blogs which explains the performance implications of how partition works from query standpoint. But I will try to post the advantages from Switch in and switch out with Aligned Indexes.
Imagine we have clustered index already on Partitioncol and now we need to add primary key on the PKCOL.
if we try to create in the normal way like below then we will hit back with the error
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_TEST(pkcol)
Msg 7726, Level 16, State 1, Line 1
Partition column 'pkcol' has data type int which is different from the partition function 'myDateRangePF_test' parameter data type datetime.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How about the below syntax? Well , it won't work either
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_TEST(partitioncol)
Msg 1908, Level 16, State 1, Line 2
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 0, Line 2
Could not create constraint. See previous errors.
So what is the correct way of creating then? We need to add the partitioning column along with primary key in the constraint definition
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_TEST(partitioncol)
So let's create a staging table with similar structure to that of main table means our primary key NON Clustered will be aligned.
CREATE TABLE [dbo].[TABLE1_stage]
([pkcol] [int] identity NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] [varchar](50) NULL,
[partitioncol] datetime not null)
GO
CREATE CLUSTERED INDEX IX_TABLE1_partitioncol_stage ON dbo.TABLE1_stage (partitioncol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON myPartitionScheme_TEST(partitioncol)
ALTER TABLE dbo.TABLE1_stage ADD CONSTRAINT PK_TABLE1_stage PRIMARY KEY NONCLUSTERED (pkcol,partitioncol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON myPartitionScheme_TEST(partitioncol)
As said we already have rows in the main table and the partition looks like below
when you want to perform switch unlike in non aligned index case we don't need to disable the indexes and rebuild them after switch operation.
We can directly perform it with out any issues using the below command
alter table table1 switch partition 1 to table1_stage partition 1
Thanks for Reading.
Comments