The CTE most efficient ways to delete rows from table as per your need.
If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to sub-query:
Example 1,
;WITH CTE AS
(
SELECT TOP
10*
FROM UserDetail
)
DELETE FROM CTE
If you want to delete specific subset of rows instead of arbitrary subset, you should explicitly specify order to sub-query:
Example 2,
DELETE FROM UserDetail
WHERE UserID IN
(
SELECT TOP 10
UserID
FROM [UserDetail]
WHERE UserID =10
ORDER BY UserID DESC
)
