SQL Server 2008 introduces a new hint, the FORCESEEK hint that allows to "kindly" suggest Query Optimizer to use a seek operation instead of a scan. You can use this either as a table hint or in a query plan as a query hint.

So far so good, it was late last night when I started to test it. And fatique causes not just car accidents but also big SQL mistakes. The Books Online samples worked just fine:

USE AdventureWorks;
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO
SELECT *
FROM Sales.SalesOrderHeader AS h
INNER JOIN Sales.SalesOrderDetail AS d WITH (FORCESEEK)
    ON h.SalesOrderID = d.SalesOrderID 
WHERE h.TotalDue > 100
AND (d.OrderQty > 5 OR d.LineTotal < 1000.00);
GO 

But no, I wanted an example that it's simpler.

1. MISTAKE #01: 

So I tried the next query:

USE tempdb;
GO
CREATE TABLE T(I INT PRIMARY KEY NOT NULL);
GO
INSERT INTO T VALUES (1),(2),(3);
GO
SELECT *
FROM T WITH (FORCESEEK);

The result was normal:
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Dummy, FORCESEEK (f-o-r-c-e  s-e-e-k) , it needs a search condition (s-e-e-k). So if I run

SELECT *
FROM T WITH (FORCESEEK)
WHERE I=1;

it will work. But in this case the Query Optimizer uses an index seek anyway so the hint is useless.

2. MISTAKE #02:

Question: How can I generate the simplest query that the QO would execute as a scan and I can use the FORCESEEK operator to change its behavior?

WRONG QUESTION!

The right question is: How to generate a seek that QO decides to implement as scan, and FORCESEEK to help you eliminate a possible deadlock or other problems caused by the QO decision? Usually the answer is not a simple query. (Thanks Remus for sorting this out).

So I learned the lesson that you should test features considering their purpose, not yours Cool