Look at Sybase's Official Handbook index working principle

A brief introduction to Sybase database
Sybase Company was founded in 1984, the company name "Sybase" from "System" and "database" combined meaning. Sybase's first relational database product was the Sybase SQLServer1.0, which was launched in May 1987. Sybase first put forward the idea of Client/server database architecture and was the first to implement it in Sybase SQL Server. Sybase felt that it was hard to rely on one force to make SQL Server (then not ASE) the boss, so he joined Microsoft and developed it together. Subsequently, in 1994, two companies terminated their cooperation. By this point, you should all have exactly the same set of SQL Server code. Sybase SQL Server later, in order to differentiate itself from Microsoft's MS SQL Server, renamed: Sybase ASE (Adaptive Server Enterprise), in fact, should change the name of Microsoft. Sybase ASE still maintains the status of a large database vendor. In the telecommunications, transportation, municipal, banking and other fields, has a strong market.
But it seems that many are legacy systems of large companies. For the historical reasons above, many of Sybase's grammars are similar to the T-SQL of MS SQL Server. Now the online Sybase data and documents are relatively few, many years ago. This Sybase online Help manual is more complete, the address is http://infocenter.sybase.com.
The following is the 12th chapter in the manual How the index works, the Sybase index of the working principle of the more understandable. And most of the theory should also be applied to other databases, so it is more valuable.
Chapter 12:how Indexes work

Indexes are the most important physical design element in improving database performance:

Indexes help prevent table scans. Instead of reading hundreds of data pages, a few index pages and data pages can satisfy many queries.

For some queries, data can is retrieved from a nonclustered index without ever the data rows.

Clustered indexes can randomize data inserts, avoiding insert "hot spots" on the last page of a table.

Indexes can help avoid sorts, if the index is matches the order of the ' columns in ', by clause.

In addition to their performance benefits, indexes can enforce the uniqueness of data.

Indexes are database objects that can is created for a table to speed direct access to specific data rows. Indexes Store the values of the key (s) that were named when the index is created, and logical pointers to the data pages or to other index pages.

Although indexes speed data retrieval, they can slow down data modifications, since most changes to the data also require Updating the indexes.
Indexes prevent full table scans, and for some queries you do not need to access data pages (composite indexes), clustered indexes avoid frequent inserts of new data to the last page, and avoid sorting.
Types of Indexes

Adaptive Server provides two types of indexes:

Clustered indexes, where the table data is physically stored in the "the" the

For allpages-locked tables, rows are stored into key order on pages, and pages are linked in key.

For data-only-locked tables, indexes are used to direct the storage of data in rows and pages, but strict key ordering is Not maintained.

Nonclustered indexes, where the storage order of data in the "not" related to index keys

Can create only one clustered index on a table because there are only one possible physical ordering of the data rows. Can create up to 249 nonclustered indexes per table.

A table that has no clustered the index is called a heap. The rows in the table are in no. particular order, and all new rows are added to the "end of" the table. Chapter 8, "Data Storage," discusses heaps and SQL operations on heaps.
The data on the data page of the clustered index is sorted according to the index key, so a table can have only one clustered index. Tables that do not have a clustered index are also called heaps.
Index pages

Index entries are stored as rows on index pages in a format similar to the format used for data rows on data pages. Index entries store the key values and pointers to lower levels of the ' index, to the ' data pages, or to individual data row S.

Adaptive Server uses B-tree indexing, so all node in the index structure can have multiple.

Index entries are usually much smaller than a data row in a data page, and index pages are much more densely populated tha n data pages. If A data row has bytes (including row overhead), there are rows per page.

An index on a 15-byte field has about rows per index page (the pointers require 4–9 bytes per row, depending on the Ty PE of the index and the index level).

Indexes can have multiple levels:

Root level

Leaf level

Intermediate level
B-tree balance tree, that is, the parent node can have multiple child nodes (unlike a binary tree with only two).
Root Level

The root level is the highest level of the index. There is only one root page. If a allpages-locked table is very small, so this entire index fits on a single page, there are no intermediate or le AF levels, and the root page stores pointers to the data pages.

data-only-locked tables always have a leaf level between the root page and the data pages.

For larger tables, the root page stores pointers to the intermediate level index pages or to leaf-level pages.
For a very small table, you need only one root index page. Large tables may have many middle pages.
Leaf Level

The lowest level of the "index is" the leaf level. At the "leaf level", the index contains a key value for each row in the table, and the rows are stored in sorted order by th E index key:

For clustered indexes on allpages-locked tables, the "leaf level" is the data. No Other level of the ' index contains one index row for each data row.

For nonclustered indexes and clustered indexes in data-only-locked tables, the leaf level contains the index key value for Each row, a pointer to the page where the row was stored, and a pointer to the rows on the data page.

The level of the leaf level just above the data; It contains one index row for each data row. Index rows on the index page are stored in key value order.
The page-level index page contains the name-value pairs for each row of data, and the index entries on the index page are sorted by index key. For clustered indexes, page-level index pages are data pages. For nonclustered indexes, the page level contains index entries for all data rows. (Specific reasons to continue looking down)
Intermediate level

All levels between the root and leaf levels are intermediate levels. An index on a large table or the index using long keys may have many intermediate levels. A very small allpages-locked table may not have A intermediate level in all; The root pages point directly to the leaf level.
Index Size

Table 12-1 describes the new limits for index size for APL and DOL tables:

Table 12-1: Index row-size limit

2670

