Sql-server – Difference between clustered index and partitioning column

partitioningsql serversql-server-2012

As I understand partitioning, the column that you partition on is how things are divided into partitions.

But does the partitioning column have anything to do with order on the disk? (Aside from which partition the data goes into.)

Say I have a table that looks like this:

CREATE TABLE [dbo].[Something](
    [SomethingId] [bigint] IDENTITY(1,1) NOT NULL,
    [OtherThingId] [bigint] NOT NULL,
    [CreatedBy] [int] NOT NULL,
    [CreatedWhen] [datetime] NOT NULL,
    [CreatedWhere] [varchar](255) NOT NULL,
     CONSTRAINT [PK_Something] PRIMARY KEY CLUSTERED 
    (
        [SomethingId] DESC
        [OtherThingId] DESC,
    )
) ON OtherThingIdPartitionScheme(OtherThingId)

The partition that a new row will be stored on is decided by OtherThingId.

But I setup the clustered index to be first SomethingId, then by OtherThingId.

Does that mean that each partition will be ordered by SomethingId, then by OtherThingId?
(Even though it is partitioned only by the secondary value of OtherThingId.)

(I am setting up a lot of partitioning right now and I want to be sure I fully understand it.)

Best Answer

But does the partitioning column have anything to do with order on the disk?

From Clustered Index Structures :

Clustered indexes have one row in sys.partitions, with index_id = 1 for each partition used by the index. By default, a clustered index has a single partition. When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition. For example, if a clustered index has four partitions, there are four B-tree structures; one in each partition.

From Table and Index Organization:

When a table or index uses multiple partitions, the data is partitioned horizontally so that groups of rows are mapped into individual partitions, based on a specified column. The partitions can be put on one or more filegroups in the database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows. The page collections for the B-tree are anchored by page pointers in the sys.system_internals_allocation_units system view.

If you want to really dig more into how data is layed out then refer to : Inside The Storage Engine: sp_AllocationMetadata

As a side note : SQL Server 2016 CTP2 has TRUNCATE TABLE ... [ WITH ( PARTITIONS ( { | }

For automating partitioning switching check out - SQL Server Partition Management utility

Related Question