If you have a large table of data in a SQL server database, and you want to add a new index, but can’t afford to lock the table, then this article will help you. However, please note:
- This does does actually lock the table still, but only briefly.
- This will not work with SQL Server Express or Standard Editions.
- This article presumes that you will be using SQL Server Enterprise Edition, or another version that supports creating indexes online.
Creating an index without locking the table
Lets say your want to create an index for the username column in the users table, you would normally use a variation of this script:
CREATE NONCLUSTERED INDEX [IX_users_username] ON [dbo].[users] ([username]) GO
That works fine, but it does lock the table whilst the index is created. To stop it locking, just append the below to the script:
WITH (ONLINE = ON)
The full script should look like this:
CREATE NONCLUSTERED INDEX [IX_users_username] ON [dbo].[users] ([username]) WITH (ONLINE = ON) GO