indexes in SQL Server and Oracle

1 indexes in SQL Server

An index is a disk structure that is associated with a table or view to speed up the retrieval of rows from a table or view. An index contains keys that are generated by one or more columns in a table or view. These keys are stored in a structure (b-tree) so that SQL Server can quickly and efficiently find the rows associated with the key values.

A table or view can contain the following types of indexes:

Clustered index

The clustered index sorts and stores the data rows in a table or view based on the key values of the data rows. The index definition contains a clustered index column. Each table can have only one clustered index, because the data rows themselves can only be sorted in one order.

Data rows in a table are stored in sorted order only when the table contains a clustered index. If the table has a clustered index, the table is called a clustered table. If the table does not have a clustered index, its data rows are stored in an unordered structure called a heap.

Almost every table defines a clustered index on a column to implement the following features:

1, can be used for regular use of the query.

2, to provide a high degree of uniqueness.

Before you create a clustered index, you should first understand how data is accessed. Consider using a clustered index for queries that have the following characteristics:

Returns a series of values using operators such as between, >, >=, <, and <=.

After you use the clustered index to locate the row that contains the first value, you can ensure that the row that contains the subsequent index values is physically contiguous. For example, if a query retrieves records between a series of purchase order numbers, the clustered index of the Purchaseordernumber column can quickly locate the line containing the starting purchase order number, and then retrieve all contiguous rows in the table until the last purchase order number is retrieved.

Returns a large result set.

Use a join clause; In general, the foreign key column is used for this clause.

Use the order BY or GROUP BY clause.

The index of the column specified in the order BY or GROUP BY clause allows the database engine to not have to sort the data because the rows are already sorted. This can improve query performance.

Clustered indexes do not apply to columns with the following properties:

Frequently changed columns

This will cause the entire row to move because the database engine must keep the data values in the row in the physical order. This is particularly important because the data is usually variable in the bulk transaction processing system.

Wide Key

A wide key is a combination of several columns or several large columns. All nonclustered indexes use the key values in the clustered index as lookup keys. Any nonclustered indexes that are defined for the same table will grow much more because the nonclustered index entries contain a clustered key and also contain key columns defined for this nonclustered index.