sql-server - 修改现有索引 : Create index with drop_existing=on vs. alter index 语句

Question

我是索引维护的新手。我看到我们的大多数索引都是使用 create index 和 drop_existing = on 修改的。例如:

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

但我看到 TSQL 也有 alter index 语句。

问题 -

1. drop_existing=on 有什么作用？如果索引存在并重新创建它，它是否只是删除索引，或者如果修改不需要重建索引，它是否节省重建索引(重新索引数据等)。 (例如，在非聚集索引中包含一列)？
2. create index with drop_existing = on 和 alter index 有什么区别？我什么时候绝对必须使用其中之一？
3. 当对索引进行修改时，索引是否会变得不可用？有没有办法将不可用时间保持在最低限度？

Answer 1

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)

DROP_EXISTING 子句告诉 SQL Server 现有的聚簇索引将被删除，但将添加一个新的聚簇索引，让 SQL Server 推迟更新非聚簇索引，直到新的聚簇索引被创建到位

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)?

如果聚簇索引键没有改变并且被定义为 UNIQUE，SQL Server 根本不会重建非聚簇索引

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 用于重建/重组索引..我没有看到与 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?

当您使用DROP EXISTING 子句时，索引将在大部分时间可用。索引将在最后需要一个排他锁，但这种阻塞会很短

引用资料:
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