David Bakin’s programming blog.

An example of documenting a broken feature as normal behavior

Here’s an example of documenting a broken feature as normal behavior – furthermore, hiding it in the documentation as a “remark”.

The issue is the SQL Server “TOP” feature to return the “top” rows of a query result.  Consider the MSDN documentation MSDN TOP (Transact-SQL) here.  The “TOP” keyword in a SELECT statement is used to return only the first N rows of a query, for some N (or alternatively, N%).

Naturally—and I really mean, of course, there’s no other reasonable way for it to work—if the SELECT query includes an ORDER BY clause then it is respected and the sort occurs before the “top N” rows are selected.

Now for some reason they let you put the TOP keyword in an INSERT, UPDATE, or DELETE statement after the main keyword.  But in these cases, the ORDER BY clause in the subordinate SELECT is ignored!

E.g., given

INSERT TOP (2) INTO Table2 (ColumnB)
SELECT ColumnA FROM Table1

an arbitrary two rows are inserted, not the first two rows returned after the sort! That’s REMARKable! But documented!

(There’s a workaround they show you: Put the TOP keyword after the SELECT keyword, not after the INSERT keyword. But why allow the other syntax to be at all if it leads to such obviously broken behavior?  Alternatively: Why not fix it if you’re going to continue allowing that syntax?)