Online Partition Index Rebuild
Published: 10th May 2015
Yet more partitioning stuff… this time it’s the fact that we can rebuild an index on just a single partition of a table… and online. Now that’s handy!!!

We know that we can rebuild an index online but that’s a whole table and, if they’re big enough to be partitioned, then we really don’t want to incur the cost of effectively creating a copy of the table as this will hurt our disks and our transaction log.

However, this feature allows us to rebuild just an individual partition online which means way less overhead and much better performance. Who could ask for more?

So… here’s the code to create a partitioned table and then rebuild just partition 3 online…

use AdventureWorks2012
go

if object_id('testPartition') is not null drop table testPartition
go

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end

if
exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end

create
partition function testPartitionFunction(date)
  
as range left
  
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
go

create partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
all to ([primary])
go

create table testPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testPartition primary key clustered(id, orderDate)
)
on testPartitionScheme(orderDate)
go

insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go

alter table testPartition rebuild partition = 1 with (online = on)
go
Comments:
NB: Comments will only appear once they have been moderated.