DELETE TOP x rows avoiding a table scan

The simplest way to delete the top X rows is use to use a derived table.

delete t1

from (select top (10000) *

        from t1 order by a) t1

This is one of the tricks I teach in my Advanced TSQL for Performance and Scalabilty course

The SQLCAT team posted a solution to deleteing the TOP x rows from a table using a view http://blogs.msdn.com/sqlcat/archive/2009/05/21/fast-ordered-delete.aspx. The view isn't needed you can just use a derived table as above.

You can do similar things with update,

update t1

  set b = 'y'

  from (select top (100) b

        from t1

        order by a ) t1      

If you want to know what was updated or deleted then you can use the OUTPUT clause.

 

delete t1

output deleted.a

from (select top (100) *

        from t1 order by a) t1

 

update t1

  set b = 'y'

OUTPUT inserted.a

  from (select top (100) *

        from t1

        order by a ) t1

 


-
Published 22 May 2009 14:00 by simonsabin

Comments

22 May 2009 16:38 by Lubor

# re: DELETE TOP x rows avoiding a table scan

The SQLCAT blog mentioned above is showing also the derived table solution and comparing its efficiency with using the view. Performance comparison is the crux of the blog there - the view is much more efficient. Of course, if you are deleting small amounts of records the difference is negligable. But in the case of MySpace with hundreds of millions of records logged daily the potential of saving 2/3 of I/Os and more than 50% of CPU for the delete operation is very attractive.

22 May 2009 20:05 by SimonS Blog on SQL Server Stuff

# UPDATE and DELETE TOP and ORDER BY : Part2

I earlier posted about using derived tables with UPDATE and DELETE to be able to update or delete the

# Performing fast SQL Server delete operations | John Sansom - SQL Server DBA in the UK

Pingback from  Performing fast SQL Server delete operations | John Sansom - SQL Server DBA in the UK

06 March 2010 09:54 by SimonS Blog on SQL Server Stuff

# How to archive data from a table to a local or remote database in SQL 2005 and SQL 2008

Often you have the need to archive data from a table. This leads to a number of challenges 1. How can

10 March 2010 13:27 by Simon Sabin UK SQL Consultant's Blog

# How to archive data from a table to a local or remote database in SQL 2005 and SQL 2008