Delete Records

Transact-SQL
1

Is there a way if I can delete any 5 records of the table

2

delete mytable
where <insert predicate that identifies the 5 rows to be deleted>

3

If you just want to delete ANY arbitrary 5 records, use the top clause

    DELETE TOP (5) 
    FROM mytable
    --- WHERE ....  
    --- add the where clause if you want to limit the set from which the arbitrary 5 records are chosen.
4

;with cte
as (
select top 5 * from tablename
)

delete from cte

5

This will delete top5 I want to delete randomly 5 records

6

;with cte
as (
select top 5 * from tablename
order by rand(CHECKSUM(newid()))
)

delete from cte

7

"top 5" without an ORDER BY clause is random. Well, not statistically random, by certainly non-determinant.

8

I may regret this, but why would you want to delete a specific number of records but for those records to be random?

What you could do (and it would be a bit of a drag), is to create a new field - call it RandomNumber. Then create a cursor or loop to populate each row with a random number using RAND().

You could then use the DELETE TOP 5 and ORDER BY RandomNumber

Its hardly a sophisticated solution, but might do the trick.

9

I would be weary about creating a new column specifically for the purpose of deleting random records. If you want to delete truly randomly selected records, I would use what @viggneshwar suggested. But, unless the requirement is to delete mathematically random set of 5 records from the table, I would simply delete the top 5 without specifying any ordering. That would not be mathematically random in the sense that the probability that any given record would be included in the list is the same as that of any other record. However, as @gbritton said, which 5 records would get deleted will be non-deterministic.