I need your help. I have[AdventureWorks2014] database and I wanted to perform partitioning on [Production].[TransactionHistoryArchive] table based on [TransactionDate] column. [Production].[TransactionHistoryArchive] table has primary key clustered PK_TransactionHistoryArchive_TransactionID. I preferred using a wizard and at the end of wizard I a script below:
USE [AdventureWorks2014]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [myFUNCTION](datetime) AS RANGE LEFT FOR VALUES (N'2011-12-31T23:59:59.997', N'2012-12-31T23:59:59.997', N'2013-12-31T23:59:59.997')
CREATE PARTITION SCHEME [mySCHEMA] AS PARTITION [myFUNCTION] TO ([DRIVE E], [DRIVE F], [DRIVE G], [DRIVE H])
ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])
DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]
COMMIT TRANSACTION
Please help me to clearly understand the index recreation and correct me if I am mistaken.
MY THEORY: For performing table partitioning, we need to assign clustered index to the [TransactionDate] column. In order to do it we need to alter primary key constraint [PK_TransactionHistoryArchive_TransactionID] from PRIMARY KEY CLUSTERED to PRIMARY KEY NONCLUSTERED. SQL does it with the help of the following query:
ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [PK_TransactionHistoryArchive_TransactionID]
ALTER TABLE [Production].[TransactionHistoryArchive] ADD CONSTRAINT [PK_TransactionHistoryArchive_TransactionID] PRIMARY KEY NONCLUSTERED ([TransactionID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Now SQL can create clustered index for [TransactionDate] column:
CREATE CLUSTERED INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive] ([TransactionDate])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [mySCHEMA]([TransactionDate])
In the last part SQL drops the created index (did not understand this part. How come SQL creates and then immediately drops that clustered index?):
DROP INDEX [ClusteredIndex_on_mySCHEMA_636976799895259162] ON [Production].[TransactionHistoryArchive]