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