How to delete the top 1000 rows from a table using Sql Server 2008?
The code you tried is in fact two statements. A DELETE
followed by a SELECT
.
You don't define TOP
as ordered by what.
For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.
;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE
How to delete last N records in SQL Server 2008 R2 database?
Here you go:
DELETE FROM mytable
WHERE %%physloc%% IN (SELECT TOP 100 %%physloc%% AS RN FROM mytable ORDER BY RN DESC);
How to delete large data of table in SQL without log?
If you are Deleting All the rows in that table the simplest option is to Truncate table, something like
TRUNCATE TABLE LargeTable
GO
Truncate table will simply empty the table, you cannot use WHERE clause to limit the rows being deleted and no triggers will be fired.
On the other hand if you are deleting more than 80-90 Percent of the data, say if you have total of 11 million rows and you want to delete 10 million another way would be to Insert these 1 million rows (records you want to keep) to another staging table. Truncate this large table and Insert back these 1 million rows.
Or if permissions/views or other objects which has this large table as their underlying table doesn't get affected by dropping this table, you can get these relatively small amounts of the rows into another table, drop this table and create another table with same schema, and import these rows back into this ex-Large table.
One last option I can think of is to change your database's
Recovery Mode to SIMPLE
and then delete rows in smaller batches using a while loop something like this:DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
BEGIN
-- Delete some small number of rows at a time
DELETE TOP (10000) LargeTable
WHERE readTime < dateadd(MONTH,-7,GETDATE())
SET @Deleted_Rows = @@ROWCOUNT;
END
and don't forget to change the Recovery mode back to full and I think you have to take a backup to make it fully effective (the change or recovery modes).
How-To delete 8,500,000 Records from one table on sql server
You can do it in chunks. For example, every 10 seconds execute:
delete from activities where activityid in
(select top 1000 activityid from activities where unt_uid is null)
Obviously define the row count (I arbitrarily picked 1000) and interval (I picked 10 seconds) which makes the most sense for your application.
How to delete the more than 10 records from top table, using Sql Server 2008?
In SQL Server, you can use row_number()
to enumerate the values and then delete the oldest ones using where
:
with todelete as (
select t.*, row_number() over (order by id desc) as seqnum
from t
)
delete from todelete
where seqnum > 10;
Your approach also works. You can do:
delete from t
where t.id not in (select top 10 t2.id from t t2 order by t2.id desc);
Note: This uses not in
, so it assumes that id
is never NULL
. That seems reasonable in this situation.
Deleting 1 millions rows in SQL Server
Here is a structure for a batched delete as suggested above. Do not try 1M at once...
The size of the batch and the waitfor delay are obviously quite variable, and would depend on your servers capabilities, as well as your need to mitigate contention. You may need to manually delete some rows, measuring how long they take, and adjust your batch size to something your server can handle. As mentioned above, anything over 5000 can cause locking (which I was not aware of).
This would be best done after hours... but 1M rows is really not a lot for SQL to handle. If you watch your messages in SSMS, it may take a while for the print output to show, but it will after several batches, just be aware it won't update in real-time.
Edit: Added a stop time @MAXRUNTIME
& @BSTOPATMAXTIME
. If you set @BSTOPATMAXTIME
to 1, the script will stop on it's own at the desired time, say 8:00AM. This way you can schedule it nightly to start at say midnight, and it will stop before production at 8AM.
Edit: Answer is pretty popular, so I have added the RAISERROR
in lieu of PRINT
per comments.
DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS
WHILE @BATCHSIZE>0
BEGIN
-- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
BEGIN
RETURN
END
DELETE TOP(@BATCHSIZE)
FROM SOMETABLE
WHERE 1=2
SET @BATCHSIZE=@@ROWCOUNT
SET @ITERATION=@ITERATION+1
SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
RAISERROR (@MSG, 0, 1) WITH NOWAIT
WAITFOR DELAY @WAITFORVAL
END
How to delete top n rows of a SQL Server table in asp.net?
See this link
Change it as per your table and Database name. Also order it if you want to
cmd.CommandText = ";WITH CTE AS ( SELECT TOP 5 * FROM PubTb ) DELETE FROM CTE"
Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
No.
You want a DELETE with a WHERE clause: this is standard SQL.
What you can do is batch deletes like this:
SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
DELETE TOP (xxx) MyTable WHERE ...
Or if you want to remove a very high percentage of rows...
SELECT col1, col2, ... INTO #Holdingtable
FROM MyTable WHERE ..opposite condition..
TRUNCATE TABLE MyTable
INSERT MyTable (col1, col2, ...)
SELECT col1, col2, ... FROM #Holdingtable
Delete all but top n from database table in SQL
DELETE FROM Table WHERE ID NOT IN (SELECT TOP 10 ID FROM Table)
Edit:
Chris brings up a good performance hit since the TOP 10 query would be run for each row. If this is a one time thing, then it may not be as big of a deal, but if it is a common thing, then I did look closer at it.
Related Topics
Generate Random Int Value from 3 to 6
Previous Monday & Previous Sunday's Date Based on Today's Date
MySQL Strip Time Component from Datetime
Postgresql Where Count Condition
Why Does the Wm_Concat Not Work Here
Functions VS Stored Procedures
Try_Convert for SQL Server 2008 R2
Max VS Top 1 - Which Is Better
Count Cumulative Total in Postgresql
Get the First and Last Date of Next Month in MySQL
Counting the Number of Occurrences of a Substring Within a String in Postgresql
"Select Distinct" Ignores Different Cases
The Difference Between 'And' and '&&' in SQL
For JSON Path Returns Less Number of Rows on Azure SQL
Add a Column to Existing Table and Uniquely Number Them on Ms SQL Server