Some Information about database indexes
Guidelines to create indexes
The characteristics of data and the usage of the data in the environment in which it is defined ascertains the columns to build the index. The usefulness of the index is directly related to the percentage of rows returned from a query. Low percentage or high selectivity are more efficient. Creating indexes on the frequently searched column is more useful and efficient. Indexes can be best created on the following columns.
• Primary Keys
• Foreign Keys or columns that are frequently used in joining tables.
• Columns that are searched for range of key values.
• Columns that are accessed in sorted order.
An index is not needed for the following columns.
• A column that is infrequently referenced in a query.
• A column that contains lots of duplicate values.
• A column that contains bitmaps, text or images.
Clustered Index
A clustered index is the one in which the order of the values in the index is the same as the order of the data stored in the table. The index is created in actual data pages. The order of values in the index pages is also ascending. The maximum number of clustered indexes for a table is 249. These indexes are best created on columns of high selectivity to unique. Accessing data using clustered indexes is quicker than using non-clustered index.
Using Clustered Indexes
Clustered indexes are used in the column which is most frequently used for ranges of key values or are accessed in sorted order. The following facts are to be remembered about creating clustered index.
• A table can have only one clustered index.
• The physical row order of the table and the indexed row are both the same in clustered index.
• A clustered index must be created before any non clustered index is created, as clustered index changes the physical order of the table. Rows are stored into sequenced order and maintained in that order.
• Uniqueness of key value is maintained explicitly with the UNIQUE keyword, or implicitly with an internal unique identifier. These unique identifiers are not accessible to the users.
• The size of the clustered index is about five percent of the table size. However, clustered index size varies depending on the size of the indexed column.
• When a row is deleted, the space is reclaimed and is available for a new row.
• A clustered index needs around 1.2 times the table size when the index is created.
• This space is automatically de-allocated after the process of creating index is over.
The characteristics of data and the usage of the data in the environment in which it is defined ascertains the columns to build the index. The usefulness of the index is directly related to the percentage of rows returned from a query. Low percentage or high selectivity are more efficient. Creating indexes on the frequently searched column is more useful and efficient. Indexes can be best created on the following columns.
• Primary Keys
• Foreign Keys or columns that are frequently used in joining tables.
• Columns that are searched for range of key values.
• Columns that are accessed in sorted order.
An index is not needed for the following columns.
• A column that is infrequently referenced in a query.
• A column that contains lots of duplicate values.
• A column that contains bitmaps, text or images.
Clustered Index
A clustered index is the one in which the order of the values in the index is the same as the order of the data stored in the table. The index is created in actual data pages. The order of values in the index pages is also ascending. The maximum number of clustered indexes for a table is 249. These indexes are best created on columns of high selectivity to unique. Accessing data using clustered indexes is quicker than using non-clustered index.
Using Clustered Indexes
Clustered indexes are used in the column which is most frequently used for ranges of key values or are accessed in sorted order. The following facts are to be remembered about creating clustered index.
• A table can have only one clustered index.
• The physical row order of the table and the indexed row are both the same in clustered index.
• A clustered index must be created before any non clustered index is created, as clustered index changes the physical order of the table. Rows are stored into sequenced order and maintained in that order.
• Uniqueness of key value is maintained explicitly with the UNIQUE keyword, or implicitly with an internal unique identifier. These unique identifiers are not accessible to the users.
• The size of the clustered index is about five percent of the table size. However, clustered index size varies depending on the size of the indexed column.
• When a row is deleted, the space is reclaimed and is available for a new row.
• A clustered index needs around 1.2 times the table size when the index is created.
• This space is automatically de-allocated after the process of creating index is over.
Add a Comment
About
Translate This Network
CNN Tech
Loading feed
Smashing Magazine
Loading feed
Events
Videos
Music
© 2009 Created by Shakeel Shrestha on Ning. Create a Ning Network!
You need to be a member of HoHalla to add comments!
Join this Ning Network