Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We have a table with a lot of insert\select every time ( like 100 machines inserting\updating every second).
What is the best way to create an index on a table that can't be locked not even for a second?
If I create the index, i'm sure it will lock the requests, and I can't do it.
It's a big table with 1 million rows+.
There is no such option as CREATE INDEX WITH (NOLOCK) (and even NOLOCK on a query takes locks, just fewer than without the hint).
The best you're going to do is WITH (ONLINE = ON), which still takes locks at the beginning and end of the operation (both relating to recompiling plans related to the table - see this blog post by Paul Randal for more details).
This will significantly reduce the impact of building the index on your applications, but there is no practical way to completely eliminate that impact. Also, this feature is not free: it requires Enterprise Edition.
