CODEWITHSUNDEEP
sqlsql-server
Pam
Pam

Reputation: 43

SQL Server Wants to Create and Then Drop A Clustered Index While Creating Partition

I am trying to partition a table with a clustered index in which the primary key is an identity field. When SQL Server creates the script to partition the table, it creates the clustered index in the script - then immediately drops it. Can anyone think of a scenario in which that would occur? Just to note, we have a workaround solution to this. I am just trying to understand why SQL Server is behaving this way. Any insights would be appreciated. Thanks.

Upvotes: 3

Views: 686

Answers (2)

Mr. K
Mr. K

Reputation: 389

This happens because the clustered index's main purpose is to physically organize the pages of a table. And because this clustered index's key was not the part of the primary key hence later on it drops.

Upvotes: 0

Hlin
Hlin

Reputation: 134

A partitioned index (a primary key) is partitioned the same manner as the table. By default, when you partition an existing table, the primary key will be dropped and recreated. This makes the index aligned with the table. Hope it helps

Upvotes: 1

Related Questions