This month’s T-SQL Tuesday asks us to explain a concept like the audience like they are 5 years old. I’ve decided to literally do that.

Do you like jelly beans? I like jelly beans. Imagine if you had a big, big bowl of jelly beans.

How many jelly beans do you have? Can you tell by looking? I think you would have to count and it would take a very long time. Well, computers are good at counting! We could teach a computer to count our jelly beans and tell us how many Jelly beans we have. Using a program called Power BI, the computer can tell us we have 800 jelly beans! That’s a lot of jelly beans.

Do you have a favorite flavor of jelly beans? I really like the pink ones that taste like bubble gum.

What if we could tell the computer to count by flavor? We could then see how many we have total and how many by flavor.

Wait a minute! We have a bunch of cinnamon jelly beans, ew. I can’t stand cinnamon jelly beans. I will probably give them to my mom. I want to hide the cinnamon jelly beans, to see how many tasty ones I have. We can add a filter to look at specific flavors.

We can even add categories of things, so we don’t have to remember which flavors are good and which flavors are bad.

Oh good, we have 700 tasty flavors and 100 gross flavors. Okay, I want you to imagine one more thing. Imagine instead of one bowl of jelly beans, you had 100 jelly bean dispensers and you owned a whole jelly bean store!

Well now we *really* need a computer to count everything. We want to know when a dispenser is empty. We want to know which flavors are most popular. Some people actually like cinnamon jelly beans. Gross! But if you own a store, you have to buy and sell the flavors people want.

Power BI is a program that helps business owners count everything going on in their stores. It helps them count everything but also let’s them look at little details like a single flavor or a single dispenser. Power BI helps store owners make more money and sell people’s favorite candy!

When I first heard about SQL on Linux back in the beta days, I really didn’t see the point. Was this an attempt to steal customers away from Oracle? Was this intended to appeal to Postgres and MySQL users who were used to $0 licensing? Were that many organizations that were fully Linux except for the one SQL server?

And then as I learned more about devops and containers, it started to click for me. And while I’m still fumbling with devops, I’ve found one really good use for Linux and containers: demos.

Specifically, making a presentation on SQL Server 2019 is what convinced me. I presented on it back when it was still called vNext. Then 5 months later I was presenting it again. But now I had to uninstall it and reinstall the latest version. And I have to find whatever virtual machine I had installed it on in the first place. I had a virtual machine because I didn’t want to contaminate my host laptop.

But now, I can run 5 lines of code or less and suddenly I have the latest CTP ready to present against. All in the time it takes to down the update plus a minute or two. I couldn’t do this without SQL on Linux and Linux containers. So, at least for demos, I’m never going back.

This week’s T-SQL Tuesday invitation is all about puzzles. I’ve got an accidental puzzle that I’ve never quite solved, from one of my demos. I’m sure the answer will be a “Well duh!” moment.

I give presentations on SQL Server execution plans. As part of that, I like to show that if you pull a single row from a heap, it has to read everything. As part of that demo, I try to push everything out of memory by disabling readahead reads, taking a checkpoint, and dropping clean buffers. But for some reason… it never quite works!

IF NOT EXISTS ( SELECT  *
FROM    sys.schemas
WHERE   name = N'Demo' )
EXEC('CREATE SCHEMA [Demo] AUTHORIZATION [dbo]');
GO

IF OBJECT_ID('Adventureworks2014.Demo.Person', 'U') IS NOT NULL
BEGIN
DROP TABLE AdventureWorks2014.Demo.Person;
END

SET STATISTICS IO ON

SELECT *
INTO [AdventureWorks2014].[Demo].[Person]
FROM [AdventureWorks2014].[Person].[Person]

DBCC TRACEON (652,-1);

CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT * FROM [AdventureWorks2014].[Demo].[Person]
WHERE BusinessEntityID = 25

You can see here that it shows 3,808 logical reads, but 5 physical reads.

I’m sure there is some simple way to force it to do all of the physical reads, but I have yet to figure it out. Or it may be that I’m misunderstanding something and physical reads are the only pages used. But when I look at the execution plan, it says it read all of the rows.

I’d love to get an answer to this puzzle. I’m sure it’s something simple.

Update: Andy G. asked if maybe the issue is I’m not using all the rows. Here I tried a heap of a single row, and I get 1 logical read and 0 physical reads.

Table 'Person'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This week’s T-SQL Tuesday is about where you use databases in your personal life. And I have a database I don’t use any more that’s a little happy and a little sad. For the first year of my marriage, I would track every time we played a board game together.

Of course, some may question if it was really a database. We kept the data in Google Sheets. It was ugly data; if we played multiple games in a day, I didn’t always put in all the dates. I didn’t always spell games the same way. I had different entries for which configuration of Star Realms we played, even though it was the same game.

One thing that was really useful was seeing which games Annie kicked my butt at. Or to see which games we played the most. Magic the gathering is in there twice because I did’t always spell it the same way.

After a while, we didn’t play quite as frequently and all the data entry started to wear on me. I even played around with making a PowerApp to make it easier.

In the end though, I stopped keeping track. Maybe at some point I’ll start again. I find it strangely satisfying to have this bizarre log of the first year of my marriage. During that time, we played about 90 distinct games about 220 times. We had a lot of fun and still dedicate 9 PM to 10 PM as our date hour, but will watch movies or play video games too now. And ultimately, we started what has been the best decision of my life, which was getting married.

For this T-SQL Tuesday, Andy Leonard asks us, “What is your why?”, why do you do what you do?

Many of the answers from others are heartfelt and admirable. Mine are not. Most fall under the category of either dumb or dumb luck.

Starcraft and Scholastic

My very first taste of programming was back in first grade or so with LOGO. I thought it was a lot of fun but never did any significant after that. I first got my second taste of programming with Starcraft custom maps. The year was 1999. I know this because my username was eugene11.  I’ll let you do the math. Starcraft had a scripting logic that was basically a simple if/then system.

But the day I became a true programmer was when looking in the back of a scholastic catalog, I found Interplay’s Learn to Program BASIC! The description promised to teach me how to make video games.

It was pretty easy to follow along with the lessons, although I didn’t truly understand what I was doing.  I could make small modifications to the games they provided but didn’t understand half of the logic therein.

In high school I would make video games on my graphing calculator, because I was bored. I also participated in a game jam called PyWeek.

Brothers and ex-girlfriends

When I went to college, I figured I’d either be a programmer or a professor. I went to Penn State beaver because it was close, cheap and my brother graduated there, so I already knew one of the professors.

In my senior year, I needed to take an elective in my major. I took Business intelligence, because the other option didn’t work with my girlfriend’s schedule. Remember when I said some of the reasons were dumb?

Later on I asked the professor for a job. He worked full time as the head of Business Intelligence at Bayer Material Science. He hired me and I worked there for a year. Ultimately, though, it wasn’t a good fit.

My next job was labeled .net/SQL developer. But after I was accepted I realized it was 90% SQL and suddenly I was a DBA and the new BI department. This part falls under the dumb luck, and it was lucky. I was in a role that I could grow into and over the years ended up working as a BI consultant within the company.

While much of the path to BI was an accident, I’ve stayed with it in part because of the strong community and the friendships I’ve made. I really wonder if I’d find a community as strong and giving as #sqlfamily in a different field. But also, if I’m being honest, because the opportunity cost is high and this is a well-paying profession.

Mom and my new adventures

My career for past 7 years has largely been an accident. I’ve always loved computers and thinking, but I can hardly say I chose this field. But back in September, I quit my job to work for myself. I did this to work on my physical health, my mental health, and have more control over my career.

And honestly, why not? Worst case I have a gap year on my resume, and I go back to get a regular job, right?

I’ve written about the lessons learned so far and more recently, I’ve discovered another why. Working for myself allows me the flexibility to take care of my mom. And so why not becomes much more of a why.

The theme for biggest failure of my career, I wrote that I would have automated things more. What was that automation? Nightly ETL with stored procedures, maybe SSIS + BIML if I was smarter. If I was doing the project all over, it’d wouldn’t be called automation it would be called best practices!

So, what project would I definitely call automation that I have done? Well I can only think of one project, and it’s pretty silly. 4 years ago, I was using the Raspberry Pi as my main computer. Over the past few years I’ve tinkered with the Raspberry Pi and I’ve built some dumb BASH scripts to install and configure a new install of the OS.

It’s not fancy or impressive, but it makes my life easier and I’m happy with it. In the future I’d like to do something similar with windows so I can spin up a whole Hyper-v lab without any work.

This month’s T-SQL Tuesday invitation is to write about a project that went horribly wrong. And hoo-boy, do I have a project that comes to mind.

My biggest project was my biggest failure

The biggest project I ever worked on, that I ever led, was also my biggest failure. In many ways it has defined the early trajectory of my career and shaped what I value in my new career.

