SQL INDEX WITH ONLINE = ON

Question

I have created a index on SQL Azure Where Query works perfectly fine and Index is created successfully but with an exception ONLINE = OFF

My Query to Create Index:

CREATE NONCLUSTERED INDEX [nci_wi_tbl_transactions] ON [dbo].[tbl_transactions] (
[bank_account_id]) INCLUDE ([bankcode]) WITH (ONLINE = ON)

When viewing the index in table is like this:

CREATE NONCLUSTERED INDEX nci_wi_tbl_transactions ON dbo.tbl_transactions (  bank_account_id ASC  )  
     INCLUDE ( bankcode ) 
     WITH (  PAD_INDEX = OFF ,FILLFACTOR = 100  ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  )
     ON [PRIMARY ] 

If you notice the above Index is created with ONLINE = OFF

I am trying to figure out why it is creating index with ONLINE = OFF instead of ONLINE = ON, Is it possible to create non-clustered index with ONLINE = ON. If yes how can I create index with ONLINE = ON

Am I missing something here.

Answer 1

WITH (ONLINE = ON) is a property of the CREATE INDEX statement, not of the index that gets created.

Hypothetically, two CREATE INDEX statements that were identical except that one had WITH (ONLINE = ON) and the other had WITH (ONLINE = OFF) would result in the creation of exactly the same index.

Answer 2

The metadata simply doesn't track how you created it because that is an operational / runtime attribute that does not become a permanent property of the index. It would be like a row maintaining information about what recovery model the database was in when the row was added.

Can you explain this little more, so I can find a solution to this issue.

I mean that when you create an index SQL Server doesn't remember whether you did so using ONLINE = ON or ONLINE = OFF. So when you view the properties or generate a new script from SSMS, it's always going to be OFF. People have used other workarounds, like manually scripting the indexes, including checking if the index can be built online (by manually adding ONLINE = ON), and always doing so if it is possible. For example:

• Script out all SQL Server Indexes in a Database using T-SQL

Or, if you are generating the scripts from SSMS, you can simply manually change the output and try ONLINE = ON always, just be prepared that sometimes that option is not possible.