Online reindexing of partition.

Till now SQL 2012 its not possible to reindex a partition online.
We had two option

1. Either reindex entire table online
2. Reindex a single partition offline.



As data growing over time and tables are now of TBs size, is normal situation partition is a required strategy for DBAs and Developers. But in OLTP system reindexing or defraging index is regular requirement and doing this activity on large table without down time is really a challenge and trouble for us.
But now in SQL Server 2014 we can reindex a partition online.
So we dont need to take table offline to reindex a partition or we dont need to reindex entire large table everytime we need reindexing.
So we can reindex current partition regularly and even dont need downtime for this.

Lets see with example for this.


create partition function part_test_func(datetime) as range
left for values ('2008-09-10','2008-09-20','2008-09-30')

CREATE PARTITION FUNCTION part_test_func( datetime)

AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');

CREATE PARTITION SCHEME part_test_scheme

AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);

CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,

                        data varchar( 100) ,

                        date datetime)

ON part_test_scheme( date);

INSERT INTO  part_test

VALUES( 'a' ,

        '2014-09-05'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-09-15'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-09-25'

      );

INSERT INTO part_test

VALUES( 'a' ,

        '2014-10-05'

      );

CREATE CLUSTERED INDEX idx ON part_test( id );

CREATE INDEX idx_data ON part_test( data);

CREATE INDEX idx_date ON part_test( date);

Alter index all on part_test

rebuild  partition = 1

with (online = on)

 So now we are rebuilding only 1st partition that also online. :-)