Member Avatar for ranj_s20

Hi,


Can any one suggest the method to delete bulk records upto 1 lakh without specifying any condition? There's no Id to be specified in checking in the where condition. Please suggest an idea .

Thanks In advance

  • 3 Contributors
  • 6 Replies
  • 93 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Netcode

Dani AI

Generated

The core issue in this thread is that “first 100,000 rows” is not defined unless an explicit ordering exists. SQL tables do not guarantee physical order. If the goal is to remove the oldest data, a deterministic ordering column (timestamp, identity, or other sequence) is required; otherwise the choice of rows will be arbitrary. correctly asked for the table layout, and pointed out that fast whole-table clears are a different problem to removing the first N rows.

When removing a specific N rows, a safe pattern is to enumerate rows by a chosen ordering and delete those with low sequence numbers. Example using ROW_NUMBER() to identify the target set (assumes a suitable ordering column exists):

WITH Marked AS (
  SELECT PrimaryKeyCol,
         ROW_NUMBER() OVER (ORDER BY CreatedAt ASC) AS rn
  FROM dbo.MyTable
)
DELETE T
FROM dbo.MyTable T
JOIN Marked M ON T.PrimaryKeyCol = M.PrimaryKeyCol
WHERE M.rn <= 100000;

To avoid long transactions, timeouts and large log growth, perform the work in repeated small batches (1k–10k rows depending on workload), committing between batches and stopping when no rows remain. A batch loop can use OFFSET/FETCH or a CTE that limits the batch; check @@ROWCOUNT after each delete and break when zero.

Important cautions: foreign keys and cascading deletes can dramatically increase work and must be handled (child rows first or use cascading if intended). Triggers will fire on each deleted row. DELETE is fully logged; truncation or partition-switching are faster alternatives when removing whole partitions but have constraints. Always test on a copy, take backups, and run large deletes during low activity windows.

References: ROW_NUMBER() usage (Microsoft Docs) and TRUNCATE/partition options (Microsoft Docs).

Recommended Answers

All 6 Replies

Member Avatar for pritaeas

Can you please explain with your table definition and a small example what it is that you want to achieve ?

Member Avatar for ranj_s20

Hi,

I just want to delete the old data of datbase. Hence i need to delete about 1 lakh records from different tables. Hence i need the query to delete the records.

Member Avatar for pritaeas

DELETE FROM tablename

But wouldn't it be easier to just drop everything and recreate all tables ?

Member Avatar for ranj_s20

No yaar i want to delete the first 1 lakh record,how should i get the count,rather to delete this much record time out will show on executing query

Member Avatar for pritaeas

See this thread on how to do it. If you omit the where in the sub-query, then the natural order of the table will be used.

DELETE FROM tablename WHERE id IN (SELECT TOP 5 id FROM tablename)
Edited by pritaeas because: n/a
Member Avatar for Netcode

You need am ID to specify a WHERE condition. Else, you may end up losing the whole data in the table. In order to remove the data from the table while retaining table structure, do this:

TRUNCATE TABLENAME

but you can delete a selected number from the table just as specified below:

DELETE TOP 1000 [ID]
      ,[Reference_Number]
      ,[FirstName]
      ,[LastName]
  FROM [HumanResource].[dbo].[Employees]
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.