SQL Server and Oracle Indexes

1. Index in SQL Server

An index is a disk structure associated with a table or view, which can accelerate the speed of retrieving rows from a table or view. An index contains keys 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 effectively find the rows associated with the key value.

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

Clustered Index

The clustered index sorts and stores the data rows according to the key values of the data rows in the table or view. The index definition contains clustered index columns. Each table can have only one clustered index, because data rows can only be sorted in one order.

Data rows in the table are stored in order only when the table contains clustered indexes. If a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in a unordered structure called a heap.

Almost every table defines clustered indexes for columns to implement the following functions:

1. It can be used for frequently-used queries.

2. Provide High uniqueness.

Before creating a clustered index, you should first understand how data is accessed. Consider using clustered indexes for queries with the following features:

Return a series of values using operators (such as BETWEEN,>, >=, <, and <=.

When a clustered index is used to locate the row that contains the first value, the rows that contain the subsequent index value are physically adjacent. 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 rows containing the start purchase order number, and then retrieve all consecutive rows in the table, until the final purchase order number is retrieved.

Returns a large result set.

The JOIN clause is generally used as a foreign key column.

Use the order by or group by clause.

The index of the column specified in the order by or group by clause does not need to be sorted BY the database engine because these rows have been sorted. This improves the query performance.

Clustered indexes are not applicable to columns with the following attributes:

Columns frequently changed

This will move the entire row because the database engine must physically keep the data values in the row. Pay special attention to this because the data in the large-capacity transaction processing system is usually variable.

Width key

A wide key is a combination of several or several large columns. All non-clustered indexes use the key values in the clustered index as the search key. Any non-clustered index defined for the same table will be much larger, because the non-clustered index item contains the clustering key and the key column defined for this non-clustered index.

• 7 pages in total:
• Previous Page
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• Next Page