Python has Jupyter, Java has JavaDoc, and C# has XMLDocumentts and SQL has comments in code. When documenting lessons or user guides we rely on MS word. The challenge - they are static and painful to compose. and no one reads them. We tried wiki pages too.. not much different.
SQL Notebooks uses Jupyter - and learning from the success of the tool especially in data science and acadamia, Microsoft added several language kernels, including TSQL. This allows you to write code, document results eliminating the need for taking screenshots that you would normally do.
Documentation of your scripts, especially when they need to be circulated to your team or more important to your non-technical business team to run their own queries, this comes very useful.
For now notebooks are available only on Azure Data Studio (default), Visual Studio Code (extension) and Azure portal.