Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 199

How it works...
In step 1, we created one purchase order table named ordDemo , by inserting a hundred
thousand records. In step 3, we executed the SELECT query by filtering records based on
the Refno field, with the execution plan enabled. We can see the execution plan with details,
in step 5, which was simply to scan the whole table just to return a few records from the
100,000 records we inserted. This is a CPU- and I/O-centric operation, because to return
a few thousand records, each and every record given in the table is scanned.
In step 6, we created the clustered index on the Refno column, based on the criteria we
discussed in the Getting Ready section of this recipe. After creating an index, we executed
the same SELECT query we ran in step 3, with execution plan enabled; you will see a big
difference between the two execution plans given in steps 5 and 6.
I/O cost is 0.379421 , in the first execution plan, and 0.0571991 , in the second execution
plan, even though both queries return the same number of rows. You might wonder why there
is a difference in the same query that is run on the same table and returns the same number
of rows.
The answer is really very simple if we know how an index works. When there was no index
on the table ordDemo , SQL Server went through each and every row to check whether its
Refno value is less than 3 or not. If it is less than 3, it was included in the results set. This
process is called a Table Scan. However, if there is an index, SQL Server knows what values
are contained in each data page. It directly moves to the particular pages, picks up all the
qualified records, and displays them in a results set. This process is called an Index Seek.
There's more...
The following is an explanation of some of the technical vocabulary used in this recipe:
Heap
Any database table that doesn't have a clustered index on it is called heap. Heap has Index
ID=0 in sys.partitions catalog view. Unlike the clustered index, which stores data sorted
logically, heap is used to store data without any sorting order. Data pages in heap are linked to
each other without any logical ordering, and one needs to scan the whole heap to search for
any data, unlike the clustered index, resulting in slow performance.
Here is the T-SQL command that will show you catalog view sys.partitions :
Select OBJECT_NAME(object_id) AS TableName,
* from sys.partitions
WHERE index_id=0