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.