Voice of the DBA

Category: Editorial

  • Checking Up on Developers

    This editorial was originally published on May 8, 2009. It is being re-run as Steve is on vacation.

    The other day I was reading Brad McGeHee’s post on duplicate indexes, and it struck a chord with me. That same issue is one I’ve seen many times with in house applications. Developers don’t understand indexing, they create one clustered index (CI) as the PK, and then they create the same index as a nonclustered (NCI) one. Either they don’t realize that the PK is an index, or they are ignorant of the differences between a CI and an NCI.

    What’s worse is that I see it in third party applications as well. If you’re selling a product, I think you ought to know how to tune it for a database. If you don’t, I’d like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you’d have more DBAs hired by software firms.

    Or a little more training for developers.

    For this Friday’s poll, I wanted to pick on developers a little. I like developers, I appreciate the work they do creating some great applications I use, but I also think they make some silly database mistakes. So this week:

    What are common mistakes developers make in SQL Server?

    Indexing is one area, but what other things do you see a lot from developers? I’m thinking a good list of mistakes here might be a nice checklist to go over with developers as they complete an application. It could be in code reviews, QA, pre-purchase requirements, RFPs, any number of places.

    I’ll add one more indexing issue that I’ve seen often in third party applications. Too often I’ve seen developers create a separate index on every single field in a table. For all tables! I realize that indexes improve query performance, but it does create an issue with inserts/updates, and space. Space isn’t an issue as much as it was in the past, but as data sizes get larger, I would not be surprised to see this becoming a problem in some applications. More indexes also impact backup time and size.

    Give us a response this Friday of the common mistakes that you see developers making, and that you wish they’d learn to avoid.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    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.

  • Vendor Value

    This editorial was originally published on May 18, 2009. It is being re-run as Steve is on vacation.

    Software is a very interesting business. Where else can you produce a product that has zero marginal costs for additional units? Where else can you force a “maintenance” charge on your product? What other product must continue to be improved upon by the manufacturer after it’s sold?

    There actually are other industries for all of these cases, but they’re not numerous, and they differ from software. The business of building and selling software is fascinating, and I’m always learning more about it. I constantly see great questions in the forums at the Business of Software social network site. I’ll also plug the Business of Software 2009 Conference; if you’re a software person, consider attending this event. I went last year and found it very stimulating and educational, forcing me to think a lot about how that industry works.

    I happen to work for a software vendor now, Red Gate software, and I used to work for another, JD Edwards. I wasn’t a part of the development or sales in either company, but I did get to talk to people that were, and learned a bit the business. And so when I saw thisopen letter to Oracle,  it struck a bit of a chord.

    I’m not sure how software maintenance evolved to where it is today, but I think requiring it on a regular basis is an antiquated way of doing business. Microsoft doesn’t do it, though many third party vendors that work with Microsoft product do.  However I’m not sure that it’s really a value proposition for most customers.

    At least not if the software is written well. It’s a piece of junk, maybe that maintenance is required.

    I think that maintenance should be an optional part of the product, and that should fund support efforts, not a rolling cash cow to build the next version of your product. Sales fund that, or I think they should.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are available atsqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

    You can also follow Steve Jones on Twitter:

    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.

  • Core T-SQL

    Often I find myself wondering about the minimum bar we expect people to clear to be competent in some field. Almost all fields are evolving these days as technology and new ideas are put into practice in medicine, art, construction, law, etc. It seems as though a Renaissance is taking place with the speed and variety at which new information is spreading, usually due to advances in technology.

    In our field, working with SQL Server, we certainly see new ideas and enhancements taking place all the time. However there are also a number of core skills that evolve, but at a slower pace. For example, T-SQL grows with each new version of SQL Server, but the core language remains, and many people continue to use the knowledge they have had for years when writing code.

    This week I’m curious if we can debate about, and compile, a list of core skills with T-SQL that we think someone ought to understand to be considered competent as a database developer. I’m asking:

    What core skills should someone have with T-SQL?

    I’ll start the list, but feel free to add to it or give me your thoughts. I think someone ought to be able to understand these items and write code to solve problems that involve:

    • finding duplicate rows (grouping, joins)
    • returning aggregates of single or multiple columns (MAX, MIN, SUM, COUNT)
    • return aggregates in groups, or islands. (grouping and aggregates, windowing)
    • join multiple tables together on matching, multiple columns (joins)
    • find data in one table that doesn’t have matches in another (outer joins)
    • filtering data (WHERE)
    • subqueries and complex CTE joins of data
    • create row numbers and join back to a table without numbers (APPLY)
    • pivoting data from rows to columns

    That’s a starting list from a number of questions I’ve seen, but feel free to add your own skills you think people need.

    Steve Jones

    Video and Audio versions

    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.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 20.3MB) feed

    MP4 iPod Video ( 23.4MB) feed

    MP3 Audio ( 4.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • What is SQL Server not good at?

    I was reading a post that had this quote: ” The MongoDB docs tell you what it’s good at, without emphasizing what it’s not good at. ”

    This isn’t to pick on MongoDB, but the post did make me wonder what SQL Server isn’t good at? Should our docs, and BOL specifically, have warnings about when there might be problems with code? In addition to the remarks, should there be warnings about various features, functions and code? I image that the CREATE FUNCTION page might have some warning about scalar UDF performance in many situations, and I’m sure many of you would think of warnings that might be added for other features.

    I understand Microsoft might not like to point out flaws, but in the interest of building better code and applications, shouldn’t various versions of BOL, and perhaps all docs, warn the client about potential issues with using a feature in a certain way? Documenting the potential problems with using a feature in particular situations isn’t a flaw; it’s guidance about how misuse might introduce other issues.

    It’s not likely that we’ll start seeing more warnings in the official documentation, but for those of you that would like to improve the situation for others, there are always the “Community Additions” sections on all BOL pages. I don’t know if Microsoft would post your warnings there, but if they allow them, we might be able to help others understand the pitfalls of using a particular technique.

    Steve Jones

    Video and Audio versions

    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.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 15.6MB) feed

    MP4 iPod Video ( 18.3MB) feed

    MP3 Audio ( 3.8MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center