ansaurus

Question

SQL Server 2005 efficient delete

Answer 1

+2 A:

See this thread, the original poster actually did some tests and posted the most efficient method. An MVP did originally chime in with an option to actually insert the data you want to retain into a temp table and then truncate the original table and reinsert.

RandomNoob 2010-01-04 21:03:14

I like it. Next time i have to do something like this ill have to give this a shot.

Ioxp 2010-01-04 21:07:42

We were initially planning to move the records to be retained to a new table. However tests showed that the time to rebuild the indexes from scratch would be too high.

no_one 2010-01-04 21:10:22

I do this regularly and it works like a charm. Much better than deleting large numbers of rows.

Bob Pusateri 2010-01-04 21:10:29

In the future i think that this will be the best solution for handling these types of issues. Accepted.

Ioxp 2010-01-04 21:29:51

depends on a lot of things - for example how big your table is and so on.In our case, it was far more efficient to delete than move the data. Partitioning - due to various constrains was not an option.

no_one 2010-01-04 23:33:18

Answer 2

+1 A:

I just did something like that recently. I simply created a SQL Server job that ran every 10 mins deleting a million rows. Code follows

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DELETE TOP 1000000 FROM BIG_TABLE WHERE CreatedDate <= '20080630'

Said table in question had about 900 mil rows to start with. Didn't notice any significant performance issues.

no_one 2010-01-04 21:07:17

So the script i have written removes data from a lot of tables (most of them have only 1 record in them) do i need to declare this once after the BEGIN TRAN or do i have to put it by every delete statement in my script?

Ioxp 2010-01-04 21:15:14

Before the BEGIN TRANSACTION.FOR exampleSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;DELETE TABLE ADELETE TABLE BCOMMIT TRANSET TRANSACTION ISOLATION LEVEL YOUR_ORIGINAL_LEVEL

no_one 2010-01-04 23:37:10

Answer 3

+1 A:

The most efficient way is to use partition switching, see Transferring Data Efficiently by Using Partition Switching. The drawback is that it requires planning ahead in how the partitions are deployed.

If partition switching is not available then the answer depends on the actual table schema. You better post the actual schema (including all indexes and most importantly the clustered key definiton) and the criteria that qualifies the delete candidates.

As for Q2, SQL Server had row level locking since mid 90s, I don't know what you're actually asking.

Remus Rusanu 2010-01-04 21:11:30

If u downvote, explain why

Remus Rusanu 2010-01-04 21:21:39

SQL Server has page level locking not row level locking. http://download.oracle.com/docs/cd/E12151_01/doc.150/e12156/ss_oracle_compared.htm

Ioxp 2010-01-04 21:24:22

I'd recommend you stick to MSDN documentation for SQL Server features, not Oracle's. See http://msdn.microsoft.com/en-us/library/ms189849.aspx and http://msdn.microsoft.com/en-us/library/ms189286.aspx for the available locking modes in SQL Server. Even the Oracle marketing document you link says 'SQL 7.0 has row level locking', and that refers to SQL 7, from 1997. current verion is 10.5 (2008 R2) and row-level locking has been the default modus operandi for SQL Server since SQL 2000 (ie. SQL 8.0). Making decision based on such outdated information can't possibly lead to good solutions.

Remus Rusanu 2010-01-04 21:32:56

partitioning requires enterprise edition of SQL Server 2005. For many databases which were migrated from SQL Server 2000 to 2005/2008 it is difficult to set up partitions if the tables are huge.

no_one 2010-01-04 23:35:14

ansaurus

tags:

views:

answers:

SQL Server 2005 efficient delete

related questions