The INDEX, FORCESCAN, and FORCESEEK hints

The INDEX, FORCESCAN, and FORCESEEK hints are table hints, and we will consider each in turn. The INDEX hint can be used to request the query optimizer to use a specific index or indexes, an example of which was shown in our discussion of columnstore indexes in Chapter 11, An Introduction to Data Warehouses. Either the index ID or the name of the index can be used as a target for the query optimizer, but a name is the recommended way because we do not have control over the index ID values for nonclustered indexes. However, if you still want to use index ID values, they can be found on the index_id column of the sys.indexes catalog view, where index ID 0 is a heap, index ID 1 is a clustered index, and a value greater than 1 is a nonclustered index. On a query that uses a heap, using the INDEX(0) hint results in a Table Scan operator being used, whereas INDEX(1) returns an error message indicating that no such index exists. A query with a clustered...