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.
The post Why I Use AdventureWorks for Demos appeared first on Grant Fritchey.
