February 28, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 2

  • By Karl Moore
  • Send Email »
  • More Articles »

Most tables in your database will typically hold at least one index.

A table index is rather like the index of a book. It allows for the fast retrieval of specific information.

Adding an index to a field makes SQL Server go and organise all the information within that field, sorting and making it quicker to access that rows' data in future.

But unfortunately indexes in SQL Server aren't quite as simple as Access. Here, we have two different types of index:

  • Clustered This type of index sorts the entire table based on one field. Imagine it as a phone book; it organises the Surname field in ascending order and can therefore find rows quicker based on that field. Of course, if it were just to search through a random list of data, it would be much slower. Note though, you can only have one clustered index per table
  • Non-Clustered This is a strange index. Let's say you add this index to the Surname field. This index stores all of the Surnames in ascending order somewhere else, along with a pointer to the original row. So when you search for a particular Surname, it looks it up in the separate list of surnames, finds the pointer to the actual row, then speedily retrieves it. You can have more than one non-clustered index per table

You can use both clustered and non-clustered indexes within the same table. And even though SQL Server does a lot of behind-the-scenes work to implement these, you should note that this doesn't affect the way you retrieve records via Visual Basic.

It's also worth pointing out that you can involve more than one field within an index if you wish. For example, you may want to index both the Surname and Forename fields together meaning searches on both of those fields will be lightning fast.

But adding too many indexes also slows down data addition, as after every new record insert, SQL Server needs to reorganise the index. So use them in moderation.

It's also worth noting that, just as in Access, indexes can be unique - or not. If an index is unique, it does not allow duplicates (sometimes called duplicate keys).

To create an Index:

  • Open your table to alter the design (as before)
  • Click the 'Table and Index Properties' button on the toolbar
  • Select the 'Indexes/Keys' tab
  • Click 'New'
  • Select the Columns (fields) you want to add to your Index
    • You can select more than one if you wish
  • Enter an Index name
    • Alternatively, stick with the default
  • Check 'Unique' if you want this column to contain only unique values
    • It will automatically select the 'Index' option button. This creates a unique index, whilst the 'Constraint' option simply stops duplicates. Stick with the default.
  • If you would like this to be a clustered index, check the 'Create as CLUSTERED' box
  • To add another index, click 'New'
  • Click Close when finished

To run the Index Wizard:

  • Launch your Server Taskpad (as before)
  • Click 'Tools' on the toolbar, then 'Wizards'
  • Expand the 'Database' entry
  • Select 'Create Index Wizard' and click OK
  • Follow the on-screen prompts

To manage your Indexes:

  • View your Tables (as before)
  • Select the one you want to manage
  • Right-click, select 'All Tasks', 'Manage Indexes'
  • Use the 'New', 'Edit' and 'Delete' commands to alter as required
  • Click Close when finished

If you're working on a large project and are unsure where you should add indexes, the Index Tuning Wizard can take frequently used SQL statements and decide for you. We won't be covering this feature in-depth here - for more information, check out Books Online.

To run the Index Tuning Wizard:

  • Launch your Server Taskpad (as before)
  • Click 'Tools' on the toolbar, then 'Wizards'
  • Expand the 'Management' entry
  • Select 'Index Tuning Wizard' and click OK
  • Follow the on-screen prompts

Page 3 of 5

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date