表分區現有表，其中分區鍵不是主鍵的一部分

主要目標：向表添加分區以使舊訂單的刪除不阻塞/更快（並且還了解分區）

我有一個現有的表訂單，如下所示：

CREATE TABLE Order (
   OrderId INT,
   OrderDate Datetime,
   Quantity INT,
   CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED 
(
   [OrderId] ASC
) 
ON [PRIMARY];

該表包含過去 10 年的 5000 萬行。我只需要最近 5 年的數據。

我有一個這樣的分區函式：

CREATE PARTITION FUNCTION OrderPF (datetime)
AS RANGE RIGHT FOR VALUES ('2014-01-01')

我有一個這樣的分區方案：

CREATE PARTITION SCHEME OrderPS 
AS PARTITION OrderPF ALL TO ([PRIMARY]) 

我的問題是如何進行？我仍然想要桌子上的主鍵。

是否$$ OrderDate $$列必須是聚集索引的一部分嗎？（主要問題）

CREATE UNIQUE CLUSTERED INDEX IX_Order ON Order(OrderDate,OrderId) ON OrderPS(OrderDate) ;

如果是這樣，那麼我是否必須純粹創建一個額外的非集群主鍵

$$ OrderId $$?

ALTER TABLE Order ADD CONSTRAINT PK_OrderId PRIMARY KEY NONCLUSTERED (Id) ON [PRIMARY];

這是正確的方法嗎？

如果OrderId是單調遞增的，則可以對其進行分區。然後，您可以截斷不需要保留數據的舊分區。就像是：

create partition function pf_OrderId(int) 
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go


CREATE TABLE [Order] (
   OrderId INT,
   OrderDate Datetime,
   Quantity INT,
   constraint [PK_OrderId] primary key clustered (OrderId)
) 
ON ps_OrderId(OrderId)

go

--then you can examine the max OrderDate in each partition when trimming old data
select p.partition_number,
      (select max(OrderDate) MaxOrderDate 
       from [Order] 
       where $PARTITION.pf_OrderId(OrderId) = p.partition_number) MaxOrderDate 
from sys.partitions p
where p.object_id = object_id('Order')
and p.index_id = 1

當然，您可以調整分區的粒度以大致符合您的數據保留要求。如果您有清除舊數據的硬性要求，那麼您將截斷 N 個分區並在最多一個分區上執行 DELETE。並且您始終可以拆分分區函式以在重要時間插入分區邊界，例如在一年或季度開始時的過夜。

要將現有表移動到分區方案，請刪除所有索引和聚集主鍵約束，然後在新分區方案上重新創建它們。在分區方案上創建聚集索引後，隨後創建的索引將預設進入那裡。如果不先刪除非聚群索引，則在刪除聚群PK時會重新建構它們，並在重新創建時重新建構它們，並且它們仍然不會被分區。例如

CREATE TABLE [Order] (
   OrderId INT,
   OrderDate Datetime,
   Quantity INT,
   constraint [PK_OrderId] primary key clustered (OrderId),
   index ix_Order_Orderdate (OrderDate)
) 

go

create partition function pf_OrderId(int) 
as range right for values (0,1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000)

create partition scheme ps_OrderId
as partition pf_OrderId all to ([Primary])

go

drop index ix_Order_Orderdate on [Order]

alter table [Order] 
drop constraint [PK_OrderId] 

alter table [Order] 
add constraint [PK_OrderId] primary key clustered (OrderId)
on ps_OrderId(OrderId)

create index ix_Order_Orderdate on [Order](OrderDate)

然後驗證聚集索引和非聚集索引是否都已分區：

select i.name index_name, p.partition_number 
from sys.partitions p
join sys.indexes i 
on p.object_id = i.object_id
and p.index_id = i.index_id 
where p.object_id = object_id('Order')

引用自：https://dba.stackexchange.com/questions/230661