I am thinking of using a TRUNCATE TABLE SQL command to quickly clear a database table-- daily. I have never used this command and I plan on executing it within a Java Servlet. I want to use this command versus -- delete table-- which would require that I also recreate the table on the fly [daily]-- because I think it will be less invasive. Anyone have any experience with this? Q...
Yes. It sounds exactly like what you need. However--why do you want to clear out the table? Why not just put a date field on it and then make sure the query you use to access your data uses that field in its WHERE clause?
Because I like keeping my web host happy and in this case-- I do not need the data. It is of a daily nature and no use after the day it is provided for. Also-- it should make the database faster-- when useless data is cleared out. Q...
TRUNCATE TABLE is good - it's fast and doesn't write any data to the log file. DELETE FROM TABLE is OK - but it writes to the log file and can take a long time. DROP TABLE is OK if you can afford not to have a table in existence whilst it is being dropped and recreated.
Truncate table directly de allocates the data from page while delete work on row basis. Because of this in truncate data entry doesn’t exist at log label while in delete data present at log label. You can use truncate if you don’t want to rollback your data at any condition. Didn’t get much clarity on your last statement that you need to recreate the table every time. Truncate also doesn’t delete the schema of the table. It will be there only data will truncate.
Any time you need to flush everything from a table but still keep the table, use truncate. If you need to keep the primary key count, use delete.