One immediate thing I look for in an execution plan is a Key Lookup. In a Key Lookup operation, SQL Server has to reference the clustered index on the table because a value it’s looking for is not present in the index that was used to find the row.

Normally, when you look at the Key Lookup, you will see a list of output columns. This is a great way to help you modify your indexes and make sure that you can avoid these extra disk hits by sacrificing a tiny bit of storage space.

A Normal Output List from a Query Plan

Today, while troubleshooting a query that is never finishing, I ran across this gem.

Empty. Bereft of Meaning. Nothing.

There’s no output list in the Key Lookup. I, wrongly, thought that Key Lookups would always include an output list. What’s going on here?

Well, here’s the scoop. I have the following, sample, table:

CREATE TABLE dbo.ServiceList (
  ServiceListId BIGINT NOT NULL PRIMARY KEY,
  BillId BIGINT NOT NULL,
  ServiceMonth DATETIME
);

In this query, ServiceList is referenced through a CROSS APPLY’d inline function. The inline function references the BillId and ServiceListId. There’s an index on ServiceList that contains both of these columns:

CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
  BillId
)
INCLUDE (ServiceListId, ServiceMonth);

This index is correctly being used by the query optimizer but in the past it’s only been used to perform lookups and push data to screen (or somewhere). So, what we’re seeing here makes sense. The CROSS APPLY needs to reference the BillId so the compiler checks the situation out and decides to use IX_ServiceList_BillId in order get the BillId. Since the index only includes ServiceListId, it has to perform a Key Lookup.

Protip: included data is only included in the index; it is not indexed.

To avoid this kind of behavior, I need to change this index to actually index on the combination of BillId and ServiceListId like so:

CREATE INDEX IX_ServiceList_BillId ON dbo.ServiceList
(
  BillId,
  ServiceListId
)
INCLUDE (ServiceMonth);

Like Jack, I think I could approach this in a number of different ways. I think I’ve already answered the professional route that I took. If I haven’t, it’s probably because my professional route isn’t all that interesting and it’s also all available on LinkedIn. You can, and should, fill in the job changes on my resume with something interesting like “After being attacked by a bear in the janitor’s closet at CareWorks Technologies, Jeremiah decided to take a safer job at HMB (they have no bears on staff as janitors).”

Anyway, you asked for it, you got it: how the hell did I get here?

I’m a Rock and Roll Machine

I love being on stage and in front of people, even though it terrifies the crap out of me. Apparently, I like that adrenaline surge. I’ve been playing guitar since I was 13 years old. When I was 23 I answered an advertisement and auditioned for a band. I got the job after 5 minutes.

Being a musician takes a lot of hard work, dedication, and practice. You work for hours and hours as a group, and hours and hours on your own preparing for a show. At that show, you’re going to walk up on stage and try to steal the attention of a room full of people who would, frankly, rather be doing one of a million other things – playing pool, talking to friends, hitting on that girl across the bar – than listening to you. As musicians, it was our job to get their attention, hold it for an hour, and make sure that they were happy about it. That job gets even harder when you’re in a band that only plays original material.

What seemed really fun – being in a band – turned out to be a lot of work – practicing three nights a week for four hours a night with the band and then practicing even more on my own. I learned a lot about myself – my tolerance for bullshit, how to get attention, and how to act in front of a crowd of people – while I was in the band. I also learned a lot about how to budget scarce resources – money and time – while still getting the job done – getting to the show.

The band eventually fell apart, as most do, but I learned many valuable lessons that I carry with me – time and resource management, performing skills, and how to make an ass of yourself and be okay with it. Most importantly, I learned that passion alone isn’t enough. You have to work for something if you really want to be good at it.

Everything to Everyone

The fact is, I’m not everything to everyone. But for a long time, I thought that I could do it.

Before and while I was in the band, I was married. The band took up a lot of time and it took a lot of time away from my marriage. While being in a band didn’t cause my eventual divorce, I’m sure it contributed to it (I quit the band about a year before my ex-wife and I quit the marriage). I remarried pretty quickly and that marriage ended almost as fast as it started.

Throughout all of this, though, there’s a huge undercurrent – I was trying to make everyone happy. I was trying to be a good husband, musician, developer, friend, son, brother, step-father, and about a million other things. I stretched myself thin and I broke.

These days, I know that I can only be me and that I’m the only person I need to make happy. There’s a reason why I work with SQL Server but I program with Ruby, why I listen to old school hardcore punk but I play a bizarre blend of folk and country, why I devote more time to my friends and family than I have before – these things all make me happy. If it doesn’t make me happy and I don’t need to do it to live, I don’t do it.

Self-Fulfilling Catastrophe

A couple of paragraphs ago I said “I stretched myself thin and I broke.” I really do mean that. During the first divorce, I moved into a tiny house on the ass end of Columbus, stopped paying most of my bills, and ended up living on as little as $20 a week. The funny part, though, is that I always found the cash to go out and party, or to stay in and party. This became a bad habit even once the divorce was done and I should have been back on my feet. Over the next 4 years everything spiralled completely out of control.

