Modifying an existing index: Create index with drop_existing=on vs. alter index statement

Question

I am new to index maintenance. I see that most of our indexes are modified using create index with drop_existing = on. For example:

create nonclustered index ixn_SomeTable__SomeIndexName_ic1 
on dbo.SomeTable                ( Column1 )
include                         ( Column2, IncludeThisNewColumn3 )
with ( sort_in_tempdb = on, drop_existing = on, online = on, fillfactor = 95 ) on [SomeFileGroup]
go

but I see TSQL also has alter index statement.

Questions:

What does the drop_existing=on do? Does it just drop the index if it exists and recreates it, or does it save on rebuilding the index (re-indexing data etc.) if the modifications don't really need rebuilding of index (for example, including a column in a non-clustered index)?
What is the difference between create index with drop_existing = on and alter index? When do I absolutely have to use one or the other?
Does the index become unavailable when the modifications to the index are in progress, and is there a way to keep the unavailable time to a minimum?

Community · Accepted Answer · 2017-05-23 12:01:56Z

What does the drop_existing=on do? Does it just drops the index if it exists and recreates it or does it save on rebuilding the index (re-indexing data etc)

The DROP_EXISTING clause tells SQL Server that the existing clustered index is being dropped but that a new one will be added in its place, letting SQL Server defer updating the nonclustered index until the new clustered index is in place

does it save on rebuilding the index (re-indexing data etc) if the modifications don't really need rebuilding of index. (for example including a column in a non-clustered index)?

SQL Server won't rebuild the non clustered index at all if the clustered index key doesn't change and is defined as UNIQUE

What is the difference between create index with drop_existing = on and alter index? When do I absolutely have to use one or the other?

alter index is used to Rebuild/Reorg index..I don't see any comparison with Create

Does the index become unavailable when the modifications to the index are in progress and is there a way to keep the unavailable time to minimum?

When you use DROP EXISTING clause,index will be available for most of the time..Index will require an Exclusive lock at the end,but this blocking will be very short

References:
https://stackoverflow.com/a/41096665/2975396
https://msdn.microsoft.com/en-us/library/ms188783.aspx
http://sqlmag.com/database-high-availability/use-create-indexs-dropexisting-clause-when-recreating-clustered-index

Collectives™ on Stack Overflow

Modifying an existing index: Create index with drop_existing=on vs. alter index statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related