Creating index in SQL Server with ONLINE = ON if supported

Question

I'm creating an index in a SQL Server table with ONLINE = ON option:

CREATE INDEX IX_Name ON Users (Name) WITH ONLINE = ON

If this script is run on non-Enterprise SQL Server edition I get this error:

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

How to write a SQL script to use ONLINE = ON option on Enterprise editions and not to use it for non-supported editions?

check select SERVERPROPERTY ('edition') is enterprise and IF. — mxix, May 24 '16 at 16:54
It still shows the same error even if ONLINE = ON inside 'if' with non-satisfied condition. — alexey, May 25 '16 at 08:47
@alexey try this https://stackoverflow.com/questions/50407490/how-to-create-nonclustered-index-with-online-if-available — Archmede, Nov 13 '20 at 17:13

score 1 · Answer 1 · answered May 24 '16 at 17:03

1

Something like this should help

IF  SERVERPROPERTY ('edition') like '%Enterprise%Edition%'
BEGIN
CREATE INDEX IX_Name ON Users (Name) WITH ONLINE = ON
END

Also I think the login you are using should have permission to View Server State

answered May 24 '16 at 17:03

Pரதீப்

Edition should be Enterprise or Developer. – Bogdan Sahlean May 24 '16 at 17:26
Another option: SERVERPROPERTY('EngineEdition') = 3 /* Enterprise, Developer */ OR SERVERPROPERTY('EngineEdition') = 5 /* SQL Azure */. – alexey May 25 '16 at 08:38
3

If you run this script against SQL Express it still drops 'Online index operations can only be performed in Enterprise edition of SQL Server.' – alexey May 25 '16 at 08:38

1 Answers1