Non-clustered indexes are sorting of the columns you specify that "point" back to the data pages in the clustered index. This is why the clustered index you choose is so important because if effects all other indexes.
Non-Clustered index types:
-The index does not act as a constraint
-The index prevents any identical rows from being inserted.
-- The actual table data will not be reordered.
-- Sometimes called a "heap table" for tables lacking clustered indexes because it points to the actual data pages that are essentially unordered and non-indexed.
-- If no clustered index, non-clustered indexes point to the actual data in the table.
-- If clustered index present, non-clustered index point to clustered index.
-- Logical order of the index does not match the physical stored order of the rows on disk.
-- Add non-clustered indexes for queries that return smaller result sets.
-- Large results will have to read more table pages anyway so they will not benefit as much from a non-clustered index.
-- Add to foreign-key columns where joins are common that are not covered by the clustered index.
Post a Comment