Sql-server – Understanding the recreation of index in Table partitioning

clustered-indexconstraintnonclustered-indexpartitioningsql server 2014

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]

Best Answer

Dropping the index is not required, and is likely a coding oversight in the wizard output. It appears you've not just blindly ran the generated code; this is a Good Thing™, and is why I much prefer to write my own T-SQL statements by hand.

The procedure for modifying a clustered primary key to a non-clustered primary key, then adding a clustered index to a separate column is exactly as you've described:

  1. DROP the primary key constraint.
  2. ADD a new primary key constraint with the NONCLUSTERED keyword.
  3. CREATE a clustered index on the desired column.

The DROP INDEX statement is certainly not required.

Related Question