To fully understand why we would ever need to re-index a table, we need to understand how indexes work within SQL Server.
An index, much like a book index, is a sorted list that we can use to lookup the page where the data actually is. This is much better than manually trawling through the table checking each record for a match. This is called a table scan.
Let's say we have a transport table that contains the following fields:
In this table, we need to ensure that the following things are true:
Transport Idis always unique