SQL Server: Clustered Versus Non-Clustered Index

SELECT * FROM Sales
WITH (INDEX(PK_Sales))
WHERE Unit-Price > 100
AND Unit-Price < 300
AND Carrier-Tracking-Number = '0123-567A-A1'

For the above query the only index that exists is the non-clustered index on the primary key column. The given query uses the Carrier-Tracking-Number and Unit-Price columns in WHERE clause conditions. Therefore, DBA/Developer should create a non-clustered index on these columns. This would improve query performance because the optimizer would use an Index Scan or an Index Seek operation instead of an RID Lookup. An Index Scan scans the entire non-clustered index, while Index Seek does not. Therefore, to optimize query performance, DBA/Developer might also ensure that an Index Seek is performed. In this scenario, DBA/Developer could use the following statement to create a non-clustered index on the two columns:

CREATE INDEX IX_TrackingUnit-Price

ON Sales (Carrier-Tracking-Number, Unit-Price);

Then, DBA/Developer could remove the INDEX hint in your query so that the optimizer would not be forced to use the PK_Sales non-clustered index. DBA/Developer should not create a plan guide including the OPTIMIZE FOR hint. Plan guides can be used to optimize queries without modifying the query directly. Plan guides are helpful when DBA/Developer needs to specify query hints but cannot directly access the query, such as when it is embedded within an application. In a plan guide, DBA/Developer includes the query to be optimized, and the query hints or a query plan that should be used for optimization. The OPTIMIZE FOR query hint forces the optimizer to use a specific parameter value when optimizing the query. DBA/Developer should not include the MAXDOP query hint in the query. The MAXDOP query hint specifies an integer value that identifies the number of processors on which a single query may run. If the query is run on a computer with a single processor, the query hint is ignored. DBA/Developer can also accomplish this by setting the max degree of parallelism configuration option on the SQL Server instance, but this setting would affect all queries. DBA/Developer should not modify the WHERE clause to use the BETWEEN operator. The query would still access the same underlying data and would use a lookup operator to do so.