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.
