Results 1 to 2 of 2

Thread: Index tables

  1. #1
    andy16 is offline Member
    Join Date
    May 2010
    Posts
    11
    Rep Power
    0

    Default Index tables

    Hello,

    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!

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,172
    Rep Power
    20

    Default

    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.

Similar Threads

  1. Java Tables
    By greatmajestics in forum AWT / Swing
    Replies: 2
    Last Post: 03-25-2010, 06:03 PM
  2. Help Printing Tables
    By ogidantunde in forum Advanced Java
    Replies: 0
    Last Post: 06-14-2008, 07:34 PM
  3. caching the tables
    By jayashree in forum JDBC
    Replies: 0
    Last Post: 01-30-2008, 07:43 AM
  4. how to sort 2 tables
    By valery in forum AWT / Swing
    Replies: 1
    Last Post: 08-06-2007, 09:30 PM
  5. Tables In Ejb3
    By Alan in forum Enterprise JavaBeans (EJB)
    Replies: 1
    Last Post: 05-10-2007, 05:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •