What Just Happened to my online index operation !

I was always under the impression that when you perform online index operations, only the following guidelines apply (Click here for the complete description on msdn ) :

• Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
• Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
• Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.

Only if things were that simple ! But that doesn't happen with me & I usually end up finding myself in interesting situations. Allow me to explain.

This is just for demo, but let’s say I have a table dbo.cake ;

CREATE TABLE dbo.cake (

      eggs INT NOT NULL

      ,flour INT NOT NULL

      ,sugar INT NULL);

With a unique non-clustered index on eggs (don’t ask me why there isn't a Primary Key !);

CREATE UNIQUE NONCLUSTERED INDEX idx_un_cake_01

ON dbo.cake ( eggs);

A change to the application required that duplicate values  be allowed in the column eggs, but the combination of values in the column eggs & flour be unique.

Since I was asked to try to keep the table available for use during implementation of this change, I decided to make this index change online, using this script;

CREATE UNIQUE NONCLUSTERED INDEX idx_un_cake_01

ON dbo.cake ( eggs,flour)

WITH (DROP_EXISTING = ON, ONLINE = ON);

When this error message caught me by complete surprise (This was on my local box, no production servers were harmed during this test) !

Msg 1844, Level 16, State 2, Line 1

Create index online is not supported on unique nonclustered index.

I have done this a few times in the past, so this error message was a complete surprise to me. A little bit of research, let to this obscure “Readme” document for SQL2005, where in lies the explanation. Here is the relevant excerpt ;

“4.1.23 Unique Nonclustered Indexes Cannot be Created Online

In the final release of SQL Server 2005, unique nonclustered indexes cannot be created online. These indexes include unique nonclustered indexes that are created due to a UNIQUE or PRIMARY KEY constraint. SQL Server Books Online incorrectly states that this is a supported operation. The following error message is returned when you create a unique nonclustered index online by using CREATE INDEX or CREATE INDEX WITH DROP_EXISTING:

Msg 1844, Level 16, State 2, Line 1 Create index online is not supported on unique nonclustered index.

If the unique nonclustered index is created due to a UNIQUE or PRIMARY KEY constraint, (ALTER TABLE ADD CONSTRAINT), the following error message is also returned:

Msg 1750, Level 16, State 0, Line 1 Could not create constraint, See previous errors.

To resolve this issue, remove the ONLINE index option from the Transact-SQL statement. In SQL Server Management Studio, clear the Allow online processing of DML statements while creating the index box from the Options page of the New Index dialog.

SQL Server 2005 does support online index operations that create clustered indexes or nonunique nonclustered indexes, drop or rebuild clustered indexes, or rebuild unique or nonunique nonclustered indexes.”

SQL Server Books Online is wrong they say !! My first reaction was to try running my script again on a SQL2008 instance, where no-doubt, it ran successfully. I decided to test this out on a few versions of SQL2005 I could lay my hands on, and the script failed with the same error on each one of these versions.

Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042

I am really glad about having used the same version of SQL Server for testing this, as my target production instance !

I take a few lessons from this experience;

• This can be used an excellent reason to ask for an upgrade to SQL Server 2008, if you are still stuck with using SQL2005
• Be mindful of Disk Space, Performance and Transaction Log considerations with any online index operations
• Don’t assume, and don't just trust books online...........……..always be thorough with your testing!

 Collapse this tableExpand this table