14 Sep Big Mac Index
So I heard about the upcoming T-SQL Tuesday from the Database Whisperer himself and decided that I would take the time to participate in the discussion for the first time. That’s right, I have never taken part in a T-SQL Tuesday prior to this post. Why now? Because of the Big Mac, that’s why.
I am certain that the McDonald’s Corporation is the only hope we have for world peace. Remember how in Demolition Man all the restaurants were Taco Bells? That’s how I knew the movie was fiction and not a documentary, otherwise they would have been eating under the Golden Arches every night. And a big part of the globalization process is what we will all use for currency in the future land run by Mayor McCheese.
Enter the Big Mac Index which is already paving the way for a global currency of beef patties, special sauce, lettuce, cheese, pickles, onions, and sesame seed buns. If we want to create peace, then we need to end hunger. Big Macs will do that. But this post needs to tie into T-SQL somehow. So let’s get down to it.
Let’s examine all the levels of a Big Mac. You’ve got a bun, special sauce, lettuce, cheese, beef-like patty substance, reconstituted onions, more bun, more special sauce, more lettuce, pickles, another beef-like patty substance, more reconstituted onions, and the top of the bun with sesame seeds embedded just to make sure you have something stuck in your teeth when the meal is over. That’s a lot of stuff shoved together to make one sandwich. As coincidence would have it that mess is just like your rows of data, piled on top of each other and if you are lucky then the rows are unique but most often you end up with duplicate rows because you just hired some kid in high school to work your counter and design databases at the same time (don’t look at me, I didn’t write that job description).
Now, here is my question to you: If you were given a Big Mac for the first time and had to go looking for the special sauce because you are allergic to mayonnaise, how would you search for it? Would you know exactly where to look immediately or would you need to do a full burger scan and inspect every part of the sandwich? And why don’t my Big Macs ever come with bacon?
And what if Peter Pepper in the back decided to make the Big Mac in a slightly different way, maybe the special sauce went on top of the lettuce instead of underneath? The only way you would know where to find the exact location of that sweet sauce nectar is if you were handed a map of the burger created right after it was made. Armed with that map you would know exactly where to look which saves you time. Time that you can then use to savor your McFlurry for dessert.
Guess what? That map is your index. I know, right? It’s really that simple. That map is the same as an index on a table in your database. Unfortunately neither that map nor a database index smells as wonderful as a Big Mac. It does, however, help me to visualize what is happening under the covers of the database engine. I am guessing it could help others as well, so that is why I have opened the following Connect item. I want Microsoft to consider introducing the CREATE BIG MAC INDEX syntax in SQL 11 since most people can picture a Big Mac a lot easier than a B-tree. If you agree with me then go vote up that item on Connect.
So, the following syntax is an example:
CREATE BIG MAC INDEX IX_BeefPatty_Lettuce_Cheese ON SesameSeedBun (SpecialSauce)
and then you would be able to find the location of the special sauce faster.
Now, what if I wanted to know the location of the special sauce as well as the pickles? Well, I could add pickles to my index, that would be easy enough. But there are going to be times when you don’t want to add more ingredients to your Big Mac index. Why? Great question. Any other questions?
Think of it this way. Let’s say the Peter Pepper has already drawn you a map of your Big Mac and he has the Hamburglar pointing to the exact location of the special sauce. Now you also want to know where the pickles are, so you have Peter come out of the kitchen and this time he draws a picture (on the same map) of Grimace pointing to the pickles. So now you know the exact location of both the special sauce and the pickles and everyone is happy, right?
Well, not Peter. He’s pissed because in the time it took him to draw those pictures for you the french fries burned and he has to start a whole new batch and now there’s a huge line of people waiting for fries to complete their orders. Nobody is happy except for you and your pickles. This could have been avoided if Peter had included all of the necessary information in his first map but he had no idea that you wanted to know about the pickles so maybe next time you could communicate that to him in advance if you don’t mind. Would it kill you to tell the man what you want when you place your order? Now, there very well could be times when you tell Peter “that first map is nice, but what I really want is a new map”, but I wouldn’t do this if there is a line at the counter.
And that’s why I love the Big Mac index more than any other type of index. In an related note, I’m also very hungry.
SQL 11? You know, if I took those 1’s, put them on their side, and bent them downwards…they would look just like the Golden Arches.