check how many rows delete updated per day and store it in another table?

  • how to track how many rows updated or deleted per day in a single table

    and load the information in another table .

    please help with this.

  • SQL doesn't keep track of insert or deletion rates, so you'll need a trigger on the table or something like Change Data Capture or Change Tracking to do this.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You could compute changes to the clustered index as updates to the table (insert/delete/update).

    I have a script here: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

    Save index usage stats on a daily basis and compare with the previous day. Not exactly what you asked, but pretty close.

    -- Gianluca Sartori

  • All good choices. Another way would be to capture extended events (or trace) for all insert/update/delete commands against the table. It won't show rows affected, but you can tell what has been accessed.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • SQL does sort of track modification counts (for indexes, not heaps), but the data is cumulative and does necessarily persist. Look at view:

    sys.dm_db_index_operational_stats

    However, while an index is continuously active, the view should give you what you want. Capture the stats you want compare periodically, then compare one time snapshot to another to see what has occurred during that time interval. You can tell from the stats themselves whether such a comparison is valid.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • super48 (5/8/2015)


    how to track how many rows updated or deleted per day in a single table

    and load the information in another table .

    please help with this.

    Your turn. Why would you need to do such a thing?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • This is the interview question I faced 2 days back.

  • Thanks. I guess I should ask the interviewers why they would want to do such a thing without it being a full blown audit of the table with a trigger to capture the changes. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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