Voice of the DBA

Author: way0utwest

  • April Blogging Challenge 2 – Primary Key in CREATE TABLE

    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.

    Designating a Primary Key at Design Time

    In the first post on this subject, I noted that I often add a primary key after the fact, using ALTER TABLE. However I’ve been wanting to build the habit of adding the PK at design time, inline with the CREATE TABLE statement, so I decided to look up the syntax and start practicing.

    The inline code adds a CONSTRAINT clause into the table definition. I can do this in two ways, the most common is after I’ve built all the columns, I add CONSTRAINT, a name (PKUsers) then the PRIMARY KEY keyword with the optional clustered/nonclustered designation, and finally the column(s) in parentheses

    Here’s my new code:

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

    The better way, IMHO, is to do this inline if you have one column. I know this isn’t consistent, but I can easily see the constraint this way.

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

    This is more difficult to see if you have multiple columns, but you can do this:

    USE sandbox; GO CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO DROP TABLE dbo.Users; GO

    Simple and easy to find the PK now, it’s named correctly, and it’s easy to read.

    References

    A few places I used

    Quick and Easy Blogging

    This post occurred to me while I was writing my first post.  Actually, this occurred to me first, but I realized that I often do the ALTER method in post 1, so I wrote that first.

    Changing to this post required using the same MSDN article, dropping the table, rewriting the DDL code, and testing it. About 5 minutes for this one because it was based on a previous post.

    Look for the other posts in the April challenge.

  • State v Migrations

    Most people work in evolutionary databases. By that I mean a database where you have some schema, and over time you are altering that schema. You might be adding columns to tables or views, changing stored procedure or function code, or something else, but you aren’t really rebuilding the database from scratch on a regular basis. Certainly some people sell software and a database that’s always being rebuild and upgraded from state X to state Y, but that’s a much more complex issue.

    For those of us that work with these evolving databases, we really have two choices in how to work on upgrades. We can store each change to the database as a script and ensure we run the correct scripts in the correct order (discarding those that aren’t needed) when we deploy changes. I’ve done that before, and it can work, but this approach requires I’ve have good control of production to prevent changes from being made in that environment that aren’t also made in development. This is the approach advocated by Paul Stovell, of Octopus Deploy.

    The other approach is to look at the state of development at some point in time, compare that to production (with a tool like SQL Compare) and then generate a script that makes the changes needed. This is how lots of people deploy their changes today, though this approach isn’t without its own issues. NOT NULL columns, renames, and more can cause problems with this approach. There are ways around these issues, but they require some work.

    Ultimately the problem of smoothly deploying changes to databases requires a bit of discipline from the DBAs and developers. Tools can help, and they certainly can reduce the work involved, but good habits and a consistent process are important to ensure that changes are made smoothly. The one thing that helps you find problems with your process and code is testing, which is something I’d recommend you implement no matter what method you choose for deploying your changes.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Release Wednesday

    I’m glad that someone else is adopting Release Wednesdays. I was surprised to see the post from Red Gate, though I shouldn’t have been. I knew we were releasing regularly and some groups were aiming for weekly releases. There was easily a 1 in 7 chance of them choosing Wednesdays, which has a special meaning for me.

    I worked at a startup company where we released every Wednesday. We worked in one week increments and developed an application across a couple years that evolved and morphed to meet differing business needs in the education market. We went from working with digital sales, to including physical products, to subscriptions. Our customers went from individuals to corporations, to departments in corporations, and all along the way we managed front end and database changes in one (or sometimes two) week increments.

    We didn’t practice Agile or Scrum or anything formal. We worked as closely as 10 people could, coordinating and communicating to get features implemented every week. We started releasing on Wednesday nights with the lead developer and myself staying late at the office Wednesday nights (usually with another developer or two for company) for an hour. That was reduced to phone calls where we deployed from home (and fixed problems) in real time. Eventually we built such a well engineered process, with thorough testing, that our deployments were completed in minutes with a few chat messages to verify we’d both finished our tasks.

    We did that for over 18 months with only one or two rollbacks, and without the all night frantic coding sessions to fix issues that I’d experienced elsewhere. It wasn’t simple, but it wasn’t hard. It required preparation, planning, a strong culture of professionalism, and support from our CTO.

    I see the same types of culture at Red Gate, and I’m not surprised by the rapid releases, nor the success we’re having in evolving, updating, and patching our products over time. I’m excited to see how much quicker we can move and how we will continue to build better software, faster. 

    One Wednesday at a time.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.