I’m pretty sure there were more than a few times I nearly lost my job. I frequently called in “sick” from the crowded patio of a bar at 1:30AM, had my car repossessed, racked up so much debt that people were calling my family members to find out where I was, and I partied seven nights a week. I lost a lot of my friends and damaged most of my remaining friendships irrepairably in the process. Throughout this ordeal, a few of my friends stood by me. They didn’t give up on me despite my ardent attempts to turn myself into a drooling train wreck of a human being.

In June of 2008, I gave it all up. I realized that I was a total train wreck and that everything around me was completely out of control. My career was stagnant. I was sliding backwards as a person. I wasn’t meeting any of my goals for myself because I was too busy slowly killing myself.

I stopped drinking. I got the help I needed and I began the long, painful, process of pulling myself up by my shoelaces. I dried out.

By August, I had completed the SQL Server 2005 MCITP: Database Developer certification. I started the Columbus chapter of PASS in October. I started paying back all of my bad debt (only a few months left to go). Nine months after I quit drinking, I quit my one to two pack a day smoking habit (sorry about that one, Mom and Dad) – I never would have thought I could end my 13 year addiction to nicotine.

I have a great relationship with my family and friends now, it’s better than anything I could ever hope for.

I learned a lot of things from this.

1. I can be horribly selfish.
2. There’s nothing better than not being that selfish.
3. I can do damn near anything I want to do if I put my mind to it.
4. There are some things in life that are so important you can’t afford to overlook them.

Afterward

This summer, one of my friends (someone who met me at my lowest and stuck by me through everything) is giving me the greatest honor I could ever hope for: on June 19th I’ll be officiating his wedding. Like a lot of people, I wouldn’t change a thing about my life.

The Story

Adam and Bill work together at Amalgamated Spats. During a design meeting Adam mentions a product that could solve some of the problems the developers at Amalgamated Spats are facing. Although Adam is a specialist, he has a great deal of work on his plate and Bill is designated to develop the features. Bill isn’t a specialist, he’s a generalist. Bill is a great developer, but he’s unfamiliar with this specific product.

Over the course of development, Bill makes great strides. Unfortunately, there are some features that he isn’t able to solve programmatically even though they are included in the product. These features are features that were sold as part of reason to use the product. When Adam and Bill’s manager talks to Bill about his progress, Bill tells the manager that he wasn’t able to get the features done because it isn’t possible using the product. The problem is that the features do exist in the product, they just weren’t available the way Bill was using it.

Adam and Bill’s manager is upset that they’ve put so much faith in this product. While the manager trusts Adam, Bill has been working with the product day in and day out, trying to implement these features – why wouldn’t Bill tell the truth? As a result, Adam loses credibility. He can get that credibility back, and certainly will, but for a while it’s gone.

The Problem

There are a couple of problems with this situation:

1. Bill has effectively thrown Adam under the bus (we call this bussing). Adam has lost credibility with the manager because he gave “wrong” advice.
2. Bill said “No” when he should have said “I don’t know.”

Of these, the second is far worse. Ultimately, the first problem will go away and Adam will gain that respect back and everyone will be happy again. But the second problem speaks volumes.

It’s Not Okay to Say “I don’t know”

But I just said that Bill should have said “I don’t know,” right? Wrong. Saying “I don’t know” and ending it at that is not acceptable. In this crazy software development world, it’s our job to find the solutions to problems. Did you read that correctly? I didn’t say that it’s our job to code the solutions to problems, it’s our job to find the solutions to problems. Sometimes the solution is to use an existing solution or feature which you can only find through research. Do you see what I’m getting at yet? “I don’t know” isn’t acceptable but saying “I don’t know, but I’ll find out” is perfectly acceptable.

What’s the difference?

When you say “I don’t know” and you stop there you’re effectively throwing your hands in the air and giving up. You’re not only admitting that you don’t know, but that your lack of knowing is the end of it. It ends the conversation

If you say “I don’t know, but let’s find out,” you’re telling the other person that you don’t know the answer and it bothers you. It bothers you so much that you’re going to find out the answer. You’re advertising your inquisitive mindset and the way you solve problems.

What Should I Do?

The next time someone asks you a question where you don’t know the answer, tell them you don’t know. Be completely upfront about it. Follow that up with “but I’ll find out.” Then, actually find out the answer. Research the problem, research the product, and consult with experts – even if you think you are one.

Project52 is a personal challenge geared toward getting fresh content on your website. The goal is to write at least 1 new article per week for 1 year. Because we all know what it‘s like to procrastinate on our content. A website is not just a fresh design that can be uploaded to the web and forgotten about!

I’m hoping I can hold up my end of the bargain, but here’s to hoping.

Object-Relational Mappers, O/R-Ms, are often hailed as the solution to the differences between object-oriented code and the relational storage layer. By simply using some magical tool, library, or bong we can code, map, or smoke our way to software that accounts for the differences between databases and object-oriented software. The thing is: this doesn’t work.

Magical Snake Oil

A lot of developers see the database as nothing more than a hole for objects. You tie a string around them in the application layer, put some pretty ribbons on the string so you can find your object later, and you then chuck it in the hole. When you need it later, you find the pretty ribbon and pull the object back out, right?

