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 (https://blog.sqlauthority.com)