Post reply

Recursive Select - What is this really doing

  • October 3, 2025 at 3:51 am

    #4664218

    I've noticed several instances of what looks like a recursive insert with the format:

    insert [dbo].[MyTable] select * from [dbo].[MyTable]

    I've been able to show that it isn't in fact inserting rows reclusively, but the most likely thing it seems to be associated with is a Create Clustered Index operation. Unfortunately I can't correlate every incident of this back to an index create statement.

    Does anyone know what SQL is doing here and why?

    • This topic was modified 3 weeks, 3 days ago by Leo.Miller.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • October 3, 2025 at 3:11 pm

    #4664584

    Hey Leo,

    your assumption is correct. This is an indicator of an Clustered Index Creation or Clustered Index REBUILD. This pattern (INSERT INTO MyTable SELECT * FROM MyTable) is an internal mechanism SQL Server may use during clustered index creation or rebuilds on heaps.

    It is a metadata-driven copy operation to transition the table structure.

    You can check it in the [command] in sys.dm_exec_requests

    SELECT r.session_id,
           r.command,
           t.text
    FROM   sys.dm_exec_requests r
           CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    WHERE  t.text LIKE '%INSERT%MyTable%SELECT%MyTable%'

    Microsoft Certified Master: SQL Server 2008
    MVP - Data Platform (2013 - ...)
    my blog: http://www.sqlmaster.de (german only!)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply