How to select an sql index

Question

How to select an sql index

I have an table TabEmp with fields c_ID (primary key), and c_Emp_ID.

I created an index on it idx_TabEmp (non-clustered) with the fields c_ID (primary key) and c_Emp_ID

I am using select statement

select * from index = idx_TabEmp 

It throws an error

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax

I am using

select * from TabEmp (index = idx_TabEmp)

it works, but I am not sure if that is the correct way to select an index

Can you please tell me the correct way to query an index?

Answer 1

The index is something which the optimizer picks up "automagically - ideally you don't need to force select an index.

If you really want to force select the index, use index hint

SELECT *
FROM TabEmp
WITH (INDEX(idx_TabEmp))

---

Also, note that with no filter conditions (ie, no WHERE clauses), the Index does not come into the picture since you are not searching for a specific data - you're selecting everything.

To provide the book analogy - when you're reading a full book cover to cover - you don't need to look at the Index. It's only when you're searching for a specific page that you look at the index and find what you want.