How clustered and Non clustered index makes search faster?

Question

Clustered indexes physically order the data on the disk.

Say i have table employee and columd employee_id. Now i store values 9, 6, 10, 4 under employee_id. with clustered index on employee_id. values on disk will be stored in sorted fashion i.e 4, 6, 9, 10. Now if i search on employee_id with id as 9, database can use search algorithm like binary search or some other to quick spot record with id 9. So its possible it does not fine the record in one operation as with binary serach. Is that correct?

Non clustered index A non-clustered index has a duplicate of the data from the indexed columns kept ordered together with pointers to the actual data rows (pointers to the clustered index if there is one). So if take same example as above. In this case Database will create separate object to store the data along with memory location.Something like this

9 -- its phyical location

6 -- its phyical location

10 -- its phyical location

4 -- its phyical location

So i first i need to search 10 in newly created object and get its memory location. Then go back to orginal memory location. So how come it makes search faster ?

Also as per my understanding, index should be created on column involved under where clause not under select clause . Right?

Answer 1

Consider a much larger data set - not four values, but a million. Querying via an index may indeed have a small overhead of having to perform another read from the table once the correct physical location is found, but for large data sets it's negligible when you consider how much faster searching an index is compared to performing a full scan on the unsorted table.