Voice of the DBA

Tag: database design

  • The Identity Debate

    This editorial was originally published on Mar 5, 2008. It is being re-run as Steve is on vacation.

    The Identity property might be one of the most controversial constructs in SQL Server, or at least right up there with the NULL/No NULLs debate. Someone sent me a note awhile back about this debate and I thought it might be worth bringing forward in an editorial.

    When I was getting started in SQL Server in the early 90s, I learned about primary keys, natural keys, and the ways you go about modeling a system. It all made good sense, especially at the time when I was working with systems that took years to build and were based on very well understood processes with tight controls on the data involved. So I learned to look for natural keys.

    A few years later I moved to a smaller company, considerably more agile, that was trying out new way of doing business. In the process of trying to revamp their systems, I found that things I was told were “unique” or “set in stone”, weren’t. Not even close, more like set in Play-Doh. And even then in a very humid environment with no danger of things drying out.

    I ran into identity field at some point and found that they were lightweight, solved a lot of problems, didn’t interfere with natural keys, and above all, were easy for a DBA to remember and work with when building applications or troubleshooting issues.

    However there is often a huge debate out there on what the value of identity colums is and how should they be used. Some people, like Joe Celko, rail against their use in a relational system. Others use them everywhere, making them clustered primary keys for every table. Many people are somewhere in between, using them where it fits, and sticking with natural keys if they are identifiable. Some people even use identities as their primary key, relegating natural keys as alternates with a separate unique index.

    I don’t think we put out enough information here, or on any other database site, that would help people better model their data and build relational systems. It’s a hard topic to write about and it’s hard to do it well without making everyone’s eyes glaze over.

    But I wanted to put this out there and see what you think about identity columns and their use (or mis-use) in today’s databases. They are definitely a tool that’s available to the SQL Server professional and one that I bet everyone has an opinion on. We surely value those opinions and as with many of these debates, I’m looking forward to learning a thing or two.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • The Cost of Architecture

    Falling Water is an amazing architectural wonder.

    I saw a note recently about the cost of decisions in programming made over time (thanks to BrentOzar, PLF). It’s an interesting look back at what decisions might have ended up costing computing a tremendous amount of pain and problems over time. It’s a nice read, especially if you remember something about the history of computing and how technology has evolved over the last forty or so years.

    Making decisions and choices early on in the development of a system is hard work. There’s a reason why architects are paid well, and sometimes seem slow to decide how to proceed. The high pay, however, is a reason why so many people crave the architect title, and even find reasons why they should get it, even when under qualified. Unfortunately we have no good guidelines on how to decide what titles someone should have, or they have earned.

    A great developer, a great architect, is often worth much more productive than an average one. Not only while building a system, but also in the future as the system is used, evolves, and is improved by others. Making good choices early on can result in much better software over time, as the ways in which software is improved are often based on the way it was originally built. Future developers try to continue to add to the project, or are constrained, by the early decisions made about the architecture.

    Hindsight always gives you a better view of a decision than you have when you are making it. Even in the article the author points out that the biggest mistake he found in computing was still the best decision at the time. We will hopefully make many good decisions, but all of us will make some bad ones. Whether in specifying hardware, writing T-SQL, or some other choice, we will find over time that some of our choices were poor ones.

    The best thing we can do is learn to look back at our architecture decisions over time and evaluate whether they were good or bad. Learn from our mistakes, and learn from the mistakes others have made, trying to strive to do better in the future with the knowledge that comes from examining the past.

    Steve Jones

    The Voice of the DBA Podcasts

  • DRI or No DRI?

    This editorial was originally published on Feb 19, 2006. We are reprinting pieces this week as Steve is traveling in the UK.

    There is a great debate going on in the forums about using PKs/FKs to enforce RI, in other words, declarative referential integrity (DRI) as opposed to using code logic to ensure that proper relationships are maintained in a schema. The opening post is a DBA stunned that developers in his new company do not use DRI. The reason given is performance is degraded, and that’s one that I can buy. There is additional overhead for checks with DRI, but it’s minimal and actually David Poole has a great article about this topic coming soon.

    It’s interesting that one of the questions is about how many 3rd party applications do not use DRI in their databases. Who knows why, though my suspicion is that they can easily “fix” issues with the applications with backend updates rather than maintaining good DB design practices. Or that they can easily alter the application to meet changing needs at various customers’ sites.

    I think it’s mostly the latter reason. That and laziness. I think the vast majority of developers are lazy by nature and dealing with DRI constraints when building an application is a pain. It’s a pain for me, but I still think it’s a good idea. Just like stored procedures add some overhead, so does DRI and many developers I have seen, both building shrink wrap and corporate software, don’t want to deal with the overhead.

    Personally I like having DRI implemented for the same reason that developers use objects, libraries, functions, and other consolidation techniques. It centralizes the “rules” about your application and ensures that they are always used. I agree there is overhead, but it’s a minimal amount and if you are seeing this on most servers then you are probably underpowered.

    My big concern is that often multiple applications or even multiple modules of a single application often need the same business rules: like no orders unless we have a valid customer number. If you depend on the application to enforce this, then you are gambling that every developer will do it correctly and the same way. In small, tightly controlled and managed environments, this works great. But as you grow your development teams, then it is easy for someone to forget to implement some RI rule or implement it differently than others.

    And those bugs are hard to find.

    I don’t think that every application needs DRI setup in it, but if you choose to not implement it, be sure you understand the consequences of your actions. And your boss does as well.

    Steve Jones

  • Archiving

    This actually looks like an easy way to archive data

    Not many database systems get designed with a well thought out archiving plan at the beginning of their lifecycle. In most cases that’s fine since few databases seem to gather enough data to require archiving, and if there is extra time early in the life cycle of an application, it’s probably better spent tuning queries for efficiency anyway.

    However when archiving is implemented, it can’t be done so in a cumbersome way. That almost always leads to some sort of data hoarding by individuals, which often leads to an application being built on Excel rather than SQL Server. This article talks about the problem of data access from a storage point of view, but it could easily apply to databases. If we require some special function, or intervention by the DBA to allow access to archived data, it’s quite possible that users will take matters into their own hands.

    As SQL Server has grown, we have ended up with a number of features that should make the archival of data much easier. Partitioned views and tables in many cases can allow an application to function with understanding that the underlying data is stored in multiple locations, and potentially even multiple systems. It does take a little more administrative work to seamlessly implement these features, but it’s a skill that DBAs should be able to make work smoothly.

    The problem of large data sets will continue to grow over time, and it’s a problem I suspect most DBAs will face at some point in their career. Take some time to learn about partitioning and how you can both manage data more efficiently over time, as well as improve performance if you find access patterns vary for different sets of data.

    It’s also a good reason to ensure that your applications don’t ever expect to pull back an entire table for any reason. Working with a few rows of data at a time is prudent when you have thousands of rows and essential when you have billions.

    Steve Jones

    The Voice of the DBA Podcasts