Page size	user-visible index row-size limit	Internal index row-size limit
2 k (2048 bytes)		650
4 K (4096bytes)	1250	1310
8 k (8192 bytes)	2600
16K (16384 bytes)	5300	5390

Because you can create tables and columns wider than the limit for the "index key", these columns become. For example, if your perform the following on a 2K page server, then try to create a index on C3, the command fails and Ad Aptive Server issues An error message because column C3 is larger than the index row-size limit.

CREATE TABLE T1 (C1 int,c2 int,c3 char (700))

"Non-indexable" does not mean this you cannot use this columns in search clauses. Even though a column is non-indexable (as in C3, above), you can still create statistics for it. Also, if you include the column in a where clause, it'll be evaluated during optimization.
The length of the column cannot be greater than the maximum length of the index entry, or an error will be.
Clustered indexes on allpages-locked tables

In clustered indexes on allpages-locked tables, leaf-level pages are also the data pages, and all rows are kept in Physica L ORDER by the keys.

Physical ordering means that:

All entries on the A data page are in the index key order.

By following the ' next page ' pointers on the ' data pages ', adaptive Server reads the entire table in the index key order.

On the root and intermediate pages, each of the entry points to a page at the next level.
Clustered Indexes and select operations

To select a particular last name using a clustered index, adaptive Server I uses sysindexes to find the root page. It examines the values on the root page and then follows page pointers, performing a binary search on each page it Accesse s as it traverses the index. Figure 12-1 below.

Figure 12-1: Selecting a row using a clustered index, allpages-locked table

On the ' Root level page ', ' Green ' is greater than ' Bennet, ' but less than, so ' karsen for ' pointer ' is ' Bennet to Page 1007. On page 1007, ' Green ' is greater than ' greane, but less than ' Hunter, ' so the ' pointer to page 1133 be followed to the DAT A page, where the row is located and returned to the user.

This retrieval via the clustered index requires:

One read for the root level of the index

One read for the intermediate level

One read for the data page

These reads could come either from cache (called a logical read) or from disk (called a physical read). On tables that are frequently used, the higher levels of the indexes are the often in cache, with found lower and data Pages being read from disk.
Clustered indexes and insert operations

When you insert a row into a allpages-locked table with a clustered index, the data row must is placed in physical order According to the "key value" on the table.

The "other" rows on the "data page" on "the page", as needed, to make room for the new value. As long as there is room to the new row on the page, the insert does does not affect any other pages in the database.

The clustered index is used to find the location for the new row.

Figure 12-2 shows a simple case where there are room on a existing data page for the new row. In this case, the key values in the index does not need to change.

Figure 12-2: Inserting a row into the allpages-locked table with a clustered index

Because you want to ensure the physical order of the data pages when inserting, you use the clustered index to find the location of the inserted value.
Page splitting on full data pages

If There is isn't enough room on the data page for the new row, a page split must to be performed.

A New data page is allocated on A extent already in with the table. If There is no free page available, a new extent is allocated.

The next and previous page pointers on adjacent pages are changed to incorporate the new page in the page chain. This requires reading those pages into memory and locking them.

Approximately half of the rows are moved to the new page and the new row inserted in order.

The higher levels of the clustered index to the new page.

If The table also has nonclustered indexes, all pointers to the affected data rows must is changed to point to the new Pag E and row locations.

In some cases, the page splitting is handled slightly differently.

"Exceptions to page splitting".

In Figure 12-3, the page split requires adding a new row to a existing index page, page 1007.

Figure 12-3: Page splitting in a allpages-locked table with a clustered index

Move half of the data to a new page of data. The separation of index pages is similar to the data page.
Page splitting on index pages

If a new row needs to being added to a full index page, the page split process on the index page is similar to the data page Split.

A New page is allocated, and half of the index rows are moved to the new page.

A new row is inserted in the next highest level of the ' Index to ' to the ' new index page.
Clustered Indexes and delete operations

When you delete a row from a allpages-locked table that has a clustered index, and the other rows on the page move up to fill the Empty spaces so this data remains contiguous on the page.

Figure 12-5 shows a page that has four rows before a deletes operation removes the second row on the page. The two rows that follow the deleted row are moved up.

Figure 12-5: Deleting a row from a table with a clustered index



deleting the last row on a page

If you delete the "last row on" a data page, the page is deallocated and the next and previous page pointers on the adjacent Pages are changed.

The "Rows" to "that" page in the leaf and intermediate levels of the index are removed.

If The deallocated data page is on the same extent as other pages belonging to the table, it can be used again when that T Able needs an additional page.

If The deallocated data page is the "last page on" the extent that belongs to the table, the extent is also deallocated and becomes available for the expansion of the other objects in the database.

In Figure 12-6, which shows the ' table after the deletion, the pointer to the deleted page has been removed from index page 1007 and the following index rows on the page have been moved the "keep" space used.

Figure 12-6: Deleting the last row in a page (after the delete)

Index Page Merges

If you delete a pointer from a index page, leaving only one row on this page, the row is moved onto a adjacent page, and The empty page is deallocated. The pointers on the parent page are updated to reflect the changes.
Nonclustered Indexes

The B-tree works much of the same for nonclustered indexes as it does for clustered indexes, but there, are some. In nonclustered indexes:

The leaf pages are not the same as the data pages.

The leaf level is stores one key-pointer pair for each row in the table.

The Leaf-level pages store the index keys and page pointers, plus a pointer to the row offset table on the data page. This combination's page pointer plus the row offset number is called the