Posted in SQL Server

#TIL About Foreign Key Scripts

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?

Continue reading “#TIL About Foreign Key Scripts”

Advertisements
Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #110 – Automate all the things

TSQL2SDAY-150x150I’m getting the new year started by joining in the T-SQL Tuesday party. Thanks to Garry Bargsley (b | t) for hosting this month’s topic: Automate All the Things.

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.

 

 

Posted in Apropos of nothing

Reflecting on reflecting

Because even when you’re practically perfect in every way, your reflection needs to take a look back at you too…

Yep – it’s that time of year again where we all start reflecting and seeing how we measure up. Well, I’d say it’s that time of year again, except lately I feel that’s all the time. It’s like we’re in a constant state of reflecting because there are so many times that lend themselves to this. The end of the calendar year. Annual reviews. Some anniversary, real or made up like a “blogiversary”. Birthdays. The Jewish New Year for some of us. (My birthday is around that time of year so it’s like I get two for the price of one!) I’m beginning to feel like if I do any more reflecting, I’ll have to start calling myself Narcissus and stay away from all bodies of water.

Continue reading “Reflecting on reflecting”

Posted in data modeling, SQL Server

How I Really Feel about Surrogate Primary Keys

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?

soapbox

Continue reading “How I Really Feel about Surrogate Primary Keys”

Posted in SQL Saturday

SQL Saturday Boston #797 Reflections

Now that SQL Saturday Boston is over (along with the rest of my insane September), I’m finally able to reflect on the whole event.

my speaker badge
My badge and its ribbons

My first SQL Saturday Boston was as an attendee when it was held out at one of the colleges in Wellesley, MA (either Wellesley College or Regis College – I don’t remember which one.) I missed one or two of the SQL Saturdays after that because I was late registering and SQL Saturday Boston fills up quickly. I managed to volunteer at SQL Saturday #500 helping in the morning at the speaker and sponsor check-in table. I had spoken at my first SQL Saturday before the SQL Saturday Boston BI 2017 but I didn’t submit my T-SQL 101 session because it was a BI Edition SQL Saturday and I didn’t think it met the criteria for a BI session.

This year, I had the honor to be on the organizing committee. It was an interesting experience to see the same event from so many different perspectives.

Continue reading “SQL Saturday Boston #797 Reflections”

Posted in SQL Server, T-SQL Tuesday

T-SQL Tuesday #105 – Brick Walls

TSQL2SDAY-150x150It’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?

Continue reading “T-SQL Tuesday #105 – Brick Walls”

Posted in data modeling, SQL Server

Designing Super Types of Tables

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?

Continue reading “Designing Super Types of Tables”