Voice of the DBA

Category: Blog

  • A New Use Case for SQL Prompt – Shrinking Code

    I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool.  SQL Prompt will be yours as well if you give it a try.

    I thought this would work, but I wasn’t sure. I saw some code the other day like this:

    DECLARE @char AS CHAR(1); SET @char = NULL; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0); SET @char = ‘E‘; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0);

    I dropped it into SSMS, and of course, I couldn’t read much of it. I did a quick CTRL+K, CTRL+Y and it looked like this:

    DECLARE @char AS CHAR(1); SET @char = NULL; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0); SET @char = ‘E‘; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0);

    Yeah, SQL Prompt. In addition to reformatting as I prefer, it removed the extra whitespace.

  • In and Out

    Travel always causes issues with work. It was the same when I was a production DBA as it is now as editor and evangelist for Red Gate. The tasks I have to manage and complete don’t seem to ever be scheduled with the idea that I might be away from my desk.

    I was off Monday with holiday and family celebrations (and the last ski day of the ’14-’15 season). I also leave Thur morning for SQL Saturday in Huntington Beach and will be gone until the weekend.

    As an editor of what’s essentially a newspaper, the issues must go out regularly. That means that last week before I took time away I had to ensure that I had newsletters scheduled for Fri-Tue, as well as working on content for this week. That compresses the schedule of tasks, with more being done before I leave. Today I need to catch up things that occurred while I was gone, as well as prep for tomorrow and Thursday. I’ll also need to get enough done that I have things scheduled for Friday and Monday.

    Time management can be hard, and over the last 10 years I’ve learned a few tricks to ensure I can handle the variety of tasks that come my way. What has worked for me is to take some of the lessons from the Boy Scouts and apply them to work.

    Be Prepared

    For those items I need regularly, I have learned to look ahead and always have a couple items prepared. This mainly means questions, articles, and editorials. Since I’m not sure what will happen in a week or two, my plan is always to hold a few items back and ensure I have at least a week’s worth of items planned.

    Like my son works on merit badges, the other thing is to regularly work on items I need rather than wait until I need them. My son often completes a work requirement before he needs it. I may have to remind him, but he’s learning to adopt that on his own. I do the same thing at work, writing, editing, soliciting content before I have the actual need.

    Vacation Isn’t Free

    Perhaps it’s an American thing, but it seems that vacation is rarely a free gift of time away from work. Even when I worked at a job that was 9 to 5 and few hard deadlines, I still found the need to prep systems or people for my time away.

    In addition to the daily work.

    There are others on staff at SQLServerCentral that help me out and I really appreciate the time, but there still is an additional load for me to prepare to be away. It helps to tackle this like saving for vacation (or the future).

    A little at a time. On a regular basis.

  • New Blogger Challenge 1 – Adding a Primary Key

    The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

    Primary Keys

    I firmly believe that every table should have a primary key. At least until you have a reason not to have one. If you have a reason, fine, but if you can’t explain it or convince me, then just add a primary key.

    I have tended to build tables like this:

    CREATE TABLE Users
(
  MyID int IDENTITY(1, 1)
, firstname varchar(250)
, lastname varchar(250)
, gender char(1)
, postalcode varchar(12)
, contactphone varchar(12)
);
GO
ALTER TABLE Users ADD PRIMARY KEY (MyID);

    Lately I’ve not liked that as my primary key now has a name like [PK__Users__7131A74146D2BBC1]. I’d rather have a more organized database with a touch more effort.

    The better way to add the key later is like this:

    ALTER TABLE dbo.Users 
  ADD CONSTRAINT pkUsers PRIMARY KEY (MyID);

    This way I can name the key, and I specifically note this is a constraint, and with the PRIMARY KEY option, it’s a unique constraint.

    References

    A few places I searched around to double check myself.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

    Look for the other posts in the April challenge.

  • Closing Out the Winter Holiday Season

    I’ve tried to write regularly about taking vacation and getting away from work to recharge. I haven’t always done a good job managing that in my life, but I’m hoping to inspire more of you to do better.

    I’ve scheduled holiday for Friday and Monday over Easter weekend to take one last ski weekend for the 2014/2015 winter season. I’ve got family coming into town and we’ll have the chance to enjoy a little time as ski season comes to a close.

    It’s been a decent, but not great ski season for me. I didn’t get as many days scheduled, or taken, as I’d have liked with a busy work and family life, but I can’t complain. Life has been good and I’m on a better pace to use up vacation this year than last.