I must be tired, but I can't see the obvious here. Consider this query:
DELETE -- SELECT * FROM san.vm_capacity_fact WHERE tk IN ( SELECT TOP 1000 tk FROM san.vm_capacity_fact ORDER BY tk DESC )
IN SSMS, if I highlight from SELECT * to the end, I get 1000 rows.
However, if I run it from the DELETE (the commented SELECT is ignored of course), nothing is deleted.
FWIW, changing it to a JOIN has no effect. I still get no records deleted:
DELETE cf -- SELECT * FROM san.vm_capacity_fact cf INNER JOIN ( SELECT TOP 1000 tk FROM san.vm_capacity_fact ORDER BY tk DESC ) cf2 ON cf.tk = cf2.tk
I knew I was tired! I was relying on SSMS to give me the row count in the bottom-right of the results window for the delete. In reality, it only returns the count of rows selected. Since I selected no rows, the count was 0
I should add that I had SET NOCOUNT ON.
You get what you pay for!