Search code examples
sql-serverindexingrebuild

SQL Server 2017 and online index rebuild not working after Standard to Enterprise upgrade

I've upgraded from Standard Edition to Enterprise Edition, which is shown correctly:

Enterprise Edition (64-bit)

If I try to modify an index (or recreate) I no longer get an error that it's only supported on Enterprise Edition. Which is good, but it doesn't apply the ONLINE = ON part.

For example:

CREATE NONCLUSTERED INDEX [NonClusteredIndex-Test] ON [dbo].[Test]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

After the index is created, ONLINE remains on OFF. I've been experimenting with various index options, all are applied, but ONLINE = ON doesn't work. Is there something I'm missing or need to modify?

Solution

  • ONLINE is not an attribute of the index itself. It only applies to the CREATE or ALTER statement that is building/rebuilding the index.

    For instance you might build the index with ONLINE=ON during business hours, but choose to rebuild it with ONLINE=OFF during an outage window.