Sql-server – Can SQL use an index while creating another

indexsql server

I just had a situation where I had to include a column in an index. This required dropping the index, and recreating it. This got me thinking, surely that's a lot of unnecessary work.

Say I had instead created the new index first, then dropped the old one. Let's also assume that I have some way of indicating that the old index will not be dropped until after the new one has been created.

Would the server get any performance benefit by using the old index while creating the new one?

Best Answer

You can do 

CREATE TABLE T(A INT, B INT, C INT)

INSERT INTO T 
SELECT 1,1,1
FROM master..spt_values

GO

CREATE INDEX ix ON T(A);

GO

--This won't benefit from existing index as noncovering
CREATE INDEX ix ON T(A) INCLUDE (B) WITH(DROP_EXISTING = ON);

GO

--This can benefit from existing index
CREATE INDEX ix ON T(A)  WITH(DROP_EXISTING = ON);

GO

DROP TABLE T;

There is no particular advantage of doing so in the first case.

Whilst the existing index provides the desired sort order it will not be used for this as it does not contain the newly added column B and so it would require a lookup back to the base table for each row. The plan shows a table scan and sort.

enter image description here

The use of DROP_EXISTING here could provide benefit if you were removing an included column though - as in the second DROP_EXISTING example (as the original index would both have the desired order and cover all columns).

The plan for this shows that the original index was read to create the replacement.

enter image description here

Related Question