Types of Sybase index

SQL Server supports two types of indexes:

• Clustered indexes: SQL Server continually sorts and re-sorts the rows of a table so that their physical order is always the same as their logical (or indexed) order. You can have only one clustered index per table.
• Nonclustered indexes: the physical order of rows is not necessarily the same as their indexed order. Each nonclustered index provides access to the data in its own sort order, giving the appearance of data in that physical order. You can have up to 249 non-clustered indexes on one table.

Clustered indexes allow faster searches than non-clustered indexes. Clustered indexes are often called the primary key of the table. If you don't specify which type of index you want, it will be a non-clustered index by default.

There are a lot of options for creating an index, but the most commonly used one is unique. Both clustered and non-clustered indexes can be unique. A unique index prohibits duplicate values in the column that the index is on. For example, every credit card has a different number, so you could create a unique index based on credit card numbers.

An index can be created that based on multiple columns; this is known as a composite index. For example, you might want to create an index for a table based on zip code and city name.

Maintained by John Shipman, john@nmt.edu; original by Steve Anderson Last updated: 1997/06/25 19:44:31 UT URL: http://www.nmt.edu/tcc/help/db/sybase/index_types.html