Voice of the DBA

Author: way0utwest

  • Altering a column to add NOT NULL

    I had to do this recently and needed to look up the syntax as I couldn’t remember it. I don’t do this often, but I was working on a particular item and wanted to test this.

    I had a table, something like this:

    CREATE TABLE SimpleTable
( MyID INT NOT NULL PRIMARY KEY
, MyChar VARCHAR(200)
, [Status] TINYINT
);

    I decide that the [Status] column shouldn’t be NULL and decide to ensure it’s always got a value. I don’t want to rebuild the table, I want to ALTER it. I use this syntax:

    ALTER TABLE dbo.SimpleTable
 ALTER COLUMN Status TINYINT NOT NULL;

    That’s it. I do this and the column now no longer accepts NULL values.

    Of course, if there’s data in there, I need to set those values to something that isn’t NULL before I can do this, but that’s a separate topic.

    By the way, I remembered most of this syntax. What I forgot is that I need to include the datatype in there to make this change. There are a number of restrictions with this command, so be sure that you read carefully before you try to make changes. If you do this in SSMS, you might end up with a script for a new table, which may not be what you want to occur in a production environment.

    Reference

    ALTER TABLE – https://msdn.microsoft.com/en-us/library/ms190273.aspx

  • Dig Out the Root Cause

    Early in my career in technology, I worked as a system administrator on a Novell network. Periodically we’d have crashes of servers on our network, or application failures, and while we understood that sometimes these were caused by transient errors, we often invoked a root cause analysis process when an issue repeated itself. I’m sure that the environment in which we worked, a nuclear power plant, contributed to the idea that we should always understand why some system failed.

    I was reminded of this while reading Grant Fritchey’s “Understand the True Source of Problems” in which he relates a few “best practices” that are likely folklore stemming from ignorance rather than actual good ideas. I’ve seen a few of these rules at various places in the past, often implemented because they appeared to work. However, we need to remember this:

    Correlation != causation

    Just because you perform some action and observe an effect doesn’t mean that one caused the other. If that were the case, I’d never install new hardware or applications on my computer systems as I’ve had crashes during various installations. However I often back out a change, or retry it and realize that I had a coincidental result, rather than a related one. The same thing has occurred in more complex systems where an action appears to cause an issue, but in reality, the two items are unrelated, or loosely related.

    We often don’t take the time to determine the root cause of many issues, which is disappointing. While it often doesn’t seem to be a worthwhile use of resources, I bet that often we’d learn there are actions we are taking (or code we’ve written) that actually is the cause. If we learned from our mistakes and could avoid making the same ones again, we’d greatly improve the quality of our technology systems, with many fewer issues over years.

    Unfortunately, “good enough” is often good enough, even if it does result in a bit of downtime.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • The May Blogger Challenge–Continuing On

    Last month I participated in Ed Leighton-Dick’s April Challenge. My four posts were tagged the April Challenge, and this month I plan to continue, and hopefully get a few more people to up their blogging.

    Building on April

    To make the next step in starting to blog regularly, I’d like to see people in May continue with their weekly blogging, but do one more thing: write one additional post.

    This additional post I’d like to be one of the T-SQL Tuesday topics. Pick one from the list of past choices and write a post. Feel free to title it with the T-SQL Tuesday number and post it. It really doesn’t matter when you wrote it. The whole idea is to just write.

    Getting Started in May

    If you didn’t participate, or you struggled to get 4 posts, get started in May. Write one post per week and publish it. You can tag it the #SQLNewBlogger or you can just send a note to myself or Ed. We’d like to know what you’re doing.

    If you struggle to write, do one of these two things:

    • Make a note, send yourself an email, whatever, when you close a ticket or complete a section of code.
    • Use one of my topics

    Either one of these things work. I’d prefer you do the first one and just make a note when you do something. I’m sure you do something at work. Maybe it’s check logs, maybe it’s solve a problem with an instance, maybe it’s write a report. Anytime you do something, make a note.

    Then write about that note in a weekly session.

    If you are still struggling for topics, here are a few that I’d like to see you tackle in May:

    • Where do you use a unique index?
    • Generate a script after adding a column in the SSMS table designer
    • Query for the list of recent full backups in msdb for a single database
    • Show how to join two tables.

    Pick one and write. Set aside a 15 minute session a week to write, and work on a short, < 1 page, post.

    Good luck, and hope to see you blogging in May.

  • Microsoft Exercise

    Have we got a deal for you! Microsoft is concerned about the health of your company and is doing something about it.

    There’s a trend in modern society of obesity, complacency, and general laziness. Not only does is this bad for society in general, but it also affects your health. So we have a new solution for you, designed specifically to combat this degenerative lifestyle in an easy way.

    The Upgrade Treadmill!

    Designed by the engineers up in Redmond, this highly engineered device ensures that you won’t sit around getting stagnant on those junky-technologies and deep-fried solutions. Instead we’ve built a system of carefully selected products released on a regular cycle that ensures you’ll be running for the rest of your career.

    And it’s easy to get started. Just call 1-800-SHARE-PRICE and enroll now in our assurance program. With a short lifetime and quick expiration, our automated reminders to your boss will ensure that you never slack off and slow down your pace of upgrades.

    It’s all in fun, but there’s some seriousness here. I originally wrote this awhile back when it seemed Microsoft was pressing for upgrades to SQL Server 2008 R2 after a relatively short development cycle post SQL Server 2008. However the piece got lost in the shuffle. I found it recently and was reminded of the sentiment when I saw a post to upgrade away from SQL Server 2005 with support ending.

    There are good reasons to upgrade; just be sure the reasons are valid for your instance and environment.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.8MB) podcast or subscribe to the feed at iTunes and LibSyn.