Oops, I deleted that data

  • brian.pawson wrote:

    I've started using MERGE for updating records because UPDATE just freaks me out. It takes more thinking out but will get faster with practice and it's good to get familiar with merge.

    Something like below. I can highlight the  inner SELECT to verify the number of records. Maybe wrap in a BEGIN TRANS - COMMIT as belt and braces approach. Our tables all have triggers for insert, update delete to copy rows to corresponding history tables but performance impact on large databases might be unpalatable (our tables are less than a million rows).

    MERGE       account                                     TARGET
    USING (SELECT id,
    COUNT(*) OVER() item_count,
    0 active_yn,
    account_code
    FROM account
    WHERE account_code = '4c') SOURCE(id,item_count,active_yn,account_code)
    ON TARGET.id = SOURCE.id
    AND TARGET.account_code = SOURCE.account_code
    WHEN MATCHED
    THEN UPDATE SET TARGET.active_yn = SOURCE.active_yn;

    ok - unless i'm being really stupid - i think you overthought the update/merge issue - feel free to mock me if i got this wrong

    update   account SET active_yn=0 WHERE account_code = '4c'

     

     

    MVDBA

  • jermitts 57867 wrote:

    Developers could certainly have read access to production data. If a production change needs to occur to data for whatever reason, the production change should be done by an ops DBA, who would document the request, the change approval,  the testing of the change script, the outcome of the testing, the user approval of the test results, the production execution, and the outcome of the production execution. This is the *most basic* form of segregation of duties. Even if your "ops DBA" is just a technician who isn't a developer. If your company is publicly held or owned by a publicly held corporation in the US, this isn't even an option, it's a requirement.

    If your developer has write access to your production databases, you're operating on a development database, and operating far outside best practices.

    We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.

    Of course, they're not allowed to write to Prod Copy databases, but they can read them with the appropriate PII scramble of stuff they shouldn't need.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin wrote:

    We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.

    .

    Carefull with that one - if you are in the UK then you might fall foul of a few GDPR complexities about moving customer data to development servers - to be honest Steve Jones is your man on this one. he knows way more than me. - but certainly data masking is a key point.

    I have a similar setup to yourself, and i can see why you have it - It's quite rare that people do automated DR testing (mine failed this morning because someone had added a new filegroup to a database)

    My DR databases are locked down and if they need to be used then I grant temporary access and try to make sure  that anything sensitive is not exposed (unless the dev really needs that data for the task)

    we are far from perfect, but maybe with the input from other people on this site we can progress day by day ..... maybe 🙂

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    Brandie Tarvin wrote:

    We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.

    .

    Carefull with that one - if you are in the UK then you might fall foul of a few GDPR complexities about moving customer data to development servers - to be honest Steve Jones is your man on this one. he knows way more than me. - but certainly data masking is a key point.

    Did you not see my second paragraph?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I use CTEs to do updates and deletes against a table.

    {

    with cte as (

    select id, field2change from table

    where id = '1234'

    )

    update cte

    set field2change = '4321'

    }

    This way with the cte you first verify you only query what you want to update.

    And of course I agree with defining the transaction with a transaction name, just in case there's a need to rollback.

  • Jeff Moden wrote:

    stelios21 wrote:

    Not deploying your own code forces you to think about all the things you have to take care will not go wrong when someone else must deploy your code. That person may not have any idea of what your code does and how it should be deployed and does not need to. Not only it forces you to think, it makes you take steps to ensure bad things will not happen.

    That's some awesome feedback.  Thank you.

    I'm right there with the DBA you talked about.  I won't deploy by hand.  It has to be in the script and the script has to follow all the rules.

    I also like your second paragraph... it's exactly the way I've always felt.  It's also a great teaching tool because the same types of things also work very well inside stored procedures.  The folks I work with have gotten extremely fast and good about doing this for both deployment scripts and within stored procedures.

    Thank you for your kind comments. I have always found your comments and articles very instructive and insightful. They are an invaluable aid to all developers. Please keep them coming.

    Sometimes, when surrounded by good developers we tend to rely on knowing what they can do and on them knowing what we can do and also knowing we know what they can do. It's easy to slip into a semi-lax attitude that way. There are no excuses.

    I have learnt a lot from having to write code for a variety of people with all sorts of expertise levels who I didn't know, never met or would never meet. We learn a lot from this sort of experience, when forced to think that there's no way of going back: once the code is out of your hands, there's no recourse available. This is almost as valuable as being in the company of good developers as it helps you focus and sharpen your game.

  • I prefer "implicit transaction", they are more safe than "begin tran" in case you use "GO" in the script.

    SET IMPLICIT_TRANSACTIONS ON
    ;with a
    as
    (
    SELECT * FROM dbo.account WHERE account_code='4c'
    )update a set
    active_yn =0
    output inserted.*,deleted.*

    rollback
    --commit tran
  • Carlo Romagnano wrote:

    I prefer "implicit transaction", they are more safe than "begin tran" in case you use "GO" in the script.

    SET IMPLICIT_TRANSACTIONS ON
    ;with a
    as
    (
    SELECT * FROM dbo.account WHERE account_code='4c'
    )update a set
    active_yn =0
    output inserted.*,deleted.*

    rollback
    --commit tran

    Hoo boy... This has the propensity to rival the robust rhetoric between bombastic blatherskites on bankers rounding or nulls.

    --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)

  • Hoo boy... This has the propensity to rival the robust rhetoric between bombastic blatherskites on bankers rounding or nulls.

    Well, you placed this poetic perfection like a placard to polarize the people.  XD

    Now that you've mentioned "the can", will you begin opening it by giving us the Cliff's Notes version?  I personally don't know what you're referring too.

  • OK let me in on the alliteration joke

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    OK let me in on the alliteration joke

    Oh, I just found Jeff's response to be "poetic" so I alliteratively responded to keep mine poetic too.  Don't worry, I won't quit my day job for anything artistically interpretive.  😛

  • thisisfutile wrote:

    Hoo boy... This has the propensity to rival the robust rhetoric between bombastic blatherskites on bankers rounding or nulls.

    Well, you placed this poetic perfection like a placard to polarize the people.  XD

    Now that you've mentioned "the can", will you begin opening it by giving us the Cliff's Notes version?  I personally don't know what you're referring too.

    Heh... sure... no problem...

    First of all, I'm sometimes annoyingly self entertaining to myself.  I was doing the LMAO thing the whole time I was writing that because I remember back.  Back to what?

    I've found several things in (especially) SQL Server that people are more opinionated on than normal.  I've see several long threads about NULLs and whether or not they should exist or people that believe there should be different types of NULLS or what the true meaning of NULL is.

    I've also see two record-setting discussions on how to write code that does true bankers rounding and why it should or should not be used, etc, etc, ad infinitum.

    Leading/trailing commas, leading/trailing semi-colons (especially for CTEs), how to format code, what tab settings should be set at, whether case sensitive servers are good, bad, useful, or not... all sorts of stuff where, if you get the right mix of "bombastic blatherskites" and poke them hard enough to get into a "deep" conversation on whatever the subject and a bazillion related and unrelated side supporting subjects and observations and personal opinions and experiences are, the discussion can carry on at a feverish rate with highly contentious discord between everyone involved quite literally for days and sometimes weeks on end.

    I was thinking that the use of "Implicit Transactions" could be one of those subjects where the intelligence of the conversations goes straight to hell in a heartbeat and so had to say something about it ('cuz I really was laughing that hard) hopefully without goding anyone into taking a stand one way or the other.  I was just waiting for someone to "start" and thinking "Dear lord, please don't let anyone pick up on this" and really started the LMFAO thing.  The only cure was to write a brief bit 'bout it and then run away like some kid that just lit a firecracker in a cop's gunbelt. 😀 😀 😀

    --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)

  • Jeff , you are anything but brief. keep it up

    and I have a migraine from trying to figure out that last message - yay, I get a day off - please post more (more days off) 🙂

    MVDBA

Viewing 13 posts - 61 through 73 (of 73 total)

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