-2

I am wondering why this is coded this way.

declare @messages table ([SEQUENCE_ID] BIGINT, [MESSAGE_ID] BIGINT PRIMARY KEY CLUSTERED ([SEQUENCE_ID])) ; delete T output deleted.SEQUENCE_ID, deleted.MESSAGE_ID into @messages from (select top (65536) * from source order by SEQUENCE_ID) T ;

What is the benefit of this over a simpler appearing SELECT ... INTO @messages ?
I can see one possible, that the DELETE T will free up any locks on the source table.
Another may be that SELECT INTO creates a heap, which then needs reordering on the key, so this reduces IO operations.

But what are the underlying reasons why this code is a good idea ?
I am assuming it is a good idea because it's in production code.

Improve this question
7
  • 4
    It's a DELETE ... OUTPUT. So it deletes the source rows that it inserts into the table variable. Commented Jul 4, 2022 at 17:01
  • Yes, I understand that, David. That was not either of the questions I asked. 1) what's the benefit of this over SELECT ... INTO 2) why is it a good idea I can't find any explanations of code like this. Lots of short examples of this construct, but no reasons why it would be preferable to adopt this. It's the difference between useless documentation, and useful explanation. Commented Jul 5, 2022 at 14:35
  • 2
    It does something completely different from SELECT ... INTO. DELETE ... OUTPUT is commonly used to treat a table as a queue. See learn.microsoft.com/en-us/sql/t-sql/queries/… Commented Jul 5, 2022 at 14:45
  • 1
    It's a single statement, rather than selecting and deleting from two different tables, and can be more performant and less prone to deadlocks Commented Jul 6, 2022 at 0:53
  • "What is the benefit of this over" - hard to compare two things that have different effects. You could edit your question to include code you think is equivalent, then comparing benefits would be a valid question. Commented Jul 6, 2022 at 13:12

1 Answer 1

Reset to default
0

In a comment, you say

I can understand the queue implementation if it deleted from a table. But this is deleting from a (nested?) query result, so how does it affect the table "source" that is aliased as T ?

So one specific thing you don't know about is what happens here:

CREATE TABLE Rabbit ( Id int, Name nvarchar(200) ); INSERT Rabbit ( Id, Name ) VALUES ( 1, 'Flopsy' ) , ( 2, 'Mopsy' ) ; DELETE T FROM ( SELECT * FROM Rabbit ) T

to which I can only suggest try it !

What you should just have discovered is that this deletes records from the underlying table. Now, this (obviously) wouldn't work for every subquery, but it works for this one.

So, we can use this construct in exactly the 'use a table as a queue' mechanism that you already understand.

Why the subquery rather than just DELETE TOP ( 65536 )? Because that wouldn't allow us to say which 65536 records we most wanted deleted - you can't put an ORDER BY in a DELETE.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.