I discovered a usage of TOP that I didn’t know about today. I wanted to return the TOP x rows, where x was a number between 0 and 30. I tried SELECT TOP @x, but with no luck. Mentioning this to Rob later resulted in the revelation that you can specify SELECT TOP (@x). Or, if you wanted to be more involved, you can try SELECT TOP (SELECT MAX(Schema_id) FROM sys.objects) FROM sys.objects. Single parentheses are required for an expression to work.
The silly examples culminated until we had this little gem:
create table #nums (num int primary key);
insert #nums values (1)
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
insert #nums select count(*) over() + num from #nums
select *
from #nums n
cross apply
(select top (n.num) * from sys.objects) o
where n.num < 10