How to select an sql index

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?

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.

This is the syntax for a table hint.

SELECT column_list FROM table_name WITH (INDEX (index_name) [, ...]);

in sql server which I think is your case based on your error.

As to whether the index will be picked up or not (both in oracle and sql server) will depend on a lot of other reasons. As the name indicates, it is just a hint to the optimizer. The cost of the query using the hint and without the hint will eventually be the deciding factors for the optimizer.

In most cases, you'll not see the need to specify the hint. The optimizer used this access path if using the index is the best way to retrieve the data and all the meta data (statistics) indicate the same.