1

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]
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.