Posted in SQL Server

T-SQL Tuesday #137 – Using Notebooks Every Day

Happy T-SQL Tuesday! Thanks to Steve Jones (t|b) for hosting this month. (And thanks for all you do for coordinating the T-SQL Tuesdays!) If you’re interested, you can read the whole invite here.

Our challenge this month is:

“For this month’s T-SQL Tuesday, I want you to write about how you have used, or would like to use, a Jupyter notebook.”

I love the potential I see with notebooks. I love the blend of documentation and code. I love the fact that you can save the results with the query so you can easily capture the before/after results and see them in the same document. When I can use notebooks for a blog post or a presentation, I do it because I like what I see so far.

But I think the key is in the second part of the paragraph where Steve gave us his challenge:

“This seemed to be exciting for many people at first, but I haven’t seen a lot of uptake from users in general. So I’m curious if you are using them.”

On a daily basis, I do use Azure Data Studio along with Management Studio. But I find I’m using ADS more for Git\Source Control (which is a total surprise to me) and using the PowerShell terminal to test my scripts that I update as needed in the query window. I’ve been playing a little more with the explorer window too. But I’m not able to really incorporate SQL notebooks on a daily basis.

I do have a couple of notebooks that I have created for myself. I’ve surprised myself in another way because those notebooks tend to be for PowerShell scripts. These are for occasional tasks where I need to check for files in a directory and either return the list that meets a given criteria or perform some other action with them, like copy them to a different folder.

I’ve noticed that my structure for these notebooks have a standard set up:

  • Identify the variables that I need to change and set those in the initial code cells.
  • Create code cells that use those variables for the different sets.  

I almost always need to run the first cell to set the variables. I may not need to run all of the other code cells but just one or two. Each code cell isn’t a large amount of code but enough to get each task done. And since they’re notebooks, I can add lots of comments about what I’m expecting each code cell to do so if I do end up sharing these, they can help my coworker figure out what I’m doing.

The other thing you’ll notice is that these are manually run scripts. I don’t need these notebooks run at a regular interval for the same set of data. I almost always have to modify the variables before I run the scripts. There’s a lot of little things that need to be looked at so I can’t automate the work. What this does allow me to do is accomplish tasks much quicker with hopefully fewer mistakes or missed files. There’s the element of repeatable but not automated. (Is “manual repeatable” a valid category?)

In reality, these notebooks allow me to keep a collection of similar scripts together and documented. It doesn’t make sense to turn these into functions or generic scripts that I open in Notepad++ or some other text editor. Plus the ability to pick and choose what I need to run and to document what each bit of code does makes notebooks the right solution for these scripts.

There are still some things that I think are missing from notebooks that would help database developers adopt them at a higher rate. (I’m looking at you, include execution plans!) But I still think they have a lot of promise. I don’t think there’s a hard learning curve with notebooks. I think it’s been hard (for me, at least) to find the right every day problem where notebooks are the solution.

Looking forward to see how others are using\not using notebooks. Thanks again for hosting, Steve!

2 thoughts on “T-SQL Tuesday #137 – Using Notebooks Every Day

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s