Index -Clustered Index- for faster Findings - similar to book index but not in separate page but for faster finding arranging the data into organized way from unorganized(mess).

 Indexing 

Indexing is similar to book table of content. Its used for faster findings. 

There are different Types of Index:

1.Clustered Index

2. Non-Clustered Index

3. Unique and Non- Unique Index.

                                                                 

1.drop index PK__testEmp2__3214EC07FA6E2E32 on testEmp2

2.sp_helpindex tx_tblemployee_salary

When to Index?

If one has to search salary from a large table frequently, index with salary key will help to retrieve it fast. 

Clustered Index:

Determines Physical state/order of a data. Note: ID column is marked as primary key constraint creates clustered index automatically.

To confirm execute sp_helptext tblEmployee.

Therefore due to this nature(id index created automatically when primary key is set to id) , no matter how messy the order of insert statement, the data is stored automatically in ascending order.  

here in the snapshot, the data are inserted in non sequential order but after inserted its arranged in sequential manner, the reason to this is Primary key ID.

In short, clustered index = arranging data 

since, data can be arranged in one way, can not have too many arrangement. Therefore a table can have only ONE clustered index. i.e if it already has PK(generates index on pk automatically) we can not make another clustered index. 

If we have to Make our own cluster index instead of default index of PK constraint, we have to delete Pk_Clustured_Index.

   But it cant be dropped and throws an error so, we have to delete it manually via object explorer.

       But remember when Index of Primary key is removed the primary key is also removed automatically, you cant have both of it.

NOTE: Clustered Index is superior to Non- Clustered Index. REASION: THE QURRY ENIGINE DOESNT HAVE TO JUMP FROM INDEX TABLE TO ACTUAL TABLE TO SEARCH, consumes less disk space cuz it doesn't need extra table.