I know that when some people see AdventureWorks, their vision turns all red around the edges, their blood pressure spikes and they begin to foam at the mouth. I do understand. AdventureWorks, Microsoft’s very old, near ubiquitous, sample database suffers from a lot off issues.
What’s The Matter With AdventureWorks?
Let’s start off with the general design of the schemas. It’s broken up in odd ways. Production has a TransactionHistory and a TransactionHistoryArchive instead of Sales? That doesn’t make any sense.
What the heck is up with the general database design anyway? I mean, pick on one example. A person can have more than one phone number. Fine. But, what about people who share phone numbers like that ancient land line in my house that is only EVER used by my mother-in-law and telemarketers. Oh, then we violate all the rules of normalization and clean data by putting in that number multiple times. There are plenty of other examples.
Hey, what about that terrific naming standard? Don’t you love it when you name a column named “SalesOrderNumber” but the data stored inside of it is nvarchar(25). Oh yeah, I would, of course, know that Number in this case means some weird string put together through a calculation.
Speaking of calculated column, they got the data types wrong so that every single time, every time, I refer to that darn thing in queries, I get false alarms as warnings in my execution plans.
Finally, it’s so small. It’s just tiny. The biggest table has 100,000 rows. That could barely work well in a columnstore index. There just needs to be more data.
About that data. The distribution across tables is frightfully odd. Some values returning huge numbers, other returning small values.
So, Why Do You Use It?
First and foremost, it’s very well published and very well documented. That means that anyone with the most rudimentary Bingle-Fu can track down a copy of it. There are 10s of thousands of examples, and I don’t just mean mine. They’re everywhere. This makes it a great tool for people to reference.
Yeah, the size is an issue, no lie. However, there are a couple of scripts out there that can pump it up in size. I’m going to be using Adam Machanic’s Thinking Big (Adventure) for some work I’m doing (side note: and I’ll be giving him full attribution as one should when borrowing others work). However, keeping it a little smaller does make it easier to transport and download.
All that said, I hear you. It’s a creepy weird database design with horrible data distribution, bad indexing, scary naming standards, poor index choices and some frightful coding decisions.
Just like your databases.
Oh, I know, not all your databases. Not the ones that you got to build your way without any compromises or drift or technical debt. All… how many times do you have zero compromise, drift or bad choices? Personally, I’ve pretty much always compromised and I have far too frequently screwed things up. So yeah, my databases, the ones my fellow DBAs build, the ones from the dev teams, the one that found it’s way from someone’s Access database (for crying out loud). They’re a mess. Just like AdventureWorks.
Conclusion
So, why do I, and lots of other people, use AdventureWorks, even though it is grossly compromised? Because so is the rest of the reality we have to deal with. AdventureWorks is a wonderful sample database because of it’s flaws (except size, that bit is an issue). Since I happen to know at least one of the people who built the original, they designed it that way. Thanks everyone involved.
LONG LIVE ADVENTUREWORKS!
TechOutbound is an amazing opportunity to spend intense, quality time with a few industry experts. In this case, on a cruise ship. I’ll be teaching several courses there. While it sells out fast, there’s still some room for the week of February 10, 2018. Go here to sign up. If you want to, you don’t have to, use the registration code BFFGF to save $100.
Also, AdventureWorksDW2016 has big tables..
But Grant, don’t you work for a company that publishes a tool that can help with the sample size? I’m sure somebody at that company would be willing to create a sample project to create loads of data inside AdventureWorks for use with their tool.
Yeah, I’ve used SQL Data Generator to bloat AdventureWorks too. I just like how Adam has done the work and it makes it easier to share with others if I just point them at a script.
My demos don’t tend to require a database like Adventureworks, but I always assumed many (not necessarily all) of the flaws were intentional, expressly so that demos COULD be built around them. I mean if all the indexes are already good, how can you effectively show how to improve an index.
Exactly. I really like the work done on the new demo database, WorldWideImporters. However, it’s too good. They designed it properly. They normalized it properly. It has very even data distribution. It’s just not that workable for me.
That would explain why the currency conversion is done all wrong. So we can fix it 🙂
Ha! Exactly.
Back when I was training folks the military way, one principle was to always made it clear when we were demonstrating how to *not* do something.
I wonder how many developers used AdventureWorks as the model they mimicked because they thought that a Microsoft-provided example implicitly represented Microsoft’s version of how to design a database.
That is an excellent point.
Sorry, I’m late in responding. For some reason my blog isn’t letting me know that people have left comments.
[…] go down that road, a quick note on methodology. First, I’ll be using Adventureworks because, reasons. Second, I won’t run any of the following queries once. When doing something like this, […]
[…] a speedy take note on methodology. Initially, I’ll be using Adventureworks for the reason that, reasons. Next, I won’t run any of the subsequent queries the moment. When undertaking some thing like […]
Nothing in this article leads us to believe that the blogger knew this db is an inverse role model.