Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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...

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.