CODEWITHSUNDEEP
sql-serverpartitioning
Geekn
Geekn

Reputation: 2882

What is the difference between creating a table on partition scheme versus creating a cluster index on partition scheme

To achieve partitioning, you can create a table on a partition scheme or create the table on primary followed by adding a clustered index on the partition scheme. Is there any difference/preference between the two approaches? They both seems to achieve the same set of partitions when querying system partitions.

Upvotes: 1

Views: 367

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89331

Is there any difference/preference between the two approaches?

A Clustered Index is the table, so no, there is no difference. When you create a clustered index, either directly or through a Primary Key Constraint you are re-creating the table, and moving it to the partition scheme.

The only observable difference I can think of is if you start with a table on a filegroup, add some non-clustered indexes, and then add a partitioned clustered indexes, the non-clustered indexes will remain on the filegroup, not on the partition scheme.

Upvotes: 2

Related Questions