1
I have a table with 80 million records, clean it without deleting the table, because it is used in real time, what is the best suggestion so that I can clean this table without losing the functionality of it? And which way is the fastest and most suitable?
6
Truncate - is a DDL command that removes all rows from a table. It can’t be reversed, it’s faster and it doesn’t use as much space as undo a delete.
Delete - is a DML command used to remove rows from a table. After running the COMMIT and ROLLBACK commands can be executed to confirm or undo the operation. When no condition (WHERE) all lines are deleted.
Drop - is a DDL command that removes tables from the database, all lines, privileges and indices will be deleted. It cannot be reversed.
The most recommended would be a truncate, but maybe only a delete would solve.
In his case, if there are no foreign keys, only the simple DELETE FROM 'TABLE' already meets it.
I used the truncate even, because I just needed to clean the records of the table, without changing the structure of it, it worked and was instant.
Regarding "both Drop and Truncate deletes the table", TRUNCATE does not delete the table but the table contents.
Yes really, I’ve corrected the post
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
Does the table have foreign keys? A DELETE FROM 'TABLE' is not what you need?
– Augusto Henrique