FORCESEEK Enhancement

-- FORCESEEK hint is extended to specify columns used to seek in the specified index

/*
Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table.
Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows,
the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset.
But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

When SQL Server chooses seek over scan while retrieving record details, it will compare key value with search input, based on comparison result,
Query engine will move to appropriate page.
Suppose, index has multiple columns, if we don't want all key columns to consider, in SQL Server 2012, we can mention the index columns to consider when the index has multiple key columns.
*/

SELECT CompanyID,CompanyName,Amount
FROM COMPANIES
WITH (FORCESEEK(Idx_Company(CompanyID)))
WHERE CompanyID = 1