Search code examples
sql-servert-sql

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

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:

  1. 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)?
  2. 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?
  3. 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?
Solution

  • 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