The previous versions of SQL Server never allowed index operations (CREATE, DROP, REBUILD) on ONLINE mode, if the index includes a Large Value Type column (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) etc).

Try running the following code in SQL Server 2008

-- Create a Table
CREATE TABLE Items (
    ItemID INT IDENTITY,
    ItemName VARCHAR(100),
    VarcharData VARCHAR(MAX)
)
GO

-- Create an Index
CREATE NONCLUSTERED INDEX [IDX_ItemIdxTest] ON [dbo].[Items] 
(
    [ItemID] ASC,
    [ItemName] ASC
)
INCLUDE ( 
    [VarcharData]
) WITH ( ONLINE = ON )
GO

The above code will produce the following error.

An online operation cannot be performed for index 'IDXItemIdxTest' because the index contains column 'VarcharData' 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 DROPEXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

This limitation has been removed in SQL Server 2012 (Denali). The above code runs without an error in a SQL Server 2012 instance.

• Reference 1  [103 clicks]
• Reference 2  [32134 clicks]
• Reference 3  [32134 clicks]

Category :	Database Engine
Release :	SQL Server Denali
Tags :	INDEX, ONLINE, Large Value Types, Performance, Scalability, SQL Server 2012, Denali