Are existing entries immediately included into a newly created index?

Question

Suppose I have a table Stuff with a column City and no indices including that column. I populate the table. Then I decide to create an index:

CREATE INDEX [StuffOnCityIndex] ON [dbo].[Stuff](City ASC);

and the database confirms that CREATE INDEX succeeded.

Will the newly created index immediately include all existing entries from the table or will it be slowly built "in background"? In other words, if I think that creating an index will improve performance then will I see that improvement immediately upon CREATE INDEX completion?

Answer 1

The CREATE INDEX is unit of work that follows ACID.

So when it completes, it has completed (or failed) completely ("Atomic" in ACID). No further work is needed to populate or otherwise prepare the index ("Consistent" in ACID)

Whether it improves performance depends on it's suitability for the queries you are running, data distribution etc. That is a whole new area...

Answer 2

The index will be completely built as soon as the CREATE INDEX finishes.

You will only see a performance improvement if you SQL statement can make use of the newly created index.

You will need to look at the execution plan to find out if the index is used.