Do i have to create a certain nonclustered index every now and then?
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.
Databases indexes work like those in books.
It's actually a pointer to the right rows in your table, based and ordered on a specific key (the column for which you define the index).
So, basically, yes, if you create the index before inserting data, you should get the same search speed when you use it later on when the table is loaded with lots of records.
Although, since each time you insert (or delete, or update the specific key) a record the index needs to be updated, inserting (or deleting or updating) large amount of data will be a bit slower.
Indexes can get fragmented if you do a lot of insert and delete on the table. Thus, deleting and recreating them is usually part of a good maintenance plan.
Check out the free scripts from ola hallengren. One is on index maintenance and statisics.
General rule of thumb,
Index fragmentation between 10 and 30 pct, re-organize.
Fragmentation > = 30 pct, rebuild.
With a re-organize., you need to update your statistics.
The rebuild automatically does it.
Indexing is a huge part of optimizing query performance.
- John