TOP vs Rowcount

  • Hi,

    I have been reading BOL, and note that the rowcount should not be used, and that future releases will may support it. The recommendation is to use TOP. But how can I be sure that when using TOP to delete, I will get the correct rows. I mean, once I have processed my records, I want to delete from temp table, so I can get the next batch:

    WHILE SELECT COUNT FROM #Temp > 1

    BEGIN

    INSERT NewClients(ClientID) Select top 1000 From #Temp order by 1

    Exec usp_RunStuff @CheckID

    DELETE top 1000 #Temp --order by 1

    END

    Currently, I am using:

    SET ROWCOUNT @batchsize

    SET @rc = @batchsize

    WHILE @rc = @batchsize

    BEGIN

    BEGIN TRAN

    INSERT NewClients(ClientID)

    SELECT C.ClientID FROM Temp C

    LEFT JOIN NewClients NC

    ON C.Client = NC.Client

    WHERE NC.Client IS NULL

    SET @rc = @@Rowcount

    EXEC usp_RunStuff @checkMode

    COMMIT

    END

  • Either way you go, to ensure that you're getting the exact same result set you need to use ORDER BY to ensure that the 1000 rows you get is the same. ROWCOUNT or TOP could return different results for the same query without the ORDER BY statement. Once that's in place, TOP should work just fine in the same way that you're working now.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • you could also use the OUTPUT clause:

    [font="Courier New"]declare @batch table( CliendId ...)

    WHILE SELECT COUNT FROM #Temp > 1

    BEGIN

    INSERT NewClients(ClientID) Select top 1000 From #Temp

    OUTPUT inserted.ClientId INTO @batch

    Exec usp_RunStuff @CheckID

    DELETE #Temp FROM #Temp T join @batch on ...

    END [/font]

    still better alternatives would be to either add an identity column to #Temp during its creation and range your inserts based on it; or if ClientID is numeric, range your inserts based on ClientID.

    [font="Courier New"]select ..., identity(int) as row_num into #Temp -- add identity while creating the table

    create index x0 on #Temp(ClientID|row_num)

    ...

    declare @Last int, @current int, @batch_size int

    set @batch_size = 1000

    select @current = min(ClientID|row_num), @Last = max(ClientID|row_num) from #Temp

    while @current <= @Last

    begin

    INSERT NewClients(ClientID)

    Select ClientId From #Temp

    where ClientID|row_num between @current and @current+@batch_size

    Exec usp_RunStuff @CheckID

    set @current = @current + @batch_size + 1

    end[/font]

  • IF OBJECT_ID('tempdb..#temp') IS NOT NULL

    DROP TABLE #temp

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL

    DROP TABLE #temp2

    SELECT * INTO #temp

    FROM sys.columns

    SELECT COUNT(*) FROM #temp

    SELECT TOP(500) *

    INTO #temp2

    FROM [#temp] ORDER BY [#temp].column_id

    ;WITH x AS (SELECT TOP(500) * FROM [#temp] ORDER BY [#temp].column_id)

    DELETE FROM x

    SELECT COUNT(*) FROM #temp

    SELECT COUNT(*) FROM #temp2

    SELECT COUNT(*) FROM

    (

    SELECT * FROM #temp

    UNION ALL

    SELECT * FROM [#temp2]) AS x

    example:

  • FYI, you do need to make sure that the order by's on the insert and the CTE match. If they don't you will delete the wrong rows.

    i.e. It should be on a UK of some sort.

Viewing 5 posts - 1 through 4 (of 4 total)

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