Not quite, it appears. First you have to create some kind of mapping so you know which tables are associated with which objects. With some O/R-Ms you have to create mappings in XML. These mappings require maintenance whenever your application-layer or database changes. They are also error prone when you are setting them up. Don’t believe me? Search the web for developers bitching about working with Hibernate and NHibernate mappings. Solving non-trivial mapping problems isn’t easy with these tools.

Isn’t one of the reasons for using O/R-Ms to cut down on the amount of difficult, “specialized” code that you have to write? Instead of writing SQL – a language purpose built to the task of querying relational data from a database – we resort to creating magical XML mappings, write more code using fluent style code with lambda expressions and other advanced language features, or rely on magical automapping to wire up classes directly with tables. Think about it: in order to avoid writing special purpose code, we’re writing special purpose code in languages that aren’t even well suited to the task at hand. If these tools are solving problems, why do so many developers bitch about them and write code that generates their entire O/R-M layer, which is really just code to generate mappings to and from SQL.

Shackled to the Solution

If everything is as horrible as I say, why are people using O/R-Ms? A lot of O/R-M software works. Technically, an AMC Gremlin also works. It has wheels, doors, and an engine. It is suitable for driving you around town. It is not ideal. Likewise, O/R-Ms are suitable to the task of getting at data, but they are not ideal. I hear the arguments all the time, and I’m going to discuss some of the more prominent ones. I feel it’s important to put my biases out there, so keep the following in mind:

1. I am a database developer and a production DBA.
2. I still work with OO languages, just not as part of my day job.
3. I don’t hate O/R-Ms; they have a place. They just aren’t a magical cure-all.

But my database is just an object store

Use a flat file. You don’t need anything more complicated than that. Clearly, since you think of your database as an object store you’re not overly concerned about data integrity, reportability, concurrency, consistency, or any of the other reasons to use a relational database. Hell, use an object-oriented database for all I care. Just stop using RDBMSes and complaining that they don’t meet your needs. Or take a refresher course in everything that an RDBMS provides. I’ll wait.

Putting my vitriol aside, a database is more than an object store. By lowering the database to the level of nothing more than an object store, you’re admitting that you haven’t taken the time to fully understand your business problem or the underlying data. Business problems are, fundamentally, problems related to ever changing data and ways to store and retrieve that data programmatically for human consumption. Objects and classes are ideal for working with and describing systems with different behaviors in a set of related ideas. The OO paradigm works with relatively constant data. I have a Phone base class (I’m not making the obvious IPhone interface joke) with a call(string number) method. The PayPhone class will implement a different call method than the CellPhone class. This works wonderfully for modeling the behavior of a telephone. In the database, we’re likely to have a table with columns PhoneId, PhoneType, and Number. There’s no actual differentiation between how we’re storing any of these phones. Having an OO paradigm actually complicates the issue.

But I don’t have time to write CRUD stored procedures

You know what? Neither do I. I generate them. SQL Server Management Studio will generate them for me. SSMS Tools Pack adds a context menu option that reads “Generate CRUD.” I could, in a very short amount of time, write T-SQL to generate dynamic CRUD for me that will even only update the values supplied.

People, I hate to break it to you, but if you took the time to understand SQL you could probably write your complex mappings using views and stored procedures faster than you could write your O/R-M XML/fluent/automapping magic code. It’s just as easy to use stored procedures and views as it is to use tables. Trust me, I know this.

You’re just being lazy or scared of SQL.

But my O/R-M uses prepared statements and those are just like stored procedures

I understand that you believe that prepared statements are just like stored procedures because some expert told you so. I’m very happy that you have good friends who have use their free time to selectively quote documentation and educate you about things they don’t understand.

Thing is: prepared statements are not just like stored procedures.

I will admit that with a prepared statement you are buying yourself the ability to have cached queries. But what if your line of business app is one of a hundred apps on a server that is near capacity. Odds are that your prepared statement execution plans aren’t going to stay in memory. My DBA voodoo talk means that your prepared statement (the one that’s “just as good as a stored procedure”) is actually worthless. With stored procedures us DBA voodoo priests can do all kinds of things that you didn’t even know about – we can tell the database which indexes to use. We can force the database to use execution plans that we saved off ages ago. Hell, we can even re-write your table structure into something that suits the relational model a lot better and hide it that re-write through the stored procedures.

But I digress

No, seriously, I was digressing. The point is, developers start making bitchy noises whenever it’s suggested that they do something in SQL. These are the same developers who suggest some new tool/library because it’s faster/better/stronger/more Daft Punk than ever.

There are a lot of problems that O/R-Ms seek to solve. In an ideal world, it would work. In reality, it doesn’t. The instant you start relying on an automated solution to fix everything, you’re losing. I strongly advocate automation. Hell, I’ve automated a ton of my job away already so that I have time to do the things I enjoy: helping our development staff understand what they’re working with and finding the best way to solve the problem. Sometimes that solution has nothing to do with the database. That’s fine by me. But we put that solution where it belongs. We aren’t shoving everything into an OO box because that’s what we know.

The Cure Is Making Us Sick

O/R-Ms make assumptions about data access. Many O/R-Ms assume that you allow access directly to the underlying tables. A lot of them also assume that your tables reside in the default database schema. The thing is, there are a lot of assumptions going on. If you work with the O/R-M library, you’re missing a lot of the built-in features that come with your database out of the box.

Security

Let’s look at an example of a corporate intranet. We have a rule that the HR department data is not visible to the sales department and vice versa. If we’re working purely with an O/R-M, we’re going to have to identify the classes and tables that map to each department and write programming logic to make sure that HR users can’t see sales data and that the sales people can’t see HR data. But, if we go ahead and connect directly to the database with Access or Excel, anyone can go ahead and see these tables.

Working with database features like schemas and security, we can lock down the tables, views, stored procedures, and functions on a schema-by-schema basis. It’s possible to give the sales department access to specific HR information while denying them carte blanche to all HR data.

Data Access

Something else to take into account is universal data access. If we’re writing all of our data access logic in an O/R-M, we’re going to need to create a robust service layer that everyone can communicate with. Depending on the size of the company, we’ll have to create multiple service layers to make it possible to access our data. Why? All of our logic, all of our rules, all knowledge about the data is embedded in the O/R-M.

Let’s say that we are working in a large corporation. We have some legacy Java applications, some front line apps that were written in PHP, and new development is going on in .NET. Our team has started development and they’re using WCF and an O/R-M to get to the data in the database. We’ve got logic embedded in our data access layer. At the inception of the project we were under the assumption that no other applications would need to interface with our data. Unfortunately, we’ve done such a good job that the other development teams would like to get to our data as well. Java and PHP can’t talk to our WCF service layer. So, we have to write some SOAP services to talk to the Java and PHP code. Any time that we change or add new functionality to our WCF layer we have to add to or change those SOAP services as well. If we had originally gone with data access logic embedded in the database through stored procedures and views, it would have been simple to give the other teams access to our database objects but not the underlying tables. In short, we would have a low-level data access layer in the database that sits just above our data and below our code.

The database provides a universal way to get at the data in the database, agnostic of any platform or programming language.

Data Life, Code Life

Do you remember what programming language you were using five or ten years ago? There’s a pretty good chance that it’s not the same language that you’re using today. Next, think about how old your data is. There’s an even better chance that you’re working with data that is a lot older than five or ten years. Sure, the underlying platform may have changed from one RDBMS vendor to another but the structure of the data has stayed the same and our data access logic, if written in SQL, has probably changed very little from RDMBS to RDBMS.

As time goes on, the data structure will change at a very slow pace. I can’t predict what language we’ll be using in the application layer in five or ten years. However, I can assure you that developers don’t want to maintain a legacy data access layer because too much logic is embedded in the layer to move away from it. Instead, what will happen is that there is going to be an expensive and lengthy conversion project to move from the existing programming language to the new programming language. These conversion projects are frequently hidden in project time frames, but they exist and they add time to your projects – time that could be used to provide features to the users or work on far more interesting projects.

There is no Magic Cure

Data lives longer than code. If you don’t believe me, ask around. The data I’m working with goes back seven years; we’ve been using .NET for 15 months. At the recent PASS Summit I spoke with people who have data that is 30 years old and I’ve heard stories of people working with data older than that. If this ancient data is an RDBMS, there’s a great chance that the application accessing it are written in a variety of languages, some old and some new. They aren’t constant. Developers have come and gone. The data is permanent. There is a universal language for accessing it.

Databases provide features and functionality that we frequently re-implement in application code. It’s incredibly important to consider where we’re placing logic lest we incur technical debt that will take orders of magnitude longer to fix than to develop correctly. Take the time and figure out when you want to pay for that feature. Is moving your release date up by several weeks more important than saving six months of time down the road?

Always Learning

As knowledge workers, we should always be acquiring more knowledge. Knowledge comes through deepening our current understanding of a subject as welling as adding breadth to our experience and expertise. That’s right, we’re supposed to keep learning new programming languages and increasing our knowledge of the ones we already know. Heck, The Pragmatic Programmer suggests learning a new programming language every year to stay on top of your game.

While you are busy learning the hot new languages, or revisiting existing languages, you’re missing something: SQL is a language. By refusing to learn SQL apart from simple SELECT statements, you are refusing to leverage the most powerful piece of software at your disposal. I understand that set-based programming is different from the type of programming that you’re used to, but it is a perfect fit for the problem domain: working with data.

A Working Example

MySpace is arguably the biggest pile of insanity to ever be unleashed upon the internet, barring twitter. And yet, somehow it keeps running. Why? Because they know when to leverage the power of the database. They’ve learned that there is some functionality that can be handled in the data tier and they use a native solution to solve the problem.

When MySpace were facing scalability problems as the site grew larger, they made use of SQL Server’s Service broker to handle communication between multiple scale-out servers. Using SQL Server Service Broker helped them ensure that transactions were atomic across multiple servers. They built a custom tool – Service Dispatcher – that build on the technology already provided by SQL Server Service Broker

They took the time to understand their tools.

It’s Not All Bad

Lest you think that I despise O/R-Ms and I’m some kind of horrible data zealot, let me assure you of a few things:

1. I am a data zealot.
2. I like some O/R-Ms

A good O/R-M makes it very apparent what it does and where its limitations are. Just like a good language. T-SQL is a good language. I know where the limitations are. Anyone who uses it for a while knows what the limitations are. That’s okay; we know what it’s good at.

Likewise, when I’m writing Ruby on Rails code, I know where the limitations of ActiveRecord are. It makes them very apparent to me and it gives me ways to drop down into native SQL and it behaves just as well. When I did .NET development for a living, we used LLBLGen Pro. It, like ActiveRecord, does some very intelligent things. I always knew the limits of LLBLGen.

A poor O/R-M solution tries to be everything to everyone. It supplies pseudo-languages to query the database that are, really, bizarre subsets of SQL with slightly non-SQL like syntax that make it just confusing enough.

But What About…

LINQ

LINQ is a really cool idea. I haven’t worked with it enough to make a judgment call on it. Aesthetically speaking, I think it uglies up code with a giant chunk o’ nasty that looks like it doesn’t belong. But, that being said, it also offers a way to query data into an internal memory representation of what appears to be a set and then join that to existing collections of objects. Pretty cool, eh?

Active Record

If we’re talking about the Ruby O/R-M framework, I think it’s pretty good. The limitations are clearly plotted out and yet it’s flexible enough (thanks to Ruby metaprogramming magic) to handle on the fly data projections and custom querying.

ActiveRecord works well when you’re working within the context it knows: table per entity data models. Once you move from that paradigm, ActiveRecord stops being quite as helpful. On a previous project, in a different life as a consultant, we were building what amounted to a master data management system with Ruby on Rails. As the data model grew in complexity to account for the vagaries of legacy applications, we had to abandon ActiveRecord on multiple occasions and work with straight SQL to retrieve the objects the way we needed. The limitations of the OO approach became readily apparent we moved outside of the OO paradigm and started working with relational data.

Are we talking the Active Record design pattern? In that case I can’t really comment. It’s a design pattern. If you implement it right, it works wonders. If you implement it poorly… Well, if you implement it poorly I’m likely to tell you that your code smells bad and then explain why.

NHibernate

Nothing against NHibernate, I’m sure some people find it useful. I don’t.

There is a great deal of complexity in NHibernate. So much so, in fact, that I will do everything I can do avoid writing a speck of code for it.

Sure, you can tell me that I don’t have to write the XML mapping files and I can use Fluent NHibernate. I’ve done that. I’m still writing code to map from classes to tables. I can accomplish the same thing with boilerplate code and T4 to generate it.

But what about AutoMapper? AutoMapper solves simple problems. When you start creating complex mappings it starts getting trickier and you have to resort to writing the mappings yourself.

But what about Visual NHibernate? It’s a hot new tool that lets developers graphically design their table to object mappings in a GUI and then create the mapping files at the push of a button. Let me get this straight – you’re telling me that I should learn a tool to make my O/R-M tool easy to use because writing SQL is too hard/slow/requires sobriety? Let’s think about that for a minute:

1. SQL is hard. I’ll grant you that it’s not easy. Neither is programming. Look at IoC containers and dependency injection.
2. Objects aren’t sets. I’ll grant you this one, too. A collection of objects bears no resemblance to a set.
3. We have a mismatch going on.
4. An O/R-M solves this problem (NHibernate, for the sake of example).
5. NHibernate mapping is painful.
6. Let’s use a tool because mapping is difficult and error prone.
7. The tool isn’t always right so we have to go back and fix our mappings.

In order to ease the pain of mapping SQL statements to objects I have to learn two new technologies to obfuscate away the data persistence layer instead of learning how to correctly access data in the first place.

I trust you can sense my incredulity about the merits of this scenario and I’m going to move on.

Other O/R-M Goes Here

I don’t know, unless I’ve listed it in this article I’ve probably never used it. But I see statements like this one: “I use (your O/R-M goes here) and it solves more problems than it creates.”

When you’re using a tool and you’re saying “Meh, it hasn’t killed my cat, yet,” I think it’s time that you rethink your strategy.

Other Language Goes Here

I’m going to use Ruby as an example, because I know it and I love it. Ruby is a class based language. Just check out the code you write for a basic Ruby on Rails project:

class Post < ActiveRecord::Base
end

ZOMG! It’s a class! But, thanks to RubyMagic, Ruby is able to change the Post class at run time to look more like this:

class Post < ActiveRecord::Base
  attr_accessor :id, :title, :body, :created_at, :author
end

Shazam! It’s like I wrote it myself, but I didn’t and I didn’t have to map anything. Time for Fruity Pebbles.

When you get down to brass tacks, it’s only because of Ruby’s magical metaprogramming that a lot of the things I love about ActiveRecord are possible. Thankfully, the DLR should make a lot of this pseudo-magic readily available in the .NET Framework with the upcoming release of .NET 4.0. I don’t know about any other programming languages and how they intend (or if they intend) to make any kind of magical dynamic whizbangery available. The point is, dynamic whizbangery is neat. Dynamic whizbangery, when used properly, goes from being neat to being a productive tool that saves everyone a ton of time and effort.

Object-Oriented Databases

I’ve never used something like db4o, so I can’t comment on them directly. But what I can tell you is this: There is no way to easily query data from an OODB. It requires complicated logic that is embedded in your data access libraries and, probably, is not available outside of your OO programming language. This effectively prevents the business users from hooking up Access or Excel to your OODB to run ad hoc reports. Instead you’ll have to write a complicated mapping process that pushes data on a regular basis from the OODB to an RDBMS for reporting purposes. You may end up doing this if you allow live ad hoc reporting against your production RDBMS. If you’re storing your data in a relational database, you can take advantage of SQL (which you’re probably already using) to move the data from the live production system to the reporting system.

OODBs do offer a seamless mapping between your code and your storage tier. There’s nothing to change because you’re storing native objects on disk. There is no O/R-M to hassle with. As I mentioned above, you’re going to need to create some kind of mechanism to export the data. db4o does contain functionality to do this. Or it claimed to the last time I looked into it. But, there are inherent difficulties in mapping OO code to relational storage mechanisms. Hell, that’s all we’ve been talking about for the last two thousand words.

The Philosopher’s Stone

There is no single solution. No alchemical, magickal, panacea or philosopher’s stone.

I really wish that I had a great solution to this problem, but the fact is that you need to take the long view about your project. Are you willing to use an O/R-M and trade off your initial development speed for problems down the road? You’ll notice that I’m not naming any specific problems. Why? Because the problems you’re going to run into are, without a doubt, very different from the problems I’ve run into. They depend on the application you’re developing, the library you’re working with, and your experience with everything involved (the other developers, the codebase, the O/R-M, and your data model).

On the other hand, you could work with SQL and a thin abstraction layer and trade off initial development speed for a consistent long-term development pace. What does this require? Your developers will need to understand your OO language of choice, the data storage model, and SQL. That seems like a lot to ask but that’s how we do things at work. The developers who work with me on a daily basis understand the underlying data model. They understand .NET development. They know how to write SQL. Not just simple SQL, but they’re capable of writing fairly complex SQL. They didn’t show up on day one with these skills – we all had to learn. I know OO development well enough, I know SQL very well, and I learned the data model.

Commitment to doing your job well requires that you understand all facets of your work – the business, the data, and the applications that access it.

Note: Any time I mention a specific technology or library, I’m not trying to knock it, I’m just pointing out an issue I’ve encountered.

References

We all owe a lot of thanks to Ted Neward for his great essay The Vietnam of Computer Science and his follow up Thoughts on Vietnam commentary

Martin Fowler’s Patterns of Enterprise Application Architecture

Phil Haack’s Why Store Your Data in a Relational Databse

Jeff Atwood’s Object-Relational Mapping is the Vietnam of Computer Science

Object-Relational Mapping Wikipedia FTW!

Theme word? I don’t have one, “f*%#ing rad” is two words, thank you very much.

Communication

Become a Better Writer

A long time ago, I went to college for four years, took out a bunch of loans, and got a degree in English, Non-Fiction Writing. I did exceptionally well in my English classes and did even better in the writing classes. I love writing and, frankly, the quality of my writing has not been up to par recently. I want to change my focus as a writer this year. I want to switch from writing short, highly technical, blog posts and change to creating longer article and essay length pieces. There’s nothing wrong with shorter, technical, posts but that is not where my interests lie. I want to focus on improving my writing so I can effectively teach more advanced concepts through writing as well as through public speaking. I was a good writer before and I’ll be a good writer again.

Become a Better Presenter

I have no doubts about my abilities as a presenter – I have a lot of room for improvement. Over the course of the year I’m going to team up with a number of people to improve my presentations. I want to get better at better content and delivery as well as meticulously practicing my presentations until I can deliver them in my sleep. I know that a lot of improvement comes from repetition and I would like to speak at least six times this year. With my current upcoming speaking schedule, that shouldn’t be difficult at all, but we’ll see.

Self-Employment

I don’t intend to be self-employed by the end of 2010, but I plan on being well on my way. How am I going to get there?

Writing

I’m writing stronger blog content. That’s not going to be enough. I want to get my name in a more places – magazines, guest blog posts, paid content, white papers, and a book. I’ve been shopping an idea around to various publishers and I’m hoping to have a book written and finished by the end of 2010. None of these things pay big bucks, but they all add to the bottom line.

Consulting

I haven’t done much consulting in the past, of course it was difficult when my day job was being a consultant – there’s an expectation that you will bring the business back to the company. Things have changed, I’m a full time employee now. My employer and I have had the talk – I’m allowed to do consulting work as long as I don’t help out our competitors.

This year I will take on several clients that require a small amount of time (10 – 20 hours each) every month. This isn’t to replace my existing job, this is to supplement it and provide additional income. Before I go completely solo, I want to have a substantial savings buffer built up and doing work on the side makes this a lot easier. I don’t plan on going at it alone – I’ve discussed this several co-conspirators and we have plans to get started this year.

Personal Ventures

I have an idea for a business that will make me tens, maybe even hundreds, of dollars. I plan on fully pursuing this over the course of 2010. Honestly, I have incredibly high hopes for this business and we have already thought of multiple ways to monetize our business. That’s not to say that it’s sure to happen, I have a lot of hard work ahead me, but I’m looking forward to it.

PASS

As many of you know, I was elected to the PASS Board of Directors and subsequently put in charge of the Summit program portfolio. This is the heart and soul of what I want to do over the next 12 months. I am going to make sure that the 2010 Summit program committee has my full support and that we’re can make the summit the best event possible.

Inky Mess

This is a personal one: I want to finish both full arm tattoo sleeves this year and, hopefully, start on my legs.

Once again, I think I’m supposed to pick victims. I’m going to pick Matt Nowack, Rick Kierner, Jeff Blankenburg, and Jorge Segarra

I also want a chainsaw on my hand like Bruce Campbell in Army of Darkness. That would be f*%#ing rad.

The problem with using an existing function is that the existing functions just return the week and not the full year + week combination. I used the function from the Less Than Dot wiki as a starting point and added my own code to create the full YYYYWW string to give me an ISO Week. This isn’t actually in the ISO standard format for reporting the ISO week, but I also don’t care so long as I can use the ISOWeek for effective querying.

Enjoy

ALTER FUNCTION ISOweek (@DATE DATETIME)
RETURNS VARCHAR(6)
AS 
  BEGIN
    DECLARE @ISOweek INT,
      @YEAR INT,
      @rVal VARCHAR(6) ;
 
 
    SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yyyy, @DATE) AS CHAR(4)) + '0104') ;
    SET @YEAR = DATEPART(yyyy, @DATE) ;
 
    SET @rVal = CAST(@YEAR AS VARCHAR(4)) + RIGHT('00' + CAST(@ISOWeek AS VARCHAR(2)), 2)
 
    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0) 
      BEGIN
        SET @rVal = dbo.ISOweek(CAST(DATEPART(yyyy, @DATE) - 1 AS CHAR(4))
                                   + '12'
                                   + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2)))
                                   + 1 ;
      END
 
    --Special case: Dec 29-31 may belong to the next year
    IF (
        (DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
       ) 
      BEGIN
        SET @ISOweek = 1 ;
        SET @YEAR = DATEPART(yyyy, @DATE) + 1 ;
 
        SET @rVal = CAST(DATEPART(yyyy, @DATE) + 1 AS VARCHAR(4)) + '01'
      END
 
    RETURN @rVal ;
  END
GO

This lets me do some snazzy reporting hackery like this:

SELECT  MIN(c2.[DATE]) AS StartDate,
        MAX(c2.[DATE]) AS EndDate
FROM    dbo.Calendar AS c
        INNER JOIN dbo.Calendar AS c2 ON c.ISOWeek = c2.ISOWeek
WHERE   c.[DATE] = '20091229' ;
 
/*
StartDate               EndDate
----------------------- -----------------------
2009-12-27 00:00:00.000 2010-01-02 00:00:00.000
*/

Which is then used to feed report parameters.

In about a week, I’m going to be taking on my first year of duties as a Director-at-Large of PASS. What are those duties? I’m going to be overseeing the program efforts for the 2010 PASS Summit. I’ll be working closely with an established team of volunteers to make the upcoming Summit an amazing experience for everyone.

Outside of PASS, what have I been doing?

Writing
I have been working on an essay length blog post. I want to branch into writing longer, more-focused, articles here. I love the shorter posts, but sometimes I don’t have anything to write about. Sometimes I just want to focus my creative energy toward something that requires more effort from me. These longer articles give me a chance to be a bit more creative and exercise my creative powers.

It’s also nice to get back to one of my passions: writing. Long before I went to college, I had a passion for writing and a passion for the sciences. When I went to college I majored in English with a concentration in Non-Fiction Writing. My original goal was to go into technical writing. That didn’t happen. I had to feed myself, so I got a “real job.” It’s nearly ten years later and I’m confident in my knowledge and ideas and writing abilities. It’s time to start writing longer pieces.

Working
For those of you how might not know, my client hired me on in November. They made me an offer a few days before I left to go to the PASS Summit and I accepted. This has been a big change for me. For the first time in 5 years I’m not a consultant – I am now responsible for the longevity of our data. With that added responsibility, I’m now the key decision maker for our databases (barring the budget, of course). In addition to my move onto the PASS Board and becoming a strategic thinker, I have to wear both tactical and strategic hats at work. It has been an interesting change and I’ve been fortifying the shop so I can do enjoyable things moving forward instead of plugging holes.

Working… for myself
I’ve been working on several side projects. There’s very little to report. One of the projects is a loose idea that will be hashed out over the next year and will hopefully become something great. Watch this space for more. I should be able to announce another one in a few weeks. And, the third one…

Well, I’ve jumped headfirst into the world of software development, again. My friend, Matt Nowack, and I (with some help from Brent Ozar) have been collaborating for the last two weeks on a project. There’s not a lot to talk about, yet. But the change in technology stack has been interesting. Rather than use a Microsoft stack, we’re using Ruby on Rails, a pair of Macs, TextMate, SQLite, and github to work together on this project. It’s been a fun experience getting back into development and considering a different set of problems from the day to day issues of a DBA. What has really amazed me, though, is how well all of these different tools have come together so nicely to make our development experience painless.

Restores
As an aside, I had the primary HDD in my laptop die not once, but twice, in the last few days. The first time it threw an error during a routine drive check that OS X thankfully performs. I had backups, so I booted the machine from the install DVD, plugged in my backup drive, and did a full restore with a low-level format. within 48 hours, the machine wasn’t booting and Seagate’s tools were reporting massive disk errors. Seagate’s tools also let me put in an RMA request through the tool itself and I have been informed that my new drive should be shipping soon. Meanwhile, I went down to a local computer emporium and purchased a new HDD. I threw it in the laptop (unformatted) and fired up the laptop with the install DVD in the drive once again. Within three hours I was up and running with a working copy of my laptop. I have never doubted the need to take regular, reliable, backups. After the events of the last several days, I am doubly convinced. Without backups, I would still be installing software right now.

Allen White recently presented for CBusPASS about PowerShell. First off, I’d like to say thanks to Allen for driving down from Canton, OH to speak in person. He took the time to stick around afterwards and chat about a bunch of things that were on user group members’ minds.

Allen’s presentation covered the basics of PowerShell through concrete examples – automating backups, restores, and database creation. Throughout the entire process, Allen demonstrated many aspects of PowerShell that make the DBA’s life easier. Allen demonstrated how it’s possible to easily work with data from files, the registry, and SQL Server simultaneously through PowerShell. Many of these things would take a considerable amount of work with T-SQL, if they’re even possible at all.

Interestingly, one of the first things I was reminded of was how much PowerShell looks like a variety of programming languages, specifically Perl. One of the nicest aspects of Perl is that it’s a glue language. Glue languages help you piece together various pieces and parts of your servers that have no real way to communicate with each other. PowerShell makes it possible to do the same thing, natively, on Windows.

PowerShell, as part of the Common Engineering Criteria, is enabled in all of the newer Microsoft server products. So, you can be sure that PowerShell is available as part of SQL Server, Exchange Server, Windows Server 2008, SharePoint, and many other products that I’ve forgotten. This will make it easier to take care of a larger number of servers and diverse server products using a single language.

This is the state of PowerShell right now. PowerShell 2.0 is available with Windows 7 and Windows Server 2008 R2.

What’s really exciting is PaSH.

PowerShell Tomorrow

Object reference not set to an instance of a phone

PaSH is an open source reimplementation of PowerShell for other operating systems – Linux, OS X, Windows Mobile.

Think about that for a minute… Did you really wait for 60 seconds to think about that? Good.

With PaSH I could VPN to my network, fire up my PowerShell prompt on my phone, and start diagnosing server problems. Or run a remote backup. Or failover a cluster. Or any number of wonderful and amazing things.

Here’s where it gets cooler: If PaSH catches on, we could soon see similar libraries for MySQL or Apache. Maybe someone will even write a layer that maps common Windows and SQL Server functionality to their open source equivalents. A heterogenous datacenter becomes a lot easier to manage when you can write maintenance scripts using a common language across all platforms.

Looking forward, this isn’t so far fetched. The open source community has embraced mono, the open source version of the .NET Framework – I have mono and MonoDevelop (the mono IDE) installed on my mac. It’s included in several Linux distributions by default. It isn’t that much of a stretch to imagine PaSH becoming an effective and accepted cross-platform administrative scripting language.

What’s my biggest weakness apart from a fondness for car keys and the gas pedal?

Well, there are two glaring flaws that come to mind. One is Brent’s raging flaw – if I don’t see myself as having any chance of being better than just about everyone, I don’t have a lot of interest in doing something.

As for my second flaw: if I’m going to do something and do it well, I’m also going to study that subject as if it were an academic subject. If I’m going to do it and I’m going to do it well, I’m going to understand why I’m doing it, how it works under the hood, and why it works that way. I will pursue the academic theory behind a subject thoroughly, sometimes far more than is necessary.

I love music. Over the course of my life, I have taken lessons in piano, guitar, and the french horn. I have taught myself the fundamentals of multiple percussion instruments, clarinet, and the fife. I’ve studied music theory formally in music classes and lessons and informally on my own. I’ve experimented with a variety of techniques to produce the sounds I want and have even taken apart and reassembled various musical instruments to gain a better understanding of their inner workings. Sounds like someone who likes music, right? Well, here’s the catch: I approached all of these instruments methodically, even though I wasn’t fully aware of it at the time. Ultimately, music became a passionate hobby: I knew that I would never be a great musician or composer. I didn’t have the drive or talent.

When I went to college I got a degree in English. I’ve always loved the written word. Rather than focus on analyzing literature, I studied the craft of writing. I read every book we were assigned on writing multiple times. I analyzed my favorite authors’ writing styles. I pared down my writing and built it up in other styles. I challenged myself to write about subjects I had never written about. I studied writing the way that many people study math or science.

I’m very lucky that I’m in a career where I can go beyond my immediate tasks, look inside, and find the theory that makes it all tick. It sometimes takes a good tug to pull me back from the theory to focus on the practical. Thankfully, so much of database development is based on a thorough understanding of theory that it’s okay to go off on a tangent.

Who is next in this game of tag? Matt Nowack, and Joe Webb.