Advanced T-SQL (part 1): Indexing

Hello folks! Today let’s discuss SQL the harder way :D

Indexing

Heap

By definition, it’s a table without a clustered index. Meaning that the order of data rows is not guaranteed. Since the physical order of data rows in a heap cannot be predicted, the clause “GROUP BY” should always be used to maintain this order.

Without indexes, a DBMS has to go through all the records in the table in order to retrieve the desired results. This process is called table scan and is extremely slow. On the other hand, if you create indexes, the database goes to that index first and then retrieves the corresponding table records directly.

There are two types of Indexes in SQL Server, which will be discussed later:

1. Clustered Index
2. Non-Clustered Index

table scan

query optimizer chooses table scan over index seek in terms of smaller cost

how to create and remove heap

B-Tree

The Balanced-Tree is a data structure used with Clustered and Nonclustered indexes to make data…