SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012

Have you ever faced a situation where you see something working but you feel it should not be working? Well, I had similar moments a few days ago. I knew that SQL Server 2008 supports online indexing. However, I also knew that I could not rebuild index ONLINE if I used VARCHAR(MAX), NVARCHAR(MAX) or a few other data types. While I was strongly holding on to my belief, I came across with that situation where I had to go online and do a little bit of reading at Book Online.

 Here is an example showing the situation I’ve gone through:

First of all,  run the following code in SQL Server 2008 or SQL Server 2008 R2.

USE TempDB
GO
CREATE TABLE TestTable
(ID INT, FirstCol NVARCHAR(10), SecondCol NVARCHAR(MAX))
GO
CREATE CLUSTERED INDEX [IX_TestTable]
ON TestTable
(ID)
GO
CREATE NONCLUSTERED INDEX [IX_TestTable_Cols]
ON TestTable
(FirstCol)
INCLUDE (SecondCol)
GO
USE [tempdb]
GO
ALTER INDEX [IX_TestTable_Cols] ON [dbo].[TestTable]
REBUILD
WITH (ONLINE = ON)
GO
DROP TABLE TestTable
GO

Then, run the same code in SQL Server 2012 version. Observe the difference between both of the executions. You will be getting the following resultset.

In SQL Server 2008/R2, it will throw the following error:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index ‘IX_TestTable_Cols’ because the index contains column ‘SecondCol’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

In SQL Server 2012, it will run successfully and will not throw any error.
Command(s) completed successfully.

I always thought it will throw an error if there is VARCHAR(MAX) or NVARCHAR(MAX) used in the table schema definition. When I saw this result, it was made clear to me that it would surely not be a bug enhancement in SQL Server 2012. As a matter of fact, I always wanted this feature to be added in SQL Server Engine as this would enable ONLINE Index Rebuilding for mission critical tables that need to be online always. I quickly searched online and landed on Jacob Sebastian’s blog where he blogged about it this subject.

Well, is there any other new feature in SQL Server 2012 which gave you a good surprise?

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

10 thoughts on “SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012

  1. I have one table and some indexes(one clustered index and some non clustered indexes) on it, if some bulk insert(contains 10,000 records) occurs on original table then how the index tables updated.
    I mean index table updates for every record or all at once…
    In this case is there any differences in update of clustered index table and non clustered index table.

  2. Dave, thanks again,
    But same error in 2008 r2 with the clustered index:
    ALTER INDEX [IX_TestTable] ON [dbo].[TestTable]
    REBUILD WITH (ONLINE = ON)
    GO

    Is it changed in 2012?

  3. hi,
    can anyone clear my doubt??

    i’ve created partitioned existing table monthly range for year.
    and set filegroup’s sizes are unlimited
    now, my problem is
    one FG1 filegroup size is 567MB
    i’ve moved some datas from FG1 to FG2 filegroup.
    still FG1 size shows 567MB. its not balanced.
    why? how can i balance the memory of filegroup.
    wat is the solution for this one.

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #022 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s