Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Online Partition Index Rebuild
Published: Mar 12, 2017
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

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

if exists
select *
from sys.partition_schemes
where name = 'testPartitionScheme'
partition scheme testPartitionScheme

select *
from sys.partition_functions
where name = 'testPartitionFunction'
partition function testPartitionFunction

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

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

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)

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

alter table testPartition rebuild partition = 1 with (online = on)
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.