Knowledge Sharing with SQL Notebooks

SQL Notebooks, based on Jupyter Notebooks, is one of the best things about Azure Data Studio. In this article, Edward Pollack explains how to get the most out of SQL Notebooks.

Whether presenting at a conference, to colleagues, or to an academic audience, choosing the best tool to share knowledge can be a challenge. PowerPoint is an effective presentation tool so long as the contents are static and there is no need for demonstrations or live feedback.

SQL Notebooks, a component of Azure Data Studio, is an excellent tool to add to your presentation arsenal. It allows you to merge markup, text, and code from a wide variety of languages in a single document. In addition to presentation, it can also be a powerful tool for documentation.

This article will introduce SQL Notebooks and discuss the various ways this tool can be used to improve knowledge sharing across any organization.

What are SQL Notebooks?

Azure Data Studio is a management tool provided by Microsoft that can be used to manage both on-premises SQL Servers and cloud-based servers. When compared to other management tools, it has a more modern interface, excellent support for extensions, and a more rigorous update schedule.

SQL Notebooks is a tool that is included in Azure Data Studio and is a Microsoft implementation of Jupyter Notebooks. A notebook can contain blocks of either code or text.

Text may include raw text, text formatted with markdown, equations, HTML, and more. This allows great flexibility in how to display text, as well as the ability to embed all sorts of images, videos, and other content into a presentation.

There is a wide variety of code that can be executed within SQL Notebooks, including T-SQL, PowerShell, Python, Spark, and more! Extensions are available that add additional functionality for other services and languages.

A key feature of Azure Data Studio is that updates are issued regularly. These updates often contain enhancements and new features, in addition to the typical bug fixes that are expected of regular updates. This ensures that SQL Notebooks is a product that gets better with time as Microsoft implements improvements.

A full list of release notes can be found here.

Using SQL Notebooks

Once Azure Data Studio is open, you can create a new notebook from the main menu, like this:

From here, there is a binary option to add either code or text:

To create an introduction, you could insert some text that introduces a topic and provides a basic framework for whatever you want to present:

Note that you will type markup in the top portion of the text box and the output as it will look in the Notebook appears on the bottom. This provides immediate feedback on fonts, colors, layout, and other presentation details without the need to switch screens or load the document into a browser/viewer.

