Difference between clustered index and partitioning column

Question

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.)

• Dan Guzman: It is the index key that determines order. The partitioning column determines the partition and therefore filegroup.

Answer 1

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

Answer 2

To add to the answer of Kin Shah, this query might help:

SELECT
 o.type_desc AS objtype,
 u.name AS [schema],
 o.name AS object,
 i.type_desc AS idxtype,
 i.index_id AS idxid,
 ISNULL(i.name, '*Heap') AS [index],
 p.partition_number AS partitn,
 p.partition_id,
 p.rows,
 p.data_compression_desc AS compr,
 a.allocation_unit_id AS alloc_unit_id,
 a.type_desc AS alloc_unit_type,
 8 * a.total_pages AS totl_KB,
 8 * a.used_pages AS used_KB,
 8 * CASE WHEN i.index_id < 2 THEN a.data_pages
 END AS data_KB,
 CONVERT(varchar(6), CONVERT(int, CONVERT(binary(2), REVERSE(SUBSTRING(a.first_page, 5, 2))))) + ':' +
 CONVERT(varchar(20), CONVERT(int, CONVERT(binary(4), REVERSE(SUBSTRING(a.first_page, 1, 4))))) AS first_page,
 CONVERT(varchar(6), CONVERT(int, CONVERT(binary(2), REVERSE(SUBSTRING(a.root_page, 5, 2))))) + ':' +
 CONVERT(varchar(20), CONVERT(int, CONVERT(binary(4), REVERSE(SUBSTRING(a.root_page, 1, 4))))) AS root_page,
 CONVERT(varchar(6), CONVERT(int, CONVERT(binary(2), REVERSE(SUBSTRING(a.first_iam_page, 5, 2))))) + ':' +
 CONVERT(varchar(20), CONVERT(int, CONVERT(binary(4), REVERSE(SUBSTRING(a.first_iam_page, 1, 4))))) AS first_iam_page
FROM sys.objects AS o
LEFT JOIN sys.schemas AS u
 ON o.schema_id = u.schema_id
LEFT JOIN sys.indexes AS i
 ON o.object_id = i.object_id
INNER JOIN sys.partitions AS p
 ON i.object_id = p.object_id
 AND i.index_id = p.index_id
LEFT JOIN sys.system_internals_allocation_units AS a -- Internal variant of sys.allocation_units.
 ON p.partition_id = a.container_id
WHERE o.is_ms_shipped = 0
 AND a.total_pages > 0
ORDER BY o.type, u.name, o.name, i.index_id, p.partition_number