# Basic Index Terminology in SQL Server > [!Note] > Understanding the distinct behaviors of **[[Clustered Index vs Heaps#Heap]]**, **Clustered Index**, **Nonclustered Index**, **Unique Index**, and **Primary Key** is vital for designing tables that balance **storage layout**, **query efficiency**, and **data integrity**. In SQL Server, how you organize rows on disk directly impacts performance. A **Heap** stores data without any ordering, making bulk loads extremely fast but full table scans inevitable when searching for specific rows. > [!tip] > **Heap** > Use when you need rapid inserts and don’t require ordered access or seek-based lookups. A **Clustered Index** arranges the table’s rows in a B-Tree structure according to a key. Its leaf level holds the actual data pages in sorted order, enabling efficient range scans and ordered retrieval. > [!info] > **Clustered Index** > Ideal for queries filtering or sorting by the indexed key, as it avoids extra I/O for range operations. In contrast, a **Nonclustered Index** builds a separate B-Tree of key values, each pointing back to the base table (or clustered index). While this supports fast seeks on non-key columns, it can incur additional lookups if the index doesn’t cover all requested columns. > [!warning] > **Nonclustered Index** > Beware of **bookmark lookups**: if your index isn’t covering, SQL Server must fetch missing columns via random I/O. A **Unique Index**—whether clustered or nonclustered—enforces that no two rows share the same key value, providing both performance benefits and data integrity. > [!success] > **Unique Index** > Essential for business rules that forbid duplicate entries, such as user emails or invoice numbers. Finally, the **Primary Key** constraint by default creates a unique clustered index on the chosen column(s), defining your table’s singular, non-nullable identifier. > [!caution] > **Primary Key** > Only one primary key (and thus one default clustered index) is allowed per table; choose your key wisely. --- ## References - Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media. - Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.