no more tables

Forgetaboutit – when we talk about data, don’t think of it being in tables.  Instead, think of Heaps, Clustered Indexes and Non-clustered indexes.  A table without a CI is heap, a table with a clustered index is … a clustered index, and an index on either a heap or a non-clustered index on a clustered index is a non-clustered index.  All clear?

All of these are database objects and are stored in pages of data, probably in series of 8 pages (of 8K each) called extents.

Finding data in a heap is a table scan (why not a heap scan?), finding data in a clustered index, assuming we have a where clause that references the index is an index lookup or an index scan.  If we want something that is not in the index, having the index does not help us find the item, so back to table scan. Finally, if we find the item in a clustered index, there we are.  If we find the item in an non-clustered index, AND the data we want is not in the index or in a covering index, we will get a reference to the location in the table (…sorry…) and we can look it up.  However, if our index contains that data we want, we can just search the index to find our information and NOT have to go to the original NCI or Heap.  Faster (and possibly less I/O) that way.

Advertisements