Microsoft – SQL Server – Online Index Management

Microsoft – SQL Server – Online Index Management

Being able to perform online Index Management is very beneficial.

In one of our use cases needed to find out whether we can change the ordering of one our indexes.

I really should review the usage of the Create Index\DROP_EXISTING command.

Though, I have yet to review it, it supposedly it allows one to change the columns and ordering of an existing index, without having to explictly drop the old one first.
http://msdn.microsoft.com/en-us/library/ms188783.aspx  

Anyway needed a quick answer as whether we can create a “Clustered Index” Online and it seems we can:

Here are a couple of good references:

1) Adding a PK online – Tibor Karaszi (7th SQL Server MVP in the World)

http://sqlblog.com/blogs/tibor_karaszi/archive/2010/01/13/adding-a-pk-online.aspx
2) Possible blocking when building an Index Online

http://www.jimmcleod.net/blog/index.php/2011/03/28/possible-blocking-when-rebuilding-indexes-online/

Error Messages:

1) Warning: Online index operation ‘<index-name>’ will proceed but concurrent access to the table may be limited due to residual lock on the table from a previous operation

One thought on “Microsoft – SQL Server – Online Index Management

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s