Delete old entries from table in MSSQL
a table in my MSSQL db is getting over 500MB. I would like to delete the x last entries so the table size is only 100MB. This should be a task which runs once a week. How could I do this?
Table before deleting the old entries:
Table after deleting the old entries:
You can use DATALENGTH to get the size of the data in a particular column. With a window function, you can sum up a running total of DATALENGTH values. Then you can delete all records in a table that push you past a desired max table size. Here's an example:
-- Sample table with a VARBINARY(MAX) column CREATE TABLE tmp (id INT IDENTITY(1,1) PRIMARY KEY, col VARBINARY(MAX)) -- Insert sample data - 20 bytes per row. ;WITH cte AS ( SELECT 1 AS rn, HASHBYTES('sha1', 'somerandomtext') t UNION all SELECT rn + 1, HASHBYTES('sha1', 'somerandomtext') FROM cte WHERE rn< 5000 ) INSERT INTO tmp (col) SELECT t FROM cte OPTION (maxrecursion 0) -- @total_bytes is the desired size of the table after the delete statement DECLARE @total_bytes int = 200 -- Use the SUM window function to get a running total of the DATALENGTH -- of the VARBINARY field, and delete when the running total exceeds -- the desired table size. -- You can order the window function however you want to delete rows -- in the correct sequence. DELETE t FROM tmp t INNER JOIN ( SELECT id, SUM(DATALENGTH(col)) OVER (ORDER BY id) total_size FROM tmp )sq ON t.id = sq.id AND sq.total_size > @total_bytes
Now check what's left in tmp: 10 rows, and the total size of the "col" column matches the 200 byte size specified in the @total_bytes variable.
UPDATE: Here's an example using your sample data:
CREATE TABLE tmp (id INT PRIMARY KEY, contact VARCHAR(100), country VARCHAR(25)) GO INSERT INTO tmp VALUES (1, 'Maria Anders', 'Germany'), (2, 'Francisco Chang', 'Mexico'), (3, 'Roland Mendel', 'Austria'), (4, 'Helen Bennett', 'UK'), (5, 'Yoshi Tannamuri', 'Canada'), (6, 'Giovanni Rovelli', 'Italy') GO -- @total_bytes is the desired size of the table after the delete statement DECLARE @total_bytes INT = 40 -- Use the SUM window function to get a running total of the DATALENGTH -- of the VARBINARY field, and delete when the running total exceeds -- the desired table size. -- You can order the window function however you want to delete rows -- in the correct sequence. DELETE t FROM tmp t INNER JOIN ( SELECT id, SUM(DATALENGTH(contact)) OVER (ORDER BY id) + SUM(DATALENGTH(country)) OVER (ORDER BY id) total_size FROM tmp )sq ON t.id = sq.id AND sq.total_size > @total_bytes SELECT * FROM tmp -- 2 rows left!
Delete old entries from table in MSSQL, If you want to remove all rows in a big table, you should use the TRUNCATE TABLE statement which is more efficient than the DELETE statement. SQL DELETE Here is an Example: If you want to delete entries that are more than 30 days old from some table 'tableName', having column entry 'datetime'. Then following query runs every day which will do required clean-up action.
DELETE FROM TABLE_NAME WHERE date_column < '2018-01-01';
This will delete all data that entered before January 2018
SQL DELETE - Deleting Data in a Table or Multiple Tables, The following example deletes all rows from the SalesPersonQuotaHistory table in the AdventureWorks2012 database because a WHERE clause Delete All Data in a MySQL Table MySQL is the world’s most popular open source database, recognized for its speed and reliability. This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields.
if you want to delete last 7days data
delete from table_name WHERE date_column >= DATEADD(day,-7, GETDATE())
DELETE (Transact-SQL), The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted! Demo Database. Below is a To remove one or more rows from a table completely, you use the DELETE statement. The following illustrates its syntax: DELETE [ TOP (expression) [ PERCENT ] ] FROM table_name [ WHERE search_condition]; First, you specify the name of the table from which the rows are to be deleted in the FROM clause.
SQL DELETE Statement, I have a large table with millions of historical records. I was asked to remove millions of old records from this table, but this table is accessed by Assuming that your table has a Primary Key and a column with a timestamp indicating when the record was inserted), you can use a query along the lines of. delete from tableToDeleteFrom where tablePK in (select tablePK from tableToDeleteFrom where someThresholdDate <= @someThresholdDate)
Deleting Historical Data from a Large Highly Concurrent SQL Server , If you are deleting 95% of a table and keeping 5%, it can actually be quicker to move the rows you want to keep into a new table, drop the old To delete data from multiple tables using a single DELETE statement, you use the DELETE JOIN statement which we will cover in the next tutorial. To delete all rows in a table without the need of knowing how many rows deleted, you should use the TRUNCATE TABLE statement to get better performance.
Fastest way to Delete Large Number of Records in SQL Server, Let's take a look at our table and removing some records. How to delete rows with no where clause. The following example deletes all rows from In MSSQL, you could create a job to run daily which deletes the first 1000 rows of your query. To steal Adam's query - DELETE TOP 1000 FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate() This would be very safe and would get rid of your data in three months or so safely and would them also maintain the size of the db in the future.