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:

  1. Drop the index
  2. Change the column type
  3. 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.

  • You cannot guarantee that actually it might or might not get blocked it depends on conflicting lock taken at that time – Shanky Apr 20 '16 at 14:59
  • That is maintenance. And take a backup first. Hopefully you don't need to do this often. – paparazzo Apr 20 '16 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.