Automation–the Secret to Happiness (T-SQL Tuesday #15)

For February 2011, T-SQL Tuesday is brought to us by Pat Wright (blog | @SqlAsylum).  It’s funny, I’ve talked to him on Twitter a few times, but didn’t know his real name until now.  Pat has some great photos from PASS 2010, which last November made me even more jealous that I wasn’t able to go.

Anyway, I just realized yesterday that T-SQL Tuesday was today, so this is will be a quick one. But I didn’t want to pass it up, because automation is a topic that I feel is extremely important yet tsql2sdaystill somewhat under appreciated in parts of the DBA world.

In my job history working with SQL Server, I’ve almost always been part DBA and part developer.  And every job I’ve had has looked something like this:

  • There are day-to-day repetitive traditional DBA type activities that I need to get done
  • There are existing products or processes that need improvement for stability and/or optimization
  • There are longer term projects that I would like to dedicate time to and make progress on

Over time, I try to push toward reducing the time needed for mundane things and increasing the time available for longer term things. The longer term things are always more valuable, certainly more educational, and definitely more fun.  One of the ways I try to get there is though automating repetitive processes wherever possible.

I’ve had mixed success with this though – here’s a cautionary tale from my own experience.  At a previous job there was a process that was run twice each week to create a set of data that eventually became AP payments to customers. When this was first developed, there were some serious problems with the process and with the source data, so I volunteered to step thorough it to verify and balance the results.  Because this worked so well (read – no bad checks), I was asked to do it again.  And again.  Before I knew it, this became the “Dave process.”  After the source data was cleaned up, I made some attempts to push for an automated solution, but wasn’t able to get there, partially because I was busy and partially because there were other already overworked teams that would have had to dedicate resources to the effort.

I’m embarrassed to say this went on for almost 2 years!, and it only ended when I announced I was moving on, and would therefore no longer be the Dave in the “Dave process”.  Fortunately, I was able to finally convince them that this task shouldn’t be transitioned, but really needed to be automated.  That was completed before I left, and it’s still going strong last I heard.

I often wonder how much time I would have had for other things if that process had been automated from the beginning.  I think the amount of time could easily be measured in weeks.  And that was just one process – there were many other developers / DBAs in similar situations.  I never want to get into that type of situation again.

Just yesterday, I read a good post by Denny Cherry (blog | @MrDenny) about backups that basically said, if you’re not doing database backups, you’re not doing your job. Kind of harsh, but definitely true. I think in the same way, if you’re not trying to automate your repetitive tasks, you’re not doing your job as a DBA.  Sure, you may be getting by for now, but you’re not doing the best that you can for your employer or for yourself.  And isn’t that really what it’s all about?

Thanks for reading, and happy Tuesday!

Posted in Uncategorized | 1 Comment

Naming and Renaming Database Constraints

keysI ran into an interesting problem recently, and learned something new as a result.  This problem involved naming and renaming of database constraints, specifically primary key, foreign key and default constraints.

The SQL Server database I was working with had been around for a while, and like a lot of legacy databases out there, this one wasn’t designed using any kind of consistent standards.  A lot of work had been done recently to try and clean things up:  obsolete objects had been removed; tables, views, sprocs and functions had been refactored and renamed, etc.  The next step was looking at naming the constraints consistently.

Creating constraints

Like most things in SQL, there are a few different ways to create constraints. Take primary keys for example – a PK can be created either when the table is created or it can be added to an existing table, either with or without an explicit name.  For example:

CREATE TABLE dbo.SampleTable1 (
	F1 int NOT NULL,
CREATE TABLE dbo.SampleTable2 (
	F2 int NOT NULL,

Notice that on the first table, I assigned a name for the primary key (PK_SampleTable1), but on the second table, I did not.  The PK on the second table still gets a name though, but it turns out to be really ugly one.


The name that gets assigned by SQL (PK__SampleTa__32149277145C0A3F) is a little puzzling.  It uses a PK prefix, then part of the table name, then a numeric sequence, all separated by double underscores.  Seems like they could have made a different choice on that one.  Note though, that if you create the constraint using SSMS table designer, it puts a nice constraint name in there for you (like PK_TableName).

Of course the ugliness factor is annoying, but a bigger problem is that if you drop and recreate this table, the PK will get a new name every time.  Same problem if you create the table in a different environment, or if you use a tool to do a database comparison.  Really ugly and annoying.


So, here’s the part where I learned something new.  Normally, when I use SSMS to script out a table that has a primary key, I expect to see the PK defined in as part of the table creation with its name like so…

CREATE TABLE [dbo].[SampleTable1](
	[F1] [int] NOT NULL,
	[F1] ASC

However, if you use SSMS to script the second table, it looks something like this:

CREATE TABLE [dbo].[SampleTable2](
	[F2] [int] NOT NULL,
	[F2] ASC

Clearly SQL is treating the constraint with the ugly name differently than it’s treating the one with the name I assigned.  Seems odd, and got me thinking, how does SQL know that “PK__SampleTa__32149277145C0A3F” was created by SQL and not by me?  After all, I could have chosen that name myself, right?

It turns out there’s a field in the system table sys.key_constraints called is_system_named.  It gets set to 0 when the constraint name is user assigned, and 1 when the name is system generated.  When set to 1, it tells SQL that the constraint name doesn’t hold any significance to the user, so feel free to ignore it when scripting.

Similarly, there is a field with the same name in sys.foreign_keys and sys.default_constraints, serving the same purpose for foreign keys and default constraints.

Exec sp_rename

You can rename poorly named constraints by either by dropping and recreating the constraint (which may not be feasible on large tables), or by renaming the constraint in place using the stored procedure sp_rename.  For my project, we chose to use sp_rename like so…

exec sp_rename ‘PK__SampleTa__32149277145C0A3F’,

Running this sproc as shown renames the constraint and sets the is_system_named field to 0, so when you script the table now, you get your constraint with a name in the script as expected.


If you have a lot of constraints that need to be renamed (as was the case in my project), doing them one at a time is a lot of work.  Rather than reinventing the wheel, I found a few examples online of scripts for automatically generating the exec statements to rename all of the constraints that don’t follow a particular naming convention.  The best I found was a blog post from Jason Strate (blog | @stratesql) which gives more good information about this problem.  I used a modified version of his script is near the end of this post.

Bug alert

Another way to rename constraints is through SSMS in the object explorer GUI (by right-clicking on the object and selecting “rename”).  If you choose to do that for a constraint with a system generated name, the constraint is renamed as expected, but the is_system_named field in sys.key_constraints is not set to 0.  This can be frustrating, because you think you did your job, but scripting the table gives an unnamed PK again.

When renaming through SSMS, sp_rename gets called behind the scenes to make the name change.  Using profiler, I found that the sp_rename call is different however, it’s using ‘INDEX’ instead of ‘OBJECT’ as the third parameter.

Profiler screen shot

Apparently, when ‘INDEX’ is passed, the is_system_named field is not reset.  There is a connect item for this originally submitted by John Bell, and I added a comment to clarify it a bit.

Final Thoughts

  • My advice is to use a consistent naming convention for your constraints, and don’t allow SQL to assign a constraint name for you.
  • If you need to rename existing constraints, use sp_rename with the ‘OBJECT’ parameter and not SSMS.
  • Here are the standard naming conventions I use.  From my experience, these or very similar conventions are pretty widely used.

Primary Key – PK_TableName
Foreign Key – FK_ChildTableName_ParentTableName (optionally add field names if there are multiple FKs between a child and a parent)
Default – DF_TableName_FieldName

Note:  add the schema name before the table name for each of these where the schema is not dbo.

Posted in Uncategorized | 3 Comments

Resolutions 2011 (T-SQL Tuesday #14)

Welcome to my first T-SQL Tuesday blog post!  This month T-SQL Tuesday is presented by new Microsoft MVP Jen McCown (Blog | @MidnightDBA) of MidnightDBA fame.  I’ve been a fan of the MidnightDBA web show for while, so this is pretty cool.

The topic this month is Resolutions.  Jen asked the question, “What techie resolutions have you been pondering, and why?”  Looking through this post after I wrote it, I realized these aren’t strictly “techie” resolutions, so maybe I’m bending the rules a bit, but so be it!

NYE Resolutions

This past New Year’s Eve I was at a small party with a few friends, and someone suggested we go around the room and share our resolutions for 2011. As we did, there were the usual ones you would expect to hear about losing weight, working out, relaxing and enjoying life more, etc. I think all of those are excellent, and they all are on my list as well, but the two I mentioned were a little different.

1.  The first one was to start a blog. And here it is, mission accomplished!

Well actually, there were two parts to the resolution, a) start a blog and b) keep posting throughout the year so at the end I have at least 2 posts/month for a total of at least 24 new posts in 2011. I’m enjoying it so far and have some more stuff in the works, so I think that’s doable.  Hopefully I’ll go well beyond that.  I was even thinking about upping my goal, but it was made officially on New Year’s Eve, so I really can’t.

2.  My other resolution was to get more sleep. I know that goes against the grain for almost all DBAs I know and most IT Pros in general, but I think I need it.  In 2010, I didn’t get a lot of sleep, and it affected my work and my overall quality of life.

Granted I had at least one good excuse with a new baby in the house starting in February. (By the way, if you’ve never been up late doing a promotion, finally falling into bed, head hitting the pillow only to hear a baby start screaming… you’re missing out.)  But she’s been sleeping through the night for several months now (thank God). and the world is a happier place.

The thing is, if I’m on something exciting at work, where I’m learning and fully engaged, then getting less sleep really isn’t a problem. It’s when I’m doing something less exciting and more tedious, like estimating hours for tasks, or filling in holes in documentation, then the fatigue starts to set in…

Anyway, I get up at 5:15 on weekdays for work, so going to bed at 10:15 would give me 7 hours of sleep. I’ve stuck pretty close to that so far in 2011, and I am honestly feeling a lot better.

One More

So those were my two resolutions for the new year, but in honor of this TS2 event, I’ll add another one…

3.  I want to work smarter in 2011. I started some of these things last year and want to continue to improve on them as this year goes forward

  • Continue to automate any and all processes that are automatable. Get away from the manual stuff as much as I can.
  • Be more organized. I made a rule for myself a while back to save everything I write so I wouldn’t have to redo anything. That’s great, but it can easily get out of control. If I spend 10 minutes looking for something that took 10 minutes to write and would probably take 5 minute to rewrite, is it worth it? To solve that I’ve tried to keep things more organized by:
    • Using a consistent file and folder naming convention
    • Adding descriptive comments with searchable keywords
    • Using Dropbox for organizing files, documents and scripts, and to make them available to me anywhere.
  • Keep a realistic number of projects going at the same time. Prioritize them correctly according to business need. Spend some time on all of my projects every day, with the most time spent on the highest priority ones. This keeps the lower priority ones from falling by the wayside, and reduces the amount of time it takes me to get back into it when that project becomes a higher priority. If I can’t spend any time on a project for a few days, question if it should move to someone else, or if it should be a project at all.

Final thoughts

I think that’s enough for now. I’m not sure if these are resolutions or goals (is there a difference?), but either way, I plan to review them throughout the year. I’ll do an update at the end of 2011 with how I did. Maybe one at 6 months as well – 2012 seems like a long way off right now.

Happy New Year!

Posted in Uncategorized | 1 Comment

Renaming MCM Video Files with PowerShell

Windows_PowerShell_icon-102x102This should be a quick post, but it might help someone with an problem I was running into.  Note that I’m just starting to learn PowerShell, so take that into account when reading…

Lately, I’ve been using Aaron Nelson’s (blog | @sqlvariant) awesome script to download all of the SQL Server MCM (Microsoft Certified Master) training videos automagically.  I updated his script to grab the .MP4 versions of the files instead of the .WMVs since I’m watching them on my iPhone.

When the script pulls the files from the MCM site, they are named with a ridiculously long prefix, like this:  HDI-ITPro-TechNet-ipod-MCM_01_DataStructures.mp4.  That’s all well and good, except when I put them on my iPhone, it ends up looking like this:


The names don’t scroll, and unlike with music, turning the phone to landscape doesn’t show anything differently.  Since I can only see the prefix, I’m left to guess which video I’ll be watching.  As fun as that is, I would rather be able to tell clearly which video is which.

In comes a very short PowerShell script.  I use this script to strip the long prefix from all of the MCM video files in my videos folder (actually, calling it a “script” is probably an exaggeration).

get-childitem *.mp4 | foreach { rename-item $_ $_.Name.Replace("HDI-ITPro-TechNet-ipod-MCM_","") }

Sync with the phone and, ta-da…


Ahh, much better.  Aaron’s script is smart enough to only download the video files you don’t already have.  So when new videos were released this morning, I ran the following to put the filenames back the way they were before running his script again.

get-childitem *.mp4 | foreach { rename-item $_ $_.Name.Replace($_.Name,"HDI-ITPro-TechNet-ipod-MCM_" + $_.Name) }

Simple but useful.  I love PowerShell because it’s so intuitive – it’s easy to look at these snippets of code and tell exactly what it’s doing.

If anyone knows of a better way to handle this, either in the script or on the iPhone, please leave a comment.  Thanks!

And by the way, if you haven’t heard, the MCM videos are awesome.  I think they are worth watching by any SQL Server professional, even if they currently have no interest in pursuing an MCM certification.  The MCM video site is here, and supplemental information on the SQLSkills site is here.

Posted in Uncategorized | 2 Comments

My Blogging Manifesto

blogI’ve been thinking about starting a blog for several years, ever since a friend and coworker of mine suggested it.  As a New Year’s resolution for 2011, I’ve decided to stop thinking about it and start doing it.  So here it is!  I’ll be writing mostly about SQL Server and BI related topics, probably with occasional diversions here and there.

The next logical question might be, why?  Why start a blog?  There has to be a better reason than “everybody’s doing it.”  Below is a quick list I came up with, in no particular order:

  • My wife thinks it cements my position as a total geek, and I’m all for anything that will get me closer to that goal 🙂
  • I’ve had a lot of great encouragement from peers and coworkers, which I really appreciate.
  • I was watching a Midnight DBA show recently and Sean (Blog@MidnightDBA) talked about how he references his blog all the time (I know, right?). Since then, I’ve heard several other people say the same thing, and it makes a lot of sense to me.
  • I don’t consider myself a very strong writer (you probably don’t either, if you’ve read this far), but I don’t know of any way to get better at it than to just do it.
  • I’ve found that teaching something is the only way to really burn it into my memory. I learn new things everyday, but so many of those things just fly away when my mind moves on to other things. This can be especially true with the highly technical stuff.
  • Over the last few months, I’ve come to really appreciate the SQL community.  It’s truly an awesome group of individuals who seem to really want each other to succeed.  I feel like I’ve been taking from the community for so long, mainly through reading and referencing other people’s blogs, and now I’m excited about the possibility of giving something back.
  • Speaking of other people’s blogs… I remember back when I started doing development and working with SQL Server, I had BOL installed locally and updated it from the MSDN Library DVDs every quarter.  That seems so backwards now.  I still think BOL is a great resource, one of the best, but the majority of help I find these days is through doing a Google search and finding the answer on someone’s blog.  Hopefully posts on this blog will help someone else in the same way.

So here we go.  What will it lead to?  Who knows… It seems like the natural progression is, people start blogging, then volunteering, then presenting.  I’m not sure if I’m ready to look that far ahead, but for now, blogging is a good start.

Thanks for reading!

Thanks also to Brent Ozar (Blog@BrentO), and his starting a technical blog series. I highly recommend it to anyone considering this type of thing.

Posted in Uncategorized | 6 Comments