Is deleting all records in a table a bad practice in SQL Server?

Question

I am moving a system from a VB/Access app to SQL server. One common thing in the access database is the use of tables to hold data that is being calculated and then using that data for a report.
eg.

delete from treporttable
insert into treporttable (.... this thing and that thing)
Update treportable set x = x * price where (...etc)

and then report runs from treporttable

I have heard that SQL server does not like it when all records from a table are deleted as it creates huge logs etc. I tried temp sql tables but they don't persists long enough for the report which is in a different process to run and report off of.

There are a number of places where this is done to different report tables in the application. The reports can be run many times a day and have a large number of records created in the report tables.

Can anyone tell me if there is a best practise for this or if my information about the logs is incorrect and this code will be fine in SQL server.

Answer 1

If you do not need to log the deletion activity you can use the truncate table command.

From books online:

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx

Answer 2

delete from sometable

Is going to allow you to rollback the change. So if your table is very large, then this can cause a lot of memory useage and time.

However, if you have no fear of failure then:

truncate sometable

Will perform nearly instantly, and with minimal memory requirements. There is no rollback though.

Answer 3

The answer depends on the recovery model of your database. If you are in full recovery mode, then you have transaction logs that could become very large when you delete a lot of data. However, if you're backing up transaction logs on a regular basis to free the space, this might not be a concern for you.

Generally speaking, if the transaction logging doesn't matter to you at all, you should TRUNCATE the table instead. Be mindful, though, of any key seeds, because TRUNCATE will reseed the table.

EDIT: Note that even if the recovery model is set to Simple, your transaction logs will grow during a mass delete. The transaction logs will just be cleared afterward (without releasing the space). The idea is that DELETE will create a transaction even temporarily.

Answer 4

I think your example has a possible concurrency issue. What if multiple processes are using the table at the same time? If you add a JOB_ID column or something like that will allow you to clear the relevant entries in this table without clobbering the data being used by another process.

Answer 5

Consider using temporary tables. Their names start with # and they are deleted when nobody refers to them. Example:

create table #myreport (
    id identity,
    col1,
    ...
)

Temporary tables are made to be thrown away, and that happens very efficiently.

Another option is using TRUNCATE TABLE instead of DELETE. The truncate will not grow the log file.

Answer 6

To Nathan Feger:

You can rollback from TRUNCATE. See for yourself:

CREATE TABLE dbo.Test(i INT); GO INSERT dbo.Test(i) SELECT 1; GO BEGIN TRAN TRUNCATE TABLE dbo.Test; SELECT i FROM dbo.Test; ROLLBACK GO SELECT i FROM dbo.Test; GO

i

(0 row(s) affected)

i

1

(1 row(s) affected)

Answer 7

Actually tables such as treporttable do not need to be recovered to a point of time. As such, they can live in a separate database with simple recovery mode. That eases the burden of logging.

Answer 8

You could also DROP the table, and recreate it...if there are no relationships.

The [DROP table] statement is transactionally safe whereas [TRUNCATE] is not.

So it depends on your schema which direction you want to go!!

Also, use SQL Profiler to analyze your execution times. Test it out and see which is best!!

Much love to Brizzy! Always wanted to go! Almost worked there too...

Answer 9

There are a number of ways to handle this. First you can move the creation of the data to running of the report itself. This I feel is the best way to handle, then you can use temp tables to temporarily stage your data and no one will have concurency issues if multiple people try to run the report at the same time. Depending on how many reports we are talking about, it could take some time to do this, so you may need another short term solutio n as well.

Second you could move all your reporting tables to a difffernt db that is set to simple mode and truncate them before running your queries to populate. This is closest to your current process, but if multiple users are trying to run the same report could be an issue.

Third you could set up a job to populate the tables (still in separate db set to simple recovery) once a day (truncating at that time). Then anyone running a report that day will see the same data and there will be no concurrency issues. However the data will not be up-to-the minute. You also could set up a reporting data awarehouse, but that is probably overkill in your case.