Redgate Data Modeler

7 October 2014

What Is a Database Index?

Sooner or later there comes a moment when database performance is no longer satisfactory. One of the very first things you should turn to when that happens is database indexing. This article will give you a general overview on what indexes are without digging into too much detail. We’ll discuss additional database index topics in future articles.

In general, a database index is a data structure used to improve queries execution time. To explain what an index is, we need to say a few words on how the data stored in tables is organized.

Tables may be organized in two ways:

• heap tables
• index-organized tables (IOTs), or clustered indexes (different vendors use different terminology, but the concept is common among them)

For now, let’s talk about heap tables only. We’ll get back to index-organized tables after we explain what an index is.

Understanding Heap Tables

In heap tables, data is stored in no particular order. New data is inserted without sorting or reorganizing previously inserted data in any way. This makes an insert operation execute very quickly but is inefficient when retrieving data.

For example, let’s say we have a table defined as above which contains one million rows. The table has no constraints and no indexes. The insert operation will be immediate, taking at most a few milliseconds of time:

But a query retrieving a person by SSN will take much more time: