Home Uncategorized A Computer Scientist Meets T-SQL

    A Computer Scientist Meets T-SQL


    T-SQL WednesdayThere I was. A freshly printed bachelor’s degree in Computer Science tucked under my arm, I walked into my First Real Job. I’d never touched the technologies I was going to work with–ASP 3.0 and SQL Server–but my employer knew that, and I figured I’d be able to pick things up relatively quickly. After all, I’d been programming since the 2nd grade, knew a number of languages (more or less), and had all of the academic background I’d ever need.

    I was given a quick tour of the office, oriented by HR, and met the rest of my group. As a member of the small IT team, my coworkers included an Exchange administrator and couple of help desk support/networking guys. No other developers. I was given a cubicle (shared with a finance person) and told to start creating the company’s intranet. So, I did. And with no one to learn from or bounce ideas off of, I made up my own rules.

    One of the first things I was asked to create was a threaded message system so that the remote marketing and sales teams could converse with one another. I got to work on a basic (and very ugly) user interface, and then tackled the database side of the equation.

    I’d never encountered a real live database before, and scarcely even knew what one was, but I took a day or two to read “Teach Yourself SQL in 24 Hours” and figured I was good to go. Somehow I recognized the need for a self-referencing table. I created it, populated it with some test data, and quickly discovered that the simple test queries I’d written to date weren’t going to work. How would I navigate the hierarchy and display the messages in a threaded fashion?

    After messing around for quite some time and applying all of my Computer Science prowess, I came up with an algorithm:

    1. Using a cursor, start iterating the rows in the table.
    2. Insert “parent” rows into a temp table.
    3. For each “parent” row, recurse, calling the same stored procedure again and starting at step 1.
    4. Finally, return all collected results.

    With its nested cursors and various work being done in temp tables, this algorithm scaled so very well that shortly after rolling the message board out to production I got to work on implementing a cache so that users wouldn’t have to wait several seconds when making a web request.

    Clearly, I’d done something very wrong. And I knew it. I just didn’t know enough to know how to find the right answer.

    Much later I learned the correct term for what I’d created–an adjacency list hierarchy–and I learned about other methods of modeling hierarchies, including materialized paths and nested sets. I learned that many of the lessons I’d been taught in school–where the curriculum was heavily biased toward procedural languages–didn’t apply well to SQL databases.

    And most importantly I learned how to ask (and answer!) questions on online forums. Being a team of one doesn’t mean that you need to work in isolation. There is a huge community of people online who want to help you succeed. Finding these forums (and newsgroups. RIP, NNTP!) was a revelation. The ability to talk shop with people who understood what I was trying to do and how best to do it was invaluable to my learning how to be a better developer and not just a student of Computer Science.

    Many years later and I still get stuck on difficult problems, but these days I don’t try to do everything in isolation. I know better than that. I reach out to my network and take advantage of some of the great minds I’m lucky enough to have access to. And you can, too. Next time you find yourself with a less than ideal solution, swallow your pride and ask for help.

    And don’t forget to help someone else in return. As much as you’ll learn from the people answering your questions, you’ll get even more value from puzzling over the numerous problems that other people face on a day to day basis. Solving as many problems as you can–your own and those of others–is in my opinion the fastest way to truly master a given technology.

    Enjoy your journey–and always remember that you don’t have to go it alone.

    Previous articleT-SQL Tuesday #21 – A Day Late and Totally Full of It
    Next articleThinking Big (Adventure)
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.


    1. Thanks for sharing your failures!  I torched my way down the same path a decade or 2 ago.  I still cringe when I think about the stuff I did wrong and every now and then I get a panicky feeling that I might still be doing it and am too ignorant to know!  What I can always fall back on is my eagerness to learn new stuff and willingness to stay involved in internet discussions.  I had the great luck to work in a terrific team for 5 years and learned a great deal about solid database design but even in the best teams there is a tendency for group-think.  Getting out on the internet at sites like this and especially Stack Overflow allows me to see what other people are doing.  New methods and techniques.  and I’m always pleased when I’m able to contribute in a meaningful way to advancement of knowledge.

    2. I have great respect for @mrdenny,@peschkaj,@adammachanic and @sqlpoolboy (in the order I’ve exposed/learn’t from you guys) for what youve achieved in your Young age!! I hope to follow your path. Someday I will meet you and probably shake your hands

    3. Thanks for sharing Adam. Reminds me of an alike situation some years ago.
      "Next time you find yourself with a less than ideal solution, swallow your pride and ask for help."
      I guess that’s the most important sentence of your post. Nobody is perfect and the highest perfection we can reach is, to realize our imperfection.
      "Being a team of one doesn’t mean that you need to work in isolation."
      Completely confirmed, too! I can’t say how much I’ve learned from people like you and others in forums, like SSC.

    Comments are closed.