I have a few questions regarding index tables with MSSQL Server Express 2008. I will be guessing a bit here... As far as I understand, index tables are automatically created for primary keys (maybe other keys, too?). You can, however, create your own index tables for columns that are often used for searching - such as unique columns - in order to optimize the time needed to perform a search on a given value. That is, to use binary search and get O(log n) efficiency rather than traversing through all of the rows and get O(n). Is this correct?
If I have a table with 1000 rows and I want to make an index table for a unique key, does this table automatically index the existing values or only new values? Is everything done automatically for me when I create a new index table?
I heard that for large databases, it is best to update the index tables during the night when the load is not peaking, but in my mind, that suggests that they are not updated automatically (unless you can choose that). In this case, where they would be updated during specific times in a day, how would the SQL server handle searches on the indexed column when something has been inserted since the index was update (that is, the index is outdated)?
I am obviously very new to this and just heard about this very quickly, so please forgive me if I am asking silly questions. :)
Thanks in advance!
Wouldn't this be better placed on a SQL Server forum?
I can only answer from an Oracle perspective, whereby rebuilding indexes is generally not a good idea.
An index is "populated" when it's created, and maintained as stuff is added to it, otherwise it would be a pointless thing. Rebuilding on a schedule (in Oracle, and outside of certain types of index) is a sign that someone is misunderstanding how these things work. Indexes like to have some room to move, if you like, and will reach an optimum stable size. People see the "spaces" in the indexes and think it's wasted, but the db likes to have them there so they can insert more stuff in as data gets inserted into the table the index is referencing.
The times it makes sense is data warehousing and bulk insert jobs where indexes are often switched off for the insertion of data, and then rebuilt.
Now, as I say this is from an Oracle perspective, so there may be a reason for it in SQL Server, but considering the number of people who rebuild indexes on schedule in Oracle when it's unecessary...well, I would go check with Microsoft.