Can SQL use an index while creating another?

Question

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?

• user1822: Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product.
• Andrew Williamson: Yes, I know. I figured an answer to this question would be database-agnostic
• user1822: No it's most definitely not
• Erik Reasonable Rates Darling: I've found some funny behavior with index creation that you might be interested in.

Answer 1

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

• Andrew Williamson: Ooh neat, I've never used WITH(DROP_EXISTING = ON) before. Does the cost of a row lookup really outweigh the benefit of the index scan (rather than a full table sort)?
• Martin Smith: @AndrewWilliamson its the cost of a row lookup for every single row in the table. and yes that is costed as more than a scan and sort. Theres quite a lot of overhead to each lookup even if the pages to be read are already in memory and obv more so if physical IO is required.