I have primary key clustered index which is constantly modified (insert and update) and read (select). Now, in order to change one of the types of the columns from which it is composed I need to re-created them or:
- Drop the index
- Change the column type
- Create the index
I am wondering if I am using the
ONLINE hint, the table will be blocked for inserts. In the documentation is said that:
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed.
Basically, the index is created for 20 minutes, so I need a way to guarantee user experience is not blocked. The alternative is to have separate window for maintenance in which the database to be in single user mode.