SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012
- by pinaldave
Have you ever faced situation when you see something working and you feel it should not be working? Well, I had similar moments few days ago. I know that SQL Server 2008 supports online indexing. However, I also know that I cannot rebuild index ONLINE if I have used VARCHAR(MAX), NVARCHAR(MAX) or few other data types. While I held my belief very strongly I came across situation, where I had to go online and do little bit reading from Book Online. Here is the similar example.
First of all – run 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
Now run the same code in SQL Server 2012 version. Observe the difference between both of the execution. You will be get following resultset.
In SQL Server 2008/R2 it will throw 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 table schema definition. When I saw this result it was clear to me that it will be for sure not bug enhancement in SQL Server 2012. For matter for the fact, I always wanted this feature to be added in SQL Server Engine as this will enable ONLINE Index Rebuilding for mission critical tables which needs to be always online. I quickly searched online and landed on Jacob Sebastian’s blog where he has blogged about it as well.
Well, is there any other new feature in SQL Server 2012 which gave you good surprise?
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Index, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
