How to create nonclustered index with online if available

Question

I'm adding a new index to a SQL Azure database as recommended by the query insights blade in the Azure portal, which uses the ONLINE=ON flag. The SQL looks something like this:

CREATE NONCLUSTERED INDEX [IX_MyIndex] ON 
       [Customers].[Activities] ([CustomerId]) 
   INCLUDE ([AccessBitmask], [ActivityCode], [DetailsJson], 
       [OrderId], [OperationGuid], [PropertiesJson], [TimeStamp]) 
   WITH (ONLINE = ON)"

However, we also need to add this same index to our local development databases, which are just localdb instances that don't support the ONLINE=ON option, resulting in the following error.

Online index operations can only be performed in Enterprise edition of SQL Server.

My question is - is there a way to write this SQL index creation statement that will use ONLINE=ON if available, but still succeed on databases that don't support it?

Not to my knowledge. Instead you'd need to do an IF statement that checked what level of SQL Server you were running on and then have two CREATE INDEX commands, one with and one without ONLINE. — Grant Fritchey, May 18 '18 at 09:21
@GrantFritchey Ah OK. I've also found this answer which seems to help determine what SQL Server level I'm on: https://stackoverflow.com/a/21582449/7532 — Mark Heath, May 18 '18 at 09:24
Btw, even WITH (ONLINE=ON) may cause blocks on the final stage https://dba.stackexchange.com/questions/41181/why-does-create-index-with-online-on-block-access-to-the-table-over-a-period/50139#50139 — Michael Freidgeim, Jun 17 '22 at 23:49

Denis Rubashkin · Accepted Answer · 2020-11-12T07:08:00.200

10

You can use something like this:

DECLARE @Edition NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

SET @Edition = (SELECT SERVERPROPERTY ('Edition'));

SET @SQL = N'
CREATE NONCLUSTERED INDEX [IX_MyIndex] ON 
       [Customers].[Activities] ([CustomerId]) 
   INCLUDE ([AccessBitmask], [ActivityCode], [DetailsJson], 
       [OrderId], [OperationGuid], [PropertiesJson], [TimeStamp]) 
'

IF @Edition LIKE 'Enterprise Edition%' OR @Edition LIKE 'SQL Azure%' BEGIN
    SET  @SQL = @SQL + N' WITH (ONLINE = ON)';
END; 

EXEC sp_executesql @SQL;

edited Nov 12 '20 at 07:08

answered May 18 '18 at 09:23

Denis Rubashkin

2,151
1
9
14

Great, although in my case I need to check Edition for "SQL Azure" – Mark Heath May 18 '18 at 09:27
this script (conditional IF) doesn't work on a server that doesn't support ONLINE = ON. it will only work on a server that does. it will throw "Online index operations can only be performed in Enterprise edition of SQL Server." so there seems to be no point to the IF clause. – user1161137 Jul 01 '20 at 03:50
That's a concept, to make it work you need to use the `CREATE INDEX` commands in a dynamic query. – Denis Rubashkin Jul 01 '20 at 05:22
This didn't work for me, even though it never enters the if statement it still gives me an error – Archmede Nov 11 '20 at 19:03
1

It' high time me to correct the answer. I have done, so try to use. – Denis Rubashkin Nov 12 '20 at 07:10

How to create nonclustered index with online if available

1 Answers1

Linked