Amplify your database development experience with Azure Data Studio

It is that time again! We are excited to bring new and exciting updates to Azure Data Studio. These updates include the general availability of the Azure SQL Migration Extension, the introduction of advanced features to the Table Designer feature that was initially released in February, some updates to notebooks, Query Plan Viewer, SQL Projects, and Schema Compare.

Announcing general availability for the Azure SQL migration extension in Azure Data Studio

We are excited to announce the general availability of the Azure SQL Migration extension for Azure Data Studio. Among many other capabilities, this extension can be used for migrating SQL Server databases to Azure for an enhanced user experience. With this extension, users can get right-sized Azure recommendations based on performance data collected from your source SQL Server databases to optimize for cost and scale. The migration experience is powered by the Azure Database Migration Service which provides a scalable, resilient, and secure way to meet the needs of your organization. See below for a snapshot UI of this extension.

With this extension, you can view and monitor the details of your database migrations. This is as simple as migrating your database using the Azure SQL extention and tracking progress and health of these migrations directly on the Azure portal.

To learn more, take a look at our Tech Community blog.

Support for .NET Interactive Notebooks extension

The .NET Interactive Notebooks extension in Azure Data Studio now provides support for additional languages such as C#, F#, and PowerShell. This multi-language experience enables users to use the best language for the task at hand. Even more exciting is that there is now the ability to use multiple languages in the same Interactive notebook and share variables between those languages using SQL Integration.

New Table Designer features

Based on user feedback, we have made available more table attributes with advanced features. Azure Data Studio now offers system versioning, graph tables, and Memory-Optimized Tables.

System versioned tables

System versioned tables, also known as Temporal Tables, can now be created in Azure Data Studio, and these tables provide historical information about column data at any given point in time. All data in Temporal Tables exists in direct relation to the time the data was created, updated, or deleted.

So why Temporal Tables?

You guessed it, time travel! System versioning makes it easy to perform “time travel” to see the history of all modifications made to data, which makes tracking changes and data auditing more feasible. Time travel also makes data recovery possible as all changes made to data are tracked in the system-versioned tables, making it easier to revert to the values which existed at the exact time the data was lost. When system versioning is enabled during table creation, the engine automatically creates a separate version of that table. System versioned tables follow the same schema as the original tables from which they were generated.

In the table above, we have a system version enabled table named “Department”. A separate historical table, “dbo.MSS…” is automatically created to track all changes made to this table. In the original Department table, a series of changes were made. The table below shows all these changes relative to the time frame they existed. For more information, take a look at our documentation on system versioned tables.

Graph tables

Graph tables have been available since SQL Server 2017 and can now be designed in Azure Data Studio. Graph tables provide an easier way to create relationships between different datasets using nodes and edge tables. Node or edge tables can be created under any schema in the database, but they all belong to one logical graph. Azure Data Studio brings a newly improved visualization to differentiate node and edge tables. In the table below, node tables are represented by a single dot, while edge tables are depicted by two dots with a line in between connecting them.

The image below shows a graph node table in the Table Designer. When a table is indicated as a node, the engine automatically assigns an ID to that table (as seen in the UI as “$node_id”) as this becomes the unique identifier to this table. The script is also updated to indicate that this table is a node table.

Edge tables, on the other hand, are used to show the relationships between node tables. When an edge table is created, multiple internal columns are created. The ID specific to the edge table, and then the IDs representing the nodes that will be related to each other as seen below. Also, notice the change in the script.

The Table Designer fully supports working with edge constraints for SQL graph edge tables. In the image below, our edge table (named “Likes”) is constrained by “Person to Product” and “Person to Restaurant” clauses. This means that an edge can either link between “Person to Product” or “Person to Restaurant” based on the “likes”. Azure Data Studio brings a one-of-a-kind, easy-to-grasp UI to depict edge and node relationships.

For more information take a look at our documentation on graph tables.

Memory-Optimized Tables

Memory-Optimized Tables can now be created via Table Designer in Azure Data Studio. With Memory-Optimized Tables, data is persisted in memory, rather than on disk, with the option to include durability to schema and/or data. This is particularly useful for applications that have databases that undergo multiple concurrent transactions. See image below for memory-optimized configuration and accompanying script changes. For more information take a look at our memory-optimized tables documentation.

Query Plan Viewer updates

There have been a small number of visible changes to Query Plan Viewer in this release of Azure Data Studio. We have added icons to note warnings and parallelism when they occur for an operator, and we’ve introduced additional Find options when searching within a plan. There is a new option on the plan toolbar (added to the bottom) which provides the option to disable tooltips, this is particularly helpful when navigating plans with numerous operators.

Finally, we’ve added support for opening saved .sqlplan files in Azure Data Studio, and we addressed some display issues in the Properties window.  Expect additional enhancements in the next release.

SQL Projects and Schema Compare

In the latest release of the SQL Database Projects extension for Azure Data Studio and Microsoft Visual Studio Code, we are introducing a new SQL Projects format based on an SDK-style project file. The SQL project SDK is named, Microsoft.Build.Sql and it is now available in preview via NuGet and the 0.16.1 version of SQL Database Projects for Azure Data Studio and Visual Studio Code. With Microsoft.Build.Sql, cross-platform pipeline builds are simplified, and the complexity of the project file is significantly reduced. For more information on SDK-style SQL Projects and updates to cross-platform SQL development take a look at this blog.

Other improvements for developers in this release focus on increasing the flexibility of local development. Updating a SQL project from the current state of a database is now enabled in the integration between Schema Compare and SQL Projects and can be accessed directly from the dashboard of a database. For developers focused on working with Azure SQL Database, a new project template has been introduced that enables local validation and testing of SQL Projects for Azure SQL Database.

Learn more

We are excited to continue this path to modernizing database experiences for developers and database administrators alike. Watch this space for future updates. To learn more about the Azure Data Studio release and journey, check out the release notes for Azure Data Studio.