Voice of the DBA

Category: Editorial

  • The Tally Table

    I saw a someone post a question recently about solving a T-SQL problem. One of the answers given used a tally table, which the original poster didn’t understand. A few follow up links pointed them in the right direction, but it got me wondering.

    How many of you know what a tally table is and how to create one? I bet a few of you don’t, which might mean you’ve never had the need. Or it might be a hole in your skill set and you didn’t realize that a tally table can be very useful in solving a number of problems. Anything from generating dates to splitting strings. There are plenty more ways to use one, and feel free to mention more in the comments that others might not have tried.

    I’m not sure the tally table is a core T-SQL skill, but I think it’s an important one you should learn as you grow your skills. After you’ve mastered the basics (Insert/update/delete, aggregates, outer joins) Adding in an understanding of window functions, the APPLY operator, and CTEs are also important to allow you to become better at solving the problems you run into with more efficient T-SQL.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Business Pressures

    I know a little something about building and scaling a system on the Internet. While SQLServerCentral isn’t the largest site out there, we’ve had lots of experience with growing a site while trying to manage a business. We were successful at it for years before Redgate Software purchased the site. I’d like to think we’ve continued to run the site successfully and balance the commercial needs with our goal of hosting a world class SQL Server community that educates the community on a daily basis.

    I thought back about our history and the way things have gone while reading an article on Github and some of the challenges they have faced they they try to build a business from their (primarily) free service. I certainly hope they succeed as I really like the Github model and only hope their investors are as enlightened and visionary as the founders of Redgate.

    There are constant challenges in running a business. No matter how good your intentions, the pressure to break even or turn enough of a profit to survive are always there. At the same time, there is just as much pressure from ethical business owners to keep their customers’ and clients’ expectations and needs in the forefront of their minds as they run their business.

    I have struggled with this regularly at SQLServerCentral and still do today. Serving the community while providing value for my boss (or making a profit to pay my salary in the past) are two conflicting goals. I think that things have worked out well, but I am constantly striving to maintain some balance between what is best for all.

    I do hope GitHub and many of the low cost services are able to transition and survive as they mature as businesses. Even if many of them can’t continue to provide the same services for free, I hope customers see enough value to pay for some level of service, and the sites survive.

    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.

  • Making Guesses

    I was reading a bit about the design and planning stage for a new database that will support some random application. The piece isn’t important, but I did notice one thing. The guidelines are generic, encompassing most of the things we might want to think about: structure of data, constraints, volumes of data and rate of growth, and more. It’s a good list, and one many of you have probably considered at some point if you’ve designed a system.

    However, I’ve rarely been able to actually do more than guess at many of the answers. Even much of the data I can write down is often little more than a guess. Even when I know the DRI constraints, I hesitate to document them separately from the code itself. Mostly because I find designs change early and often, and the documentation is rarely able to keep up with code. The same thing applies with the administrative type decisions I make.

    I’m also nervous about documenting guesses.   I find that far too often any answer I give takes on some level of truth with the developers, often being considered an immutable fact. The assumption that an early guess will match reality later seems to often blind everyone and limits the level to which we can adapt to changing situations.

    As an example, if I tell a SAN admin that we think we need 5GB of space, but we find that we need 20GB in a short period of time, I meet resistance. A debate, or argument ensues, we search for evidence that we need to change, and a very inefficient process ensues. The same thing occurs when developers, security admins, and especially managers hold too tightly to estimates and guesses.

    Certainly some guidelines are needed, but when I’m not sure, I tend to give wide ranges, just to prevent too many expectations from taking hold in people’s minds. We should know that our plans will rarely survive production deployments and workloads, but somehow we forget that when looking at the guesses we make early on.

    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.

  • Deploy from VCS

    Are you confident in your version control practices and code backups? If so, are you willing to do this? Wipe your applications and database schema from your servers and redeploy things from your Version Control System (VCS).

    I’ve typically maintained code in a VCS, but I have to admit I’d be worried about being positive about which version of code was deployed to which server in many of my previous jobs. Only in one position would I have been positive about which code was deployed to every system.

    I was teaching a class recently with Ike Ellis and he mentioned a consultant he knew always asked this. The reason was that if you can’t be confident of the state of your development code, you can’t really have confidence in your refactoring. There will always be places in the code that no one is willing to allow changes, and your flexibility will be limited.

    That’s very true, though even if you know what code you have, I’m sure there will still be sections of your code that no one wants to change. However, our goal should be to get away from code that’s untouchable.

    We should be able to change any code in the system if we have a good reason. When we can make improvements, we should. We need the confidence that we can alter code when we see the need. That means that we need a repeatable, reliable, adaptable software development process. That’s something we can’t build without making the conscious decision to do so.

    Steve Jones

    The Voice of the DBA Podcast

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