Not sure how this happened but there is a lot on my speaking calendar for the month of March. Enough to fill an entire blog post!
No matter how long you work with something, you can always find something that you never knew before. I found one about foreign keys this week.
I was reviewing SQL scripts for coworkers and I noticed that the foreign keys were written without referencing the parent table’s column. But the script didn’t fail and created the foreign keys correctly. So how did this work?
What stuck out most to me is when Garry asked the question:
What does “Automate All the Things” mean to you?
It’s not the real focus of what he’s asking us to talk about it, but I think it’s important to talk about. When I think of automation, I think of setting up routines that need to occur on a regular schedule. This means that you would automate system checks, backups, maintenance, etc. that run every night. In some ways, I see automation as a tool that helps with mainly administrative tasks.
Well I don’t work on the administrative side of the house. I work in development so I don’t have to deal with the day-to-day maintenance work that benefits from automation. Now, I do see automation used in other places in development – for example, unit tests that run every time a build is kicked off, QA automation tests, etc. I know database work can have unit tests but it’s somehow hasn’t filter down to the way that I have worked. (Yet.)
However, I have started to recognize when I have to do a series of steps over and over for a reason – usually involving resetting my environment for testing one thing or another. I’ve started collecting the small pieces together and started creating mini scripts so I can do these steps again. And I’ve started modifying them so I can make them work in any situation. For example, I need to move data between two databases but the database names may be different so I added a parameter and made the statements dynamic so I just need to supply the right database names. Or I created a simple PowerShell that has the list of commands to run to do those few steps instead of me doing them separately each time. It’s not always a lot of steps but this way I don’t accidentally mistype something or put something in the wrong place.
In many ways, this is part of setting up automation – identifying the repeatable actions and creating a mechanism to repeat those steps in a reliable way so I know every time I run the script, each action will work correctly. While I’m not scheduling them to run at a certain time – whether it’s a calendar or from some other action, it’s laying the foundation so that if I – or my coworkers – could ever automate these actions, the basic set of scripts is ready to go.
Most of my database work is still done using T-SQL scripts. Call me old-fashioned but I like using these scripts because I can use them directly in a query window in Management Studio or I can call them from PowerShell and I know I’m doing the same exact actions regardless of how I execute the script. I’ve started to use PowerShell more to run these scripts. I still feel like I’m learning PowerShell, but I’m definitely getting more comfortable and improving my skills. None of this may be the cutting edge technology but it gets the job done.
One day, I’ll probably be able to implement what I consider to be true automation. Who knows – maybe someone else will consider what I’m doing “true automation.” But until I can really automate these actions, being able to set things up so that will be possible will have to do.
When someone goes on a rant about something they truly feel strongly about – good or bad, my typical joking response is “You’re being a little wishy-washy about this. Tell me how you really feel.” I recently had a conversation with co-workers about surrogate primary keys and realized I need to use that line on myself.
So let me tell you how I really feel about surrogate primary keys. Where’s my soapbox?
It’s another T-SQL Tuesday. Thanks to Wayne Sheffield (b|t) for hosting this month. His T-SQL Tuesday challenge is to write about Brick Walls we have faced. (If you are unfamiliar with the T-SQL Tuesday party, check out the website for the full backstory.)
It took me a bit to figure out what to write about for this topic. But that’s not the type of brick wall I want to post about today.
The Brick Wall:
I was working with a highly transactional system. The table with the most writes was also the table with the most reads. To add to the problem, the data being updated the most was the same data we need to read the most. The next set of popular tables, i.e. the next most read and updated tables, had triggers that used the most popular table for some of the data needed. As you can guess, we had a lot of deadlocks in our system. Oh, did I mention that this was running SQL Server 2000?
I want to talk a little about database design patterns. When working with a relational database, there are a couple of patterns that exist to help you normalize your data. I think one of the most useful patterns in this is the supertype-subtype relationship.
You don’t see a supertype-subtype relationship defined as such when you’re looking at the physical database. You’ll only see it explicitly in the logical data model. So what is the pattern and how do you know that you have one in your database?
Happy T-SQL Tuesday #102! The challenge for this month is from Riley Major (b|t). His challenge is to think about how we can give back to the community. (Check out his blog invite or the T-SQL Tuesday website to find out more details about this monthly blog party.)
The list of suggestions that Riley put together is quite an impressive one. It’s a great reminder that there are so many ways to get involved in the community.
I’ve started doing several things on the list over the past year – speaking, taking on the T-SQL Tuesday challenges, getting involved with the local user group – so I’m definitely going to commit myself to doing more of that. Maybe I can “up my game” and submit to a SQL Saturday in a city that I have to fly to or even submit a session to present for a virtual user group or a bigger event like GroupBy or 24 Hours of PASS.
At my new job, I’ve been told there are opportunities to do “lunch and learns” and I’ve been told to encourage my co-workers to join me at the local user group meetings. It’s kind of a no-brainer to do those.
But of all the things on the list, I definitely would love to find a way to pay forward the support I’ve gotten from members of the community to others who need it. A few people have come to me asking for advice or to be sounding board. As happy as I am to help out, I still feel like there’s more that I could do in this area. One concrete thing I think I can do is volunteer to help the first timers at PASS Summit as part of Buddy Program, if they continue that program again this year. But I’m going to keep looking to find other opportunities to help support others as they need it and in general feel like I’m being a better citizen of our #sqlfamily. I know they’re out there, but I just need to find the way that I can contribute.
As I was putting this list together, I realized that these examples are little things that I can do. And that’s OK that they’re little things. I think that’s important thing to note for those who are thinking about how they can start to give back to the community. One of the great things that Riley’s original list proves is that not every way to give back to the community requires big gestures. If you’re not someone who’s comfortable stepping into the limelight or jumping in with both feet (or whatever other catchphrase fits), there are still a lot of behind the scenes ways to contribute. There is a place in this community for that special passion you have and this community wants you to share with us.
Thanks to Riley for hosting this month’s topic! And thanks to all those who are currently doing so much to support our community and those are going to take on the challenge to step up their involvement! And if there is something I can do to help out, please let me know….