Indexing

In IT, indexing refers to the creation of indexes that point to the location of resources (folders, files and records) based on file names, data fields, or unique texts and attributes in the file.

In database terminology, indexing is the process of creating an efficient data structure in a database to enable faster data retrieval. An index provides an underlying organizational schema for a database. An index divides database entries into fields based on an index structure that fits well with that specific data set. Indexing enables data to be retrieved more efficiently as only relevant fields of the database have to be searched to answer a query. Without an index, a database would have to do a “full scan” to answer every query.

Common data structures used to build indexes include B+ trees, balanced trees and hashes.  Index architectures are generally broken down into two types: clustered indexes and non-clustered indexes. Clustered indexes store row data in order. The goal is to create a sequential arrangement of data in order to reduce block reads. In non-clustered indexes, the index order is not the same as the physical ordering of the data on the disk, but rather organized in a “logical order” for that particular data set. According to database experts, non-clustered indexes are best suited for applications where data is modified frequently.

A key point to remember is that indexing using a data structure that is an ideal fit for your data results in optimum database performance.

Take a look at Idera’s webcast “Introduction to SQL Server Indexing” for an in-depth discussion of database indexing. Greg Robidoux talks about the following topics:

• clustered and non-clustered indexes
• covering indexes
• indexes with included columns
• columnstore indexes
• index statistics
• how indexes are used for table joins
• indexing for WHERE, GROUP BY, ORDER BY
• filtered indexes
• using DMVs to get insight into index usage
• query plans and more