Azure Data Studio (ADS) is a lightweight IDE built on Visual Studio Code. I've written a few articles on how the tool works, and I've got a few more coming. I covered a basic orientation and a more in depth one on some of the code writing features. In this one, I wanted to cover a very interesting feature that was added to the March 2019 release of ADS. You can download the tool and read those articles to learn about how to use this in writing code. I've gotten more comfortable with the tool, and while I still like SSMS, there are some features in ADS that I prefer.
I've written a few articles on how ADS works, shown here:
- Getting Started with Azure Data Studio
- Getting Comfortable Writing Code in Azure Data Studio
- Using Notebooks in Azure Data Studio
- Using Azure Data Studio with TFVC
- Using Azure Data Studio with Git
- Editor Tips and Tricks for Azure Data Studio
What is a Notebook?
I've been using notebooks for a few years in different parts of my work. I've learned from Python and Jupyter Notebooks are a way of capturing a number of different code items with text and sharing the information with others. I've actually been doing a little work with Azure Notebooks and have written a few blogs. Not all of my notebooks are public, but I do like them in terms of a way to structure the flow of some concept. I've used them to experiment and document how things work, allowing me to easily follow along again.
Notebooks are an open data format, essentially a an application that you use to write some document, but they contain live code elements that allow you to embed programs, images, results, and more inside of them. It's really a fantastic tool for teaching or sharing information between people, which is something I've struggled to do with other DBAs and developers. A notebook makes it easy to link lots of docs and code together.
When I first heard that we were getting T-SQL notebooks, I was excited. This is a technology that ought to have been embedded in Word, Excel, and other places in the past, but it hasn't been. The past is the past, and we can now move forward.
Getting Started with Notebooks
You need a version of Azure Data Studio that is 1.5.1 or later. This is the March 2019 release and contains notebooks with both T-SQL and PGSQL capabilities. I'll only cover T-SQL, but I assume things work similarly with PostgreSQL.
We can create a notebook just like we create any other object. We use File | New and select New Notebook. This is a first class object like a file or other item in ADS.
When the notebook opens, there's nothing to see, but a little guidance on how to begin. ADS lets you know that you can add code or text to a cell, whatever that is. A cell is essentially a section of your notebook document. You can interleave code and text, but they are separate elements that format in whatever order you've placed them. You can click on either of the words, but in the menu bar at the top there are also buttons for adding text or code cells.
Let's start with code, because many of us write code. I'll click the "+ Code" link in the notebook. This will give me the view below, in which I can now write T-SQL code.
This is a code block like in any other query, but it's self contained. It's as if I have a tab embedded in my notebook that just runs the code inside this rectangular blog. Notice there is a "Run" button with a triangle on the left. I have a line number, which grows as I add lines. I also have an ellipsis (three dots) on the right. There are other items in here, but let's write some code. I'll add a couple simple lines of code so that you can see how this works.
I have multiple code lines in here, two queries to run. If I click the black button with the triangle (Run), nothing happens. Why? This is because the notebook isn't connected to any server. I can't run code without a SQL Server instance, so I'll need to connect to something. If I click the drop down that says "Select connection", I get a choice. I haven't added anything to this notebook, so I just have "Add new connection".
If I select that, the ADS connection dialog opens. I can put in my information and make a connection. This is a fairly standard connection dialog.
Once that's done, I can now click the Run button in my notebook and get results. The format is fairly standard ADS, but embedded in the cell itself, which you can see as grown a bit.
Notebook ResultsWe have code, let's now add some text and see how rich the notebook can be. My notebook is a set size of cells now, so we need to add a cell in one of two ways.
If we want to add a cell to the end, we can click buttons in the toolbar (the +code or +text ones). This will create the specified cell. If we want a cell inside the notebook, we can click the ellipsis in the upper right and get a menu that allows us to add cells before or after this one. The "markdown" item is a text cell.
I'll click the item select and I get a new cell in my notebook. I can enter any text in here, but I need to use markdown, which is a form of noting format inside the text. You can see below I've entered some text and included the hash (#) as markdown to denote a title. This is actually an H1 title. I could use two hashes for H2. The markdown reference I use is here: Markdown cheatsheer from adam-p.
This is the editing view. Notice that my code window is now a [1] window denoting the first section of text. It also doesn't have a run button because the code and results are part of the notebook. If I click in a part of ADS outside of the text cell, I now see both text and code together as one item.
If I want to change something in text, I double click inside the cell and I get editing capabilities. If I mouse over the code cell, I see the run button and can re-run the code.
I'll add another text cell and a code cell and then execute the code cell. The results I have shown below are the bottom of the results above with the new text cell (using a H2 header) and a code cell. Note that the code cell has a USE statement in it. Once I'm connected to an instance, I'm connected. The connection at the top of ADS is actually to the Sandbox database, but I can change in code like any other query window.
Saving the Notebook
I can click Save (or Save As) for a notebook like any other object. When I do this, the notebook actually saves the text, the code, and the results. This means anyone else can open the notebook and see the results. If I look at this small notebook when I run the queries, I can see this is about 2.5MB. The basic notebook I saved after this has two lines of text and the first query, but no results.
If I execute the first batch of queries (metadata and sp_who2), the notebook size balloons to 81kb. This is something you want to be careful about when sharing notebooks. Any results are saved in the notebook, and they are going to take space.
There's a way to reduce the size of the notebook. If I click the ellipsis in the upper right of a code window, there is one other result I can choose: clear output.
If I clear the output from both of my code blocks, I get this notebook.
If I now save this, the size is 3kb.
Keeping just the text and code can save a lot of space in the file, but still ensure the notebook is a useful way of capturing and organizing information.
Building a Troubleshooter
One of the great ways that notebooks can help many of us in our everyday work is by providing some organization and code to help us work with a series of queries and data. As an example, I'll show a variation of Gianluca Sartori's post on creating a notebook. The post uses PowerShell to assemble a notebook, but I'll just add a few items manually.
I've added a few queries to a notebook and included some information from Glenn's scripts. The basic notebook looks like this:
I've also set this as a read only file in the file system. This ensures that when someone opens this notebook, they can't save results back to the same file. What I want to do is have a user do a save as and create a new file for this execution of the notebook.
Now when a user attempts to save the results of their queries, they'll get this message.
By having users save this to a new file, we can track the execution of data in this file and potentially compare the results. If I open the notebook and run the first query, I'll have results. I can save that as a new file (in this case, GlennDiagnosticQueries_20190408.ipynb). I then use the Editor Layout to split the screen and open the first file again. I'll connect to a new instance with the Add New Connection selection in the drop down and run the first query again. I then see this. Note that the notebook on the left is the wrong version in the results.
I could connect to the same instance and compare results at two different times, or connect to difference instances and compare results in this way.
Other Kernels
The notebook is a generic construct. Most generic Jupyter notebooks run Python by connecting to the python runtime on a system and executing code as though it were in a REPL. With the direction of SQL Server 2019. there are other kernel choices in ADS, with T-SQL being the other one we've looked at earlier in this article. If you click the dropdown in your notebook, you can see there are plenty of other choices.
I haven't tried to work with the Spark items, but I will cover Python in another article.
Conclusion
The use of notebooks opens up a number of possibilities for information sharing using SQL. The interactive nature of a document that can connect to an instance and run queries is a valuable tool for teams. Certainly I'll use this in presentations and to distribute code, but the idea of having a document for DR, troubleshooting, or more is a great use as well.
I've scratched the surface of how to create a notebook and interleave both code and text together. If you are interested, learn more about markdown and add rich images, links, and more to your notebooks to share with others.
If you have thoughts on notebooks or are curious how these work with other features, please let me know.