Monday, August 8, 2016

Clustered Index

a CLUSTERED index determines the PHYSICAL location (order) of data in a table. That's why you can only have ONE clustered index per table.

A Primary Key constraints will automatically create a CLUSTERED INDEX on that column.

-can have multiple columns (a COMPOSITE index)

Execute sp_helpindex tblEmployee
to view indexes.

You can change clustered index.  First:

Drop Index tblEmployee.PK_tblEmplo_3FD45DG   (or might have to use Object Explorer to Delete)

Create Clustered Index tblEmployee_Gender_Salary on tblEmployee (Gender DESC, Salary ASC)

CLUSTERED includes Data: think PHONEBOOK
NON-CLUSTERED is separate from data: think index to a BOOK

Since the index is stored separately, you CAN have MORE THAN ONE non-clustered indexes per table.

CLUSTERED INDEX is faster because it involves a single lookup, whereas NON-CLUSTERED has to do a second step to access the data location.  Also, NON-CLUSTERED requires EXTRA STORAGE SPACE for itself.

set statistics io on

(then write your select query)

After running, view Messages.


For viewing execution plan,
TABLE SCAN is when there isn't an index.
An INDEX SEEK would be more efficient.


Downside of Indexing is when you have lots of writes/updates (transactions) to a table.

Naming conventions:
IX prefix means Index.
UIX means UNIQUE index.