SQL SERVER – Create Index Without Locking Table

Indexes are the last option we consider when we do a Comprehensive Database Performance Health Check. 3 out of 4 clients, I work on the health check, we end up not creating new indexes. Most of the time we spend time looking at the indexes when removed we can get instant performance and it has been quite a successful journey so far. However, there is always an outlier and a special scenario where you have to create an index and I really feel depressed when I have to add an index to speed up my queries. Today we will see how we can create an index without a locking table.

Locking Table

There are many disadvantages to the indexes. Let me list three of the biggest disadvantages related to indexes.

• Slow Running Insert and Updates
• Slow Running Select Statements
• Table Locked During the Index Creation/Updation

One of the biggest complain lots of people have is that when they create an index on a large table it locks the table for a longer period of time leading to lots of timeouts and locking the table for an extensive period of time. Yes, it is absolutely true if you create an index on the huge table it may take lots of time to create it and create inconvenience to your users.

However, if you using the enterprise version of SQL Server you can easily create an index that is online and does not lock your table. For most of the index creation process, your table and the associated index will be available for you to use. Let us see the syntax of the online index creation.

CREATE NONCLUSTERED INDEX [IX_IndexName] ON [SchemaName].[TableName]
(
[Column1], [Column2]
)
INCLUDE([Column3], [Column4]) WITH (ONLINE = ON)
GO

If you specify keywords WITH (ONLINE = ON), your index will be created online and it will not lock your table most of the time. However, you should remember that online indexing can take sometimes up to twice the amount of the time to create the index and also takes additional space in your hard drive while it is being created. It is important to watch your disk space when you create an online index.

Note: Online index is Enterprise Only feature so if you are on standard edition this may not work for you.

Reference: Pinal Dave (https://blog.sqlauthority.com)