SQL Notes: indexes

If you create a table, it is a heap.  

If you put an index (non-clustered) on a table, a copy of the contents of some column(s) in the table is created, put into order, and a pointer to the table row(s) is created.  A query against the table initially is a table scan, a query against the table with a non-clustered index, assuming the indexed column(s) is part of the predicate, is an index scan (non-clustered).  If you disable the index, you revert to a table scan on a query

create nonclustered index Index_name on dbo.table (id)

If you create a clustered index on a table, specifying a column(s) as the key, the entire table is reordered, and a query may become an index scan (clustered).  A index scan that can be satisfied by the contents of the index does not read the table – an index scan using a clustered index returns all the data from the relevant rows and must read the table, or parts of it.  The clustered index IS the table.  If you disable a clustered index on a table, the ENTIRE table is no longer accessible – if you drop the index, the table becomes a heap and you have a table scan.

create clustered index Index_name on dbo.table (id)
Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.