Create Indexes Without Blocking Access to Data

Use the create index ... online parameter to create indexes without blocking access to the data you are indexing.

The syntax is:

create [unique] [clustered | nonclustered] index index_name
	on database.]owner.]table_name
	[with {...
			online,
	...}

For example, to create the index pub_dates_ix on the titles table with the online parameter, use:

create index pub_dates_ix
on titles (pub_id asc, pubdate desc)
with online

Except for the sorted_data parameter, SAP ASE processes other create index parameters the same way, both with or without the online parameter. For example, if you include the reservepagegap parameter with the online parameter, SAP ASE reserves the pages while creating the new data layer. However, if you create the index using the sorted_data option, SAP ASE creates the index on the existing data layer.

Restrictions

User tables must include a unique index to use the create clustered index ... online command (creating nonclustered indexes does not have this restriction).
You can run create index ... online with a pll sort only on round robin partitioned tables
If you issue an insert, delete, update, or select command while create index … online or reorg … online are in the logical synchronization blocking phase:
- The insert, delete, update, or select commands may wait and execute after create index … online or reorg … online are finished
- SAP ASE may issue error message 8233.
You cannot:
- Run dbcc commands and utility commands, such as reog rebuild, on the same table while you are simultaneously running create index ... online.
- Run more than one iteration of create index ... online simultaneously.
- Perform a dump transaction after running create index ... online. Instead, you can:
  - Run create index ... online, then dump the database, or
  - Run a blocking create index, then issue dump transaction.
- Run create index ... online within a multistatement transaction.
- Create a functional index using the online parameter.

Note: Because create index ... online increments the schema count in the sysobjects row that reflects the table's state change, concurrent activity waiting for create index ... online to commit may encounter error 540 after create index ... online commits.