Say I've got a table (id int, Title varchar) of 1000 rows. Now If only to remove every nth record (every second, 3rd or fifth) . What's the most effective method of doing this ?
For SQL Server 2005+
Every second row
WITH example AS ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.id) AS rank FROM TABLE t) DELETE example WHERE rank%2 = 0
For each 3rd row, alter the WHERE clause to:
WHERE rank%3 = 0
Anf for each fifth row:
WHERE rank%5 = 0
This uses modulus, which returns the remaining from division. When the remainder is zero, the worthiness being divided is really a multiple from the divisor.