There are a variety of conventions for markup that are useful to note. Each of these will be demonstrated in SQL Notebooks below.

  1. Headings are denoted with hash symbols. One hash (#) denotes heading 1 (the largest) and six hashes (######) indicates heading 6, the smallest heading allowed. A space needs to be included in between the hash symbols and subsequent text.
  2. HTML Markup and tags are supported. Most HTML conventions for formatting text, inserting images, files, and other content are supported.
  3. Bullets can be used by placing a dash (-) at the start of a line, followed by a space. Similarly, starting a line with 1. begins a numbered list. The list will indent and display as a distinct object within the markup, but the user needs to continue numbering with each new line to maintain that structure.
  4. Three asterisks (***) will create a horizontal line.
  5. URLs will automatically highlight and be clickable in the notebook.

The following illustrates a variety of these concepts in a short text block:

Note that the end result looks and feels like a web page, which allows text to be used effectively for both presentation and documentation.

A variety of code can be inserted into SQL Notebooks as well. When you choose a code block, you can adjust the language like this:

Once selected, you can begin writing and testing code as you usually would:

The kernel selection affects all code blocks, therefore, to change languages mid-demo requires changing the kernel. Test this ahead of time as it may take a few moments to make the switch.

T-SQL written in SQL Notebooks will benefit for the familiar color-coding, as well as IntelliSense, and will therefore look quite similar to what we would compose in a more traditional IDE.

When the play button on the top-left is clicked, the query will execute against whatever server the session is attached to:

The ability to mix code and markup is the big selling point of SQL Notebooks and can be a huge time-saver when presenting or attempting to document a system where lots of demos are needed. Whether you comment the code or create markup to reference it above is a matter of style and will vary depending on the complexity of the code you are working with.

A few options at the top of the window allow you to control the current display of code and results:

  • Run Cells will execute code for all code blocks.
  • Clear Results will remove the result grid from all code blocks.
  • Collapse Cells will reduce all code blocks to a single line with an arrow to expand as needed.

These options are useful while sharing as they allow you to remove noise from your presentation. They also offer a clean starting point with minimal clutter.

The following is a simple example of some code written in Python to calculate the area of a triangle:

The ability to mix and match different types of code into a single notebook is a huge time-saver, as well as a way to greatly reduce the need to switch between presentation, documentation, and multiple IDEs.

What are SQL Notebooks Useful For?

Live Presentation

Bridging the gap between presentation and demo allows more seamless documentation as it removes the need for the frequent context switching. While one can get used to switching windows to skip between presentation, documents, and demo, there are notable downsides to this approach that wear on even the most seasoned presenter:

  • Time is wasted switching applications and refocusing on a new screen.
  • When in full screen mode, A/V hardware or monitor-switching software may not behave as expected, resulting in downtime or confusion. This has happened to me before, and it is painful!
  • Each additional application results in greater presentation complexity and more things to remember. Many presenters write notes to themselves in comments, or even write documentation for their presentation to follow along with like a script.

Overcoming these challenges often results in compromises as it becomes necessary to add more components to a presentation that are used solely for the purpose of minimizing the impact of a hectic interface.

Presenting in SQL Notebooks allows everything to be in one place, regardless of what languages you are working in. It’s possible to mix text, markup, PowerShell, Python, and T-SQL all in a single window. This greatly simplifies a presentation when there is only a single window to worry about and at worst, maybe multiple tabs.

Documentation

The format of a SQL Notebook resembles the feel of a web page or document. Being able to articulate markup alongside code makes it far easier to document a process or create a how-to guide for an important procedure.

This is a relatively simple example, but it illustrates the value of embedding code into a how-to doc. The reader can copy and use the code as needed or run it inline within the notebook. This is especially useful when the code is more complex or when it changes often and maintaining central documentation is important. Version controlling a SQL Notebook in GIT or another source control app is also a valid way to track documentation changes over time, if this fits into your version control infrastructure.

One big benefit of using SQL Notebooks for documentation purposes is that it allows documentation and development to be centralized together. Using the same app for both purposes can be a time-saver as there is no longer a need to duplicate one’s work into a separate app. Eliminating redundancy can also reduce maintenance costs as the process to update documentation changes from “Update source and update documentation” to “Update source”.

Consider the following example, where a stored procedure change is documented within its own release script:

While the example is simple, it illustrates how documentation and software development can merge when needed to ensure that future developers know how and why a change took place.

Problem Solving

SQL Notebooks are great tools for sharing and solving challenging problems. Code and notes can easily be shared between individuals or teams for review and revision. This avoids the challenge of sharing commented code, which tends to be harder to read and understand without context or more familiarity.

There are many ways that SQL Notebooks can make useful brainstorming and problem-solving tools, including:

  • Ability to include demos and examples of similar behavior or desired results.
  • Images can be included, allowing for additional experiences to be shared.
  • Text, markup, and hyperlinks allow content to be shared, linked, and referenced.
  • Comments and notes can be added to recent or notable changes.

This allows for the ability to quickly state a problem and then build upon it over time, until a resolution is reached. As a bonus, the problem-solving process can become part of the documentation that explains the end-result.

SQL Notebooks is not a well-developed IDE, and therefore is not ideal for developing intricate code, performance testing, or running in-depth analysis. For development purposes, continue to use your preferred IDE.

There is utility in developing in Azure Data Studio and having SQL Notebooks available in the same window. Being able to swap between a notebook and in-progress code is convenient, fast, and is often a better alternative to switching applications for documentation or collaboration.

All of the Above

The best use of SQL Notebooks is combining different use-cases into one. When a single notebook can be developed and used for a presentation and then the subsequent documentation or collaboration afterwards, time and effort are saved. In addition, a SQL Notebook is far easier to follow than a stack of scripts, code, and PowerPoint slideshows.

Sharing a notebook is easy, and they self-document far better than code alone.

Extensions

Azure Data Studio supports and encourages extensions to be created and used. This allows Microsoft to add optional features as needed, but also for third parties to develop and add features that enhance Azure Data Studio and any associated components, such as SQL Notebooks. Adding extensions is easy as there is a built-in system that manages the extensions you have and can install:

The bottom button on the left navigational bar reveals a list of extensions. From there, you can search, learn more about interesting extensions, and install ones you’d like using the installation button provided with the extension. This makes adding new extensions exceptionally fast when compared to other Microsoft IDEs, such as Visual Studio or SQL Server Management Studio.

Clicking on the gear next to an installed extension displays a variety of options that include disabling the extension or uninstalling it.

SQL Notebooks vs. Jupyter Notebooks

This discussion would be incomplete with some comparisons between the various tools that are out there. Jupyter Notebooks is the product that SQL Notebooks is modelled after and therefore deserves some attention.

On the whole, the native Jupyter Notebooks application has far more functionality built into it. As a product, it has existed longer and has had more time to grow and gain new features.

Its presentation mode allows a notebook to be played back in a similar fashion to a PowerPoint slide show. This is likely the biggest feature that SQL Notebooks does not have implemented yet, and that I will continue to be anxiously awaiting.

On the flip side, Jupyter Notebooks is not a Microsoft implementation and is therefore not SQL Server-centric or Windows-centric. As a result, connecting to servers and configuring the app to work with SQL Server in the same fashion as SQL Notebooks does require some amount of effort before you can begin authoring notebooks.

After hours of testing and playing around in Jupyter Notebooks, I concluded that using it for SQL Server and Windows-based code was not worth the effort. Instead of providing screenshots and a side-by-side comparison, it made more sense to leave this topic with the knowledge that for most SQL Server professionals, SQL Notebooks will provide a far smoother experience. As a native Microsoft app (and one that is updated often), you can be safe in knowing that the SQL Notebooks app is a solid way to present and document the languages it supports and will only get better with time.

SQL Notebooks Scripting vs. Azure Data Studio Scripting

While SQL Notebooks supports any code that is natively supported by SQL Server, the features of the IDE are exceptionally limited. Within a notebook you may add and execute code, but beyond that there is very little functionality that typically is found in Azure Data Studio.

You can use any output-based features to display query metrics and get feedback, such as STATISTICS IO or STATISTICS TIME. Similarly, row counts and execution time are returned by default:

Execution plans and other XML output is not available. In addition, some plugins for Azure Data Studio apply to editing T-SQL within a *.sql file and not within SQL Notebook. As a result, object exploration is not the same as it is in other tools and code validation beyond basic syntax is not available. Right-clicking on any object within a code block provides options to view the object’s definition or to format the T-SQL.

Scripting in SQL Notebooks is primarily for sharing and not for troubleshooting, whereas performance testing or in-depth analysis are better done in Azure Data Studio or SQL Server Management Studio.

If you want to demo execution plans or features not included in SQL Notebooks, then opening a query window alongside the notebook is a relatively painless way to manage this scenario:

Once in a native query tab, click Explain to get an estimated execution plan. Since both tabs are in Azure Data Studio, swapping back and forth is nowhere near as disruptive as switching between different applications.

In general, SQL Notebooks is great for sharing, presenting, and documenting code, but leave the in-depth analysis to a native SQL editor, as there will be far more functionality available and development will generally be faster.

Azure Data Studio vs. SQL Server Management Studio

This is likely the biggest question asked by SQL Server professionals. Is Azure Data Studio worth the switch? Is SQL Notebooks worth the effort to install, configure, and use Azure Data Studio.

The answer mostly depends on how invested you personally are in SQL Server Management Studio. If you have many plugins configured in SSMS and a highly customized configuration, then switching will be challenging. Similarly, if you use many features of SSMS, such as live query stats or extended events management, then you may find them missing or lacking in Azure Data Studio.

Alternatively, if you use SQL Server Management Studio as primarily a code editor, then the switch should be easy and will likely represent an improvement to your coding quality of life. In addition, you can directly integrate Azure Data Studio with a source control app, which allows for native change tracking for any schema you are developing. For example, you can integrate a Git repository into Azure Data Studio, and then run Git commands from within the app, like this:

This is a nice addition and allows you to manage source and code in one place!

Long-term, Azure Data Studio is expected to eclipse SQL Server Management Studio in terms of features and usability. New extensions, features, and the addition of missing functionality will eventually make the switch very worthwhile.

What am I Doing?

SQL Notebooks has been a worthwhile addition to my presentation arsenal. When I can use it effectively, I do. Being able to present everything in a single window is a huge convenience and makes the flow of presentations far less awkward. Alt-Tab has worn on me over the years and flipping between 2 or more windows repeatedly has always felt like a hassle.

Using SQL Notebooks for more casual small-group presentations has consistently been a win. While live demos are risky and typing in demos doubly so, SQL Notebooks makes it far easier to manage code and presentation in a way that makes demos easier to create, test, and execute reliably each time.

Sharing a SQL Notebook after a presentation is easy and eliminates the need to share multiple disjointed files with viewers that may or may not be very familiar with your code. I far prefer providing a single notebook file, rather than a pile of PowerPoint slideshows, SQL files, documents, readme files, notes, etc.

On the Azure Data Studio side, I am still in the middle. Some missing features and extensions are painful and lead me back to SQL Server Management Studio. More often than not, I can code, troubleshoot, and test in Azure Data Studio without issues. Overall, I love SQL Notebooks and their counterpart, Jupyter Notebooks. I am confident that they will continue to make my presentation life easier in the future as new features are added and existing features enhanced.

Conclusion

SQL Notebooks is a great feature addition in Azure Data Studio that provides an excellent alternative to presentation, documentation, and overall knowledge-sharing. While it is still a somewhat new product, the value provided by it is high, and increases as updates and extensions are added.

For any technical professional that presents, shares, or documents often, this tool is worth testing and experimenting with. It has the potential to reduce the number of apps needed to present and improve the reliability of those presentations by keeping everything contained in a single window and in as few files as possible.