Menu
I need to clean out a very bloated SQL database by deleting records that are older than two years from a number of tables. What is the most efficient way of doing this?.
444
The DELETE command is used to delete existing records in a table.
To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date.
Answer: C. TRUNCATE is a DDL command. It removes the records from the table without any condition. It is not the part of any ongoing transaction and an uncommitted transaction in the session is committed after TRUNCATE is executed.
Do you have any way to determine how "old" a record is? (i.e., is there a column in the table that represents either the age of the row or a date that can be used to calculate the age?). If so, it should be a simple
DELETE FROM Table WHERE Age > 2
For example, if you have a DateTime column called CreateDate, you could do this:
DELETE FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
