Voice of the DBA

Category: Editorial

  • Choosing Sequences Over Identity

    When I was building software as a full time job, the choices for automatically generating surrogate keys were the identity property and the NEWID() function. At that time, GUIDs were too cumbersome to deal with, and I often used identity values in tables. I looked for natural keys, but often these were secondary keys for me and I preferred to use numerical values in many tables as PKs and FKs.

    In the last few years I’ve run into a few customers that prefer sequences, which are a separate object in your SQL Server database. There are some challenges with sharded systems and using deployment technologies like SQL Compare, which is one reason I’ve avoided them. However, I was reading Dr. Greg Low’s blog recently where he noted that he prefers sequences to identities. He gives a good comparison of the reasons why sequences can be easier to manipulate, with some advantages because these are a separate object. I think he makes a good case why we might always want to consider sequences over identity values in new development.

    He’s convinced me, though now I need to learn a new habit and build new skills to quickly and easily develop sequences for table keys instead of relying on identity values. This is a big change for me, with nearly 30 years of writing identity properties in CREATE TABLE statements.

    Learning to adopt new techniques and changing habits of the ways that we grow and change, and certainly how we build better software. That’s a tenet of DevOps. Experiment and learn. This is a place where I’ll start to grow and see what I think as I build demos and PoCs for customers, giving sequences a chance. I wonder how many of you might rethink using identities in the future as well.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • May the Force Be With You

    I’ve never done much more than give a passing smile when I see all the posts and notes about May 4th from geeks around the world. I was a huge Star Wars fan as a child, and the original is still probably the movie I’ve seen more times than any other. Life has moved on, and while I haven’t been a s big a fan as an adult, being shut in at home has me thinking about Star Wars today, and the good luck saying, “May the force be with you. Always.”

    I loved the original trilogy growing up, was disappointed, but slogged through the prequels with my oldest. I watched the Force Awakens in Cape Canaveral with my middle son in the theater after seeing SpaceX launch and successfully recover their first reusable rocket. We were both a little disappointed in that one, not from the effects and action, but more from the writing, which seemed to drag on.

    Since that time, I haven’t seen any of the other films in the theater. In fact, I’ve watched most of them on an airplane, traveling from one place to another. Since I’m not traveling now, I’m thinking to actually go re-watch some of these films on a larger screen, with better lighting. I’m hoping some immersion in a series of films will take my mind away from some of the stress and strangeness of the real world.

    I’m sure some of you are big fans to this day, some may not have seen all (or any) of the films. Today, as we start another work week, I’m taking a little break from the technical world I’ll spend most of the day in and looking at a fantastic, highly technologically advanced, geeky Star Wars universe. Do you have favorite films or technology? Any films or characters that stand out to you? I have to admit I was always a big fan of the land speeder, though the pod racing in The Phantom Menace was exiting to me.

    On this Star Wars day, with a world we might want to forget, share some Star Wars memories today.

    Steve Jones

     

  • Are You Ready To Change Your Work Environment?

    It’s Friday, it’s been a long few months, and I’m wondering what you think of work now that many of us are telecommuting. This week, I’m wondering if you want to answer one of these questions?

    • If you are telecommuting full time for the first time, do you want to continue doing so?
    • If you are telecommuting full time for the first time, are you ready to go back to an office?
    • If you’ve been working from home for some time, are you ready to go back to an office with people around?
    • Do you just need a variety in your working environment?

    These are questions I’ve asked myself many times across the last few decades. My thoughts and answers have changed a bit over the years, and even now they’re different than they used to be a few months ago. Right now, I want to go back to an office more often than I used to go. I usually go to the Redgate Cambridge office four times a year, and the US offices rarely. I think after this, I’d like to get to each office in the US a couple times a year and four times in Cambridge. I miss talking to live co-workers. I might feel differently when this shut in ends, but I miss having some people around.

    For almost ten years, I drove to an office every day while my wife worked at home. I was jealous, and wanted to telecommute full time, but none of my management would allow it. Then for almost another ten years I worked at home with my wife. She traveled quite often, and I struggled with the loneliness of being by myself. I often went to lunches with friends or sat in a Starbucks and worked, just to have some other people around. The third phase of my work environment has me at home alone often, but also on the road regularly, so I haven’t struggled with the times I’ve been alone in my office for weeks.

    I don’t know how things will move forward from here. I’m sure something will change, and I look forward to societies finding ways to open things up over time. I know we’ll adapt and cope, but I also know that even the most introverted of us is a social creature and we do need some contact with others. Today, I wonder if your views of how we want to work have changed.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Keeping Unique Aliases

    I am a big standards guy. Not any particular standards, but ensuring you have some in your organization and that people stick to them. Pascal case, Camel case, it doesn’t matter to me. I’ll build a habit with any of them; I just want some consistency from others working in a team. Or even across teams since people move to different projects over time.

    I ran across another post on naming conventions, this one looking at SQL standards, and it’s a practical look at a few things. I like some of the ideas, like the singular or plural choice (” Who cares. Just pick one and use it consistently. “), but I especially like the idea of the third item: standard aliasing.

    In the past, I’ve often found that I used the same aliases over and over. When I was the only DBA, this ensured consistency. “P” was always for dbo.Product, and pc was always used for ProductCategory. However, when we added other team members, I quickly realized we didn’t use the same conventions. I’d check out code and see prod.productid instead of p.productid. The new people didn’t like my single character aliases and I didn’t like typing most of the word for theirs.

    Ultimately we had to decide what made sense for both of us and compromise. We choose specific items, not all at once, but as needed. We’d have a 30s meeting or exchange a quick chat message and then update a document with the standards. When other new developers were hired, they already had a reference list that they quickly learned.

    I think the idea of standard aliases makes sense. It’s built into SQL Prompt and some other tools, precisely because many teams want to ensure their developers adhere to a standard, and more importantly, can quickly understand any code they pick up. That’s the real reason for standards: they convey information, silently and quickly.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.