What happens when I update my partitioning key?

This is a question someone asked me yesterday and if you think about it, this is really a good question.

The partitioning function defines on which partition the row will be mapped based on the partitioning column value.  Imagine that you have a table on which you have defined partitioning to make all rows with RecordType 1 to go to the PRIMARY filegroup and those with RecordType 2 to the ACTIVEDATA filegroup.  How do we handle updates that change the RecordType from 1 to 2?  The record obviously physically needs to move to the ACTIVEDATA filegroup but we are not inserting new data, we are just updating an existing row which has already been placed on PRIMARY.

Internally we are actually doing this by using a DELETE-INSERT operation.  The 'old' row is deleted and the row with updated values is inserted and thus using the partitioning function to decide the physical placement of the row. 

This is actually the same as with clustered indexes, when the value of your clustering key changes it might well be possible that its location in the table needs to change too and this is done in exactly the same manner. 

--Clustered key update

SET NOCOUNT ON
GO

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myClusteredTable]') AND type in (N'U'))
DROP TABLE [dbo].[myClusteredTable]
GO

SET

ANSI_NULLS ON
GO

SET

QUOTED_IDENTIFIER ON
GO

SET

ANSI_PADDING ON
GO

CREATE

TABLE [dbo].[myClusteredTable](
[myClusteringKey] [int] NOT NULL,
[myFiller] [char](100) NOT NULL,
CONSTRAINT [PK_myClusteredTable] PRIMARY KEY CLUSTERED
(
[myClusteringKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET

ANSI_PADDING OFF
GO

INSERT

INTO myClusteredTable VALUES (1, 'F')
GO

INSERT

INTO myClusteredTable
SELECT TOP (1) myClusteringKey + 1, myFiller FROM myClusteredTable ORDER BY myClusteringKey DESC
GO 100

CHECKPOINT

--If database is in SIMPLE recovery this will clear the transaction log
GO

UPDATE

myClusteredTable
SET myClusteringKey = 102
WHERE myClusteringKey = 1

SELECT

* FROM ::fn_dblog(null, null) --Look for LOP_DELETE_ROWS and LOP_INSERT_ROWS on the object
GO

--Partitioning key update

SET NOCOUNT ON
GO

IF

EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myPartitionedTable]') AND type in (N'U'))
DROP TABLE [dbo].[myPartitionedTable]
GO

IF

EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'RecordType_PS')
DROP PARTITION SCHEME [RecordType_PS]
GO

IF

EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'RecordType_PF')
DROP PARTITION FUNCTION [RecordType_PF]
GO

SET

ANSI_NULLS ON
GO

SET

QUOTED_IDENTIFIER ON
GO

CREATE

PARTITION FUNCTION [RecordType_PF](tinyint) AS RANGE LEFT FOR VALUES (N'1', N'2')
GO

CREATE

PARTITION SCHEME [RecordType_PS] AS PARTITION [RecordType_PF] TO ([PRIMARY], [ACTIVEDATA], [PRIMARY])
GO

CREATE

TABLE [dbo].[myPartitionedTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordType] [tinyint] NOT NULL,
CONSTRAINT [PK_myPartitionedTable] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON RecordType_PS([RecordType])
GO

INSERT

INTO myPartitionedTable VALUES(1)
INSERT INTO myPartitionedTable VALUES(2)
GO

CHECKPOINT

--If database is in SIMPLE recovery this will clear the transaction log
GO

UPDATE myPartitionedTable
SET RecordType = 2
WHERE RecordType = 1
GO

SELECT * FROM ::fn_dblog(null, null) --Look for LOP_DELETE_ROWS and LOP_INSERT_ROWS on the object
WHERE AllocUnitName LIKE 'dbo.myPartitionedTable%'
GO