A Computer Scientist Meets T-SQL

There 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.