Databases Reference
In-Depth Information
Data Access Operators
In this section, I will show you the operations that directly access the database, using
either a base table or an index, examples of which include scans and seeks. A scan reads
an entire structure, which could be a heap, a clustered index, or a non-clustered index.
A seek, on the other hand, does not scan an entire structure but, instead, efficiently
retrieves rows from an index. Therefore seeks can only be performed on a clustered or
non-clustered index. Just to make the difference between these structures clear, a heap
contains all of a table's columns, and its data is not stored sorted in any particular order.
Conversely, in a clustered index, the data is stored logically sorted by the clustering key
and, in addition to the clustering key, the clustered index also contains the remaining
columns of the table. On the other hand, a non-clustered index can be defined on a
clustered index or a heap, and usually contains only a subset of the columns of the table.
The operations on these structures are summarized in Table 2-1 below.
Structure
Scan
Seek
Heap
Table Scan
Clustered index
Clustered Index Scan
Clustered Index Seek
Non-clustered index
Index Scan
Index Seek
Table 2-1:
Data Access operators.
