Using TOP in a SELECT without ordering the results from which the "top" results are chosen will return a seemingly random
set of rows, and is surely a mistake.
The same random behavior also occurs when using TOP in a DELETE, INSERT, UPDATE and
MERGE.
Noncompliant code example
SELECT TOP 10 -- Noncompliant selects 10 random rows
fname, lname, city
FROM people
WHERE city IS NOT NULL;
DELETE TOP (10) -- Noncompliant deletes 10 random rows
FROM PurchaseOrder
WHERE DueDate < '20020701';
Compliant solution
SELECT TOP 10
fname, lname, city
FROM people
WHERE city IS NOT NULL
ORDER BY birthdate;
DELETE
FROM PurchaseOrder
WHERE OrderID IN (
SELECT TOP 10
OrderID
FROM PurchaseOrder
WHERE DueDate < '20020701'
ORDER BY DueDate ASC
);