About 8 months into my previous job, my boss wanted us to move to a different ERP system. This would allow us to consolidate 5-6 different pieces of software into a single piece of software. The theoretical business benefits would be quite significant. No longer would we need to have this awkward patchwork of integrations between different line-of-business applications. And while we eventually did it, I consider it the biggest personal failure of my career. So what went wrong?

The software was built in-house

So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, you run into two issues in that scenario.

First, a bakery is not an independent software vendor. Programming, by definition, is not their core competency. Which means that you may run into fragility or issues that you wouldn’t run into with a commercial piece of software. It also means that there isn’t going to be any documentation on migrating to the software or implementing it. Why would there be. If you built software for one company, why would you create scaffolding to move other companies onto it?

Second, not every business is the same. A lot of the fundamentals are the same, but you will run into many edge cases. We do invoices this way. They do workorders this way. We handle purchase orders this way. They handle inventory that way.

The way that I think about it is like a sea shell. It’s this intricate curve that’s grown over time, organically, to fit that creature. If you just try to fit a different snail or mollusk in that shell, it may not work out.

I was not qualified

This was my second real job ever and I had been working less than 2 years in the industry. I always tell people the jobs said .net / SQL developer, and it turned out to be a lot more SQL and a lot more DBA.

When I started that job, I didn’t know what a stored procedure was. I didn’t know what a view was. 8 months later, when this project started, I had never done any large scale integrations or migrations. At that point I had done some small integrations between pairs of systems, but nothing nearly at this scale.

Add on top of that the need to staff up and add someone to the team. So, about  year in to my new job I was also now a manager.

The software stack was older and different

Because this software had started way back in the Apple II days and grown over time, much of the technology stack was quite old. The application was built on VB6, MySQL, Classic ASP, Crystal Reports 9 and Adobe Flex. This presented challenges in getting it up and running, as well migrating to it.

Migrating data from SQL server is a giant pain. MySQL workbench has an import wizard, which works decently well, but it can be a bit of tedious process. Later I was able to set up linked servers, but that involved looking up strange property settings and forcing char padding on MySQL.

The software made a lot of assumptions

Because it was homegrown, the software made a lot of assumptions about the data. Project numbers under 1000 were reserved for certain pay codes. Half of the columns would crash the software if they had null values. A lot of the tables had to be populated for the application to even run.

So, so, so much of the process was modify a view, migrate the data, see what blows up, repeat. This is a big part of what made the weekly data load so painful. So much of the migration work was sheer trial and error.

Migrating ERP systems requires understanding 2 businesses

Migrating from one ERP system to another in this case require understanding the business that created the software and the business intending to use the software. This is a lot of learning and a lot of weird edge cases. I think I deeply underestimated the sheer complexity of running a business and all the departments therein.

When we deployed, there was a whole chunk of functionality we hadn’t implemented because “certificate of work” meant one thing to use and something totally different to the other company. They whole system updated maintenance cycles based on these certificates, whereas we had assumed they we merely customer deliverables and thus optional.

What would I have done differently?

Ultimately the project took 13 months instead of the estimated 8, and even then that was only because I decided for us to have a hard cutoff at the calendar year. So, if I could travel back in time, what would I have told my younger self?

• Tighten the feedback loop. Migrating the data from one system to another was a largely manual process and quite painful, so we only did it maybe a week. Near the end I had started automating the process. In retrospect, I should have made it a nightly process.
• Learn SSIS and BIML. So much of the pain came from the slow turnaround cycle. I suspect that if I had learned SSIS, I could have made things into a daily or even hourly migration process and saved so much time.
• Read Rapid Development. I wish I had a better idea of what I needed to know from the outset. Rapid Development was a revolutionary book for me and had so much good advice in it.
• Identify a measurable list of use cases. When we made the cut-over, a number of basic things did not work because we hadn’t tested them. What would have been smart would be to have a checklist that we could run through before hand to test the use cases.
• Don’t take it personally. I was young and I treated things outside of my control as my responsibility. I was sore for a long time about how things went, and I thought too much on how that reflected on me and my abilities.

So now what?

Even to this day, I’ve got a certain amount of skittishness around the idea of large projects. There is a whole suite of soft-skills, of methodologies involved in making sure an IT project is successful, and I’ve learned the hard way what happens when you don’t have those skills.

For now, I’m excited to focus on course authoring and projects where I just need to create content.

Finally, I’ll leave you with a favorite quote of mine:

Good judgement comes from experience. Experience comes from bad judgement.