Do i have to create a certain nonclustered index every now and then?

Question

I have a database table that has more than 50 Million record and to improve searching i had to create a non clustered indexes, and once i create one it takes 5 ~ 10 minutes to be created so i guess in the background it sorts the data according to the index.

So for example before adding index to my table searching was awful and takes long time and when i added the non clustered index, searching was fast.

But that was only when i had 50 million records.

The question is, what if i defined the index at the very beginning when creating the table before adding any data to the table? Would it give the same search performance i am getting right now? or do i have to delete and recreate the index every now and then to sort the data regularly?

I am sorry if my question seemed stupid, i just started learning about indexes and it is a confusing topic for me.

Answer 1

A non-clustered index keeps a copy of the indexed fields in a special structure optimised for searching. Creating an index on 50 million records obviously takes some time.

Once the index is created, it"s maintained automatically as records are added, deleted or updated, so you should only need to reindex if you've had a serious crash of the system or the disk.

So generally, it's best to create the index at the time you create the table.

There is an operation called 'updating statistics' which helps the query optimiser to improve its search performance. The details vary between database engines.