Have you ever encountered a situation where something is working, but you feel it shouldn’t be? I recently had a similar experience with SQL Server 2008. I knew that SQL Server 2008 supports online indexing, but I also knew that I couldn’t rebuild an index online if I used certain data types like VARCHAR(MAX) or NVARCHAR(MAX). However, I came across a situation where I had to dig deeper and do some reading to understand the behavior.

Let’s take a look at an example to better understand the situation:

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

If you run this code in SQL Server 2008 or SQL Server 2008 R2, you will encounter an error. The error message will state that an online operation cannot be performed for the index ‘IX_TestTable_Cols’ because it contains a column ‘SecondCol’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. The operation must be performed offline.

However, if you run the same code in SQL Server 2012 or a newer version, you will notice that the code runs successfully without any errors. This was a pleasant surprise for me, as I always believed that using VARCHAR(MAX) or NVARCHAR(MAX) in the table schema would prevent online index rebuilding.

After some research, I discovered that this behavior is not a bug or an oversight. It is actually an enhancement introduced in SQL Server 2012. This enhancement allows for online index rebuilding even when VARCHAR(MAX) or NVARCHAR(MAX) data types are used. This is a valuable feature for mission-critical tables that need to be online at all times.

If you’re interested in learning more about this topic, I recommend checking out Jacob Sebastian’s blog where he discusses this subject in detail.

So, have you come across any other surprising features in SQL Server 2012 or newer versions? Share your experiences in the comments below!