Create a SQL Server index without locking the table

20 November 2020
Create an index without locking the table - SQL Server

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])

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:


The full script should look like this:

CREATE NONCLUSTERED INDEX [IX_users_username] ON [dbo].[users] ([username]) WITH (ONLINE = ON)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.