CODEWITHSUNDEEP
sql-server-2008partitioning
a1ex07
a1ex07

Reputation: 37382

Partitioning non-partitioned table in SQL Server 2008


I have a table which in my opinion will benefit from partitioning:

CREATE TABLE [dbo].[my_table](
[id] [int] IDENTITY(1,1) NOT NULL,
[external_id] [int] NOT NULL,
[amount] [money] NOT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC));

There are just few different external_id and thousands of records for each of them.
SSMS Create Partition Wizard generates a script that I don't completely understand. After creating partition function and partition schema,
--it drops Primary Key,
--then creates Primary Key again on id, this time as non-clustered,
--then creates clustered index on external_id on newly created partition schema,
--and finally it drops the clustered index created on previous step.

Everything except last step seems clear, but I cannot get why it has to drop the clustered index. Should I remove the last step from the batch?

Any help will be greatly appreciated.

Upvotes: 1

Views: 1121

Answers (1)

Andrew
Andrew

Reputation: 27294

It makes sense.

The partition key is going to be the external id, so the clustered index must include that.

It preserves the primary key in a non-clustered index - since it's on ID not external_id

It created the clustered index on external_id to physically move the data into the partition scheme.

It drops the clustered index since it only used it to move the data - it was not a previously specified index.

There are a number of alternatives, assuming you always know the external_id, then you could choose to create the clustered index as (id,external_id) - the partition schema / function field used for the table must be within the clustered index on the partition schema.

Performance wise, this is not going to be a huge boost, the use of it is more that you can drop an entire external_id trivially, instead of a large delete transaction.

Upvotes: 1

Related Questions