November 4, 2019 at 9:24 am
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
November 4, 2019 at 10:35 am
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.
November 4, 2019 at 11:35 am
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
November 4, 2019 at 11:42 am
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?
November 4, 2019 at 1:47 pm
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.
November 8, 2019 at 7:10 pm
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.
November 24, 2019 at 5:34 pm
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
November 25, 2019 at 2:30 am
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
Change is inevitable... Change for the better is not.
November 25, 2019 at 5:45 pm
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.
November 26, 2019 at 8:40 am
OK let me in on the alliteration joke
MVDBA
November 27, 2019 at 11:10 pm
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
Change is inevitable... Change for the better is not.
November 28, 2019 at 8:50 am
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