Recursive Select - What is this really doing

  • 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.

  • 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