MSSQL Extension for Visual Studio Code: Streamline Development- Azure SQL Devs’ Corner

The MSSQL extension for Visual Studio Code is designed to support developers in building applications that use Azure SQL (including Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs), SQL Database in Fabric (Preview) or SQL Server as backend databases. With a comprehensive suite of features for connecting to databases, designing and managing database schemas, exploring database objects, executing queries, and visualizing query plans, this extension transforms the SQL development experience within VS Code.

The latest enhancements to the MSSQL extension for Visual Studio Code are specifically aimed at boosting productivity. Whether you’re working with databases running locally or in the cloud, the extension equips you with advanced IntelliSense, efficient T-SQL script execution, and customizable options. As a result, you can enjoy a modern and streamlined SQL development workflow.

What’s New in the MSSQL Extension for Visual Studio Code?

We’re excited to introduce significant updates to the MSSQL extension for Visual Studio Code, featuring a fresh set of UI enhancements designed to simplify and elevate your SQL development experience. Overall, these updates make connecting to databases, managing objects, and optimizing queries more intuitive than ever.

All the UI features described below are currently in Public Preview.

Connection Dialog

The Connection Dialog in the MSSQL extension for Visual Studio Code allows you to quickly connect to databases hosted in Azure SQL (including Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs), SQL Database in Fabric (Preview) or SQL Server through a simple and intuitive interface. It provides multiple input options to cater to different scenarios:

Parameters: Enter individual connection details such as server name, database name, username, and password.
Connection String: Directly input a full connection string for more advanced configurations.
Browse Azure: Browse available database instances and databases in your Azure account, with options to filter by subscription, resource group, and location.

In addition to creating new connections, the dialog now includes a Saved Connections and Recent Connections panel, making it easier to reconnect to previously used servers. You can efficiently edit and save your connections with an improved layout that offers better navigation and usability. The enhanced UI makes modifying connection details or switching databases smoother than ever.

Object Explorer

The Object Explorer in the MSSQL extension for Visual Studio Code enables you to navigate through their database objects, such as databases, tables, views, and programmability items. The enhanced filtering functionality makes it easier to locate specific objects within large and complex database hierarchies:

Apply Filters: Filter database objects by properties like name, owner, or creation date. Filters can be applied at multiple levels, including databases, tables, views, and programmability.
Edit Filters: Refine or update existing filters to further narrow down the object list.
Clear Filters: Easily remove applied filters to view all objects within the hierarchy.

These filters provide flexibility and control, making it easier to manage large databases and quickly find the objects you need.

Table Designer

The Table Designer in the MSSQL extension for Visual Studio Code offers a new UI for creating and managing tables for your databases, with advanced capabilities to customize every aspect of the table’s structure:

Columns: Add new columns, set data types, define nullability, and specify default values. You can also designate a column as a primary key or identity column directly within the interface.
Primary Key: Easily define one or more columns as the primary key for your table, ensuring each row is uniquely identifiable.
Indexes: Create and manage indexes to improve query performance by adding additional columns as indexes for faster data retrieval.
Foreign Keys: Define relationships between tables by adding foreign keys that reference primary keys in other tables, ensuring data integrity across tables.
Check Constraints: Set up rules to enforce specific conditions on the data being entered, such as value ranges or patterns.
Advanced Options: Configure more sophisticated properties and behaviors, such as system versioning and memory optimized tables.

Within the designer, the Script As Create panel provides an automatically generated T-SQL script that reflects your table design. You have the following options:

Publish: Apply your changes directly to the database by selecting Publish. This action is powered by DacFx (Data-tier Application Framework), ensuring smooth and reliable deployment of your schema updates.
Copy Script: You can either copy the generated T-SQL script from the preview panel for manual execution, or open it directly in the editor for further adjustments and modifications as needed.

Query Results Pane

The MSSQL extension for Visual Studio Code provides an enhanced query results experience, helping you efficiently visualize and understand your data output. The query results display within the bottom panel of VS Code, which also hosts the integrated terminal, output, debug console, and other tools, creating a unified interface for easy access.

You can now open query results in a New Tab for an expanded view, similar to the previous experience.

Key features of the query results pane include:

Grid View: Displays query results in a familiar grid format, allowing for easy inspection of the data. You now have the option to display results in a New Tab for a clearer, more organized view
Copy Options: Right-click within the results grid to access options like Select All, Copy, Copy with Headers, and Copy Headers, making it convenient to transfer data for other uses.
Save Query Results: Includes the ability to save query results to multiple formats such as JSON, Excel, and CSV, allowing you to work with the data outside of Visual Studio Code.
Inline Sorting: You can sort the data by clicking on the column headers directly in the query results view. Sorting can be done in ascending or descending order to make it easier to analyze specific subsets of the data.
Estimated Plan: The Estimated Plan button is located in the query toolbar, next to the Run Query button. It appears as a flowchart icon and allows you to generate an estimated execution plan without executing the query itself. This feature provides valuable insight into query performance, helping identify potential bottlenecks and inefficiencies before running the actual query.
Enable Actual Plan: A new button labeled Enable Actual Plan, located right after Estimated Plan button in the upper right corner of the results pane, lets you view the actual query plan for executed queries. This addition provides deeper insight into query performance and helps identify bottlenecks and inefficiencies.

You can customize the behavior of the query results pane by adjusting specific settings. For more details on these configuration options, visit the official documentation.

Query Plan Visualizer

The Query Plan Visualizer in the MSSQL extension for Visual Studio Code allows developers to analyze SQL query performance by displaying detailed execution plans. This tool provides insights into how SQL queries are executed, helping developers identify bottlenecks and optimize their queries.

Key features and capabilities include:

Node Navigation: Each step in the execution plan is represented as a node, allowing you to interact with the plan in various ways. You can click on nodes to view tooltips or detailed information about specific operations. Additionally, you can collapse or expand node trees to simplify the view and focus on key areas of the query plan.
Zoom Controls: The visualizer offers flexible zoom options to help you analyze the plan in detail. You can zoom in or out to adjust the level of detail, use the “zoom to fit” feature to resize the view and fit the entire plan on your screen, or set custom zoom levels for more precise examination of specific elements.
Metrics and Highlighting: The metrics toolbar allows you to analyze key performance indicators and highlight expensive operations. You can select metrics such as Actual Elapsed Time, Cost, Subtree Cost, or Number of Rows Read from the dropdown to identify bottlenecks and use these metrics to search for specific nodes within the query plan for deeper analysis.

The right-hand sidebar provides quick access to additional actions:

Save Plan: Save the current execution plan for future reference.
Open XML: Open the XML representation of the query plan to inspect details at the code level.
Open Query: View the query that generated the execution plan directly from the toolbar.
Toggle Tooltips: Enable or disable tooltips for additional details on each node.
Properties: View the properties of each node in the execution plan, with options to sort by importance or alphabetically.

Enabling the New UI Features

To get started, follow the prompt when you first install the extension or update your VS Code settings. Visit our official documentation for step-by-step instructions.

Conclusion

The enhanced MSSQL extension for Visual Studio Code offers a powerful and intuitive SQL development experience. With features like the revamped Connection Dialog, advanced Object Explorer filtering, a visual Table Designer, an updated Query Results Pane, and the Query Plan Visualizer, it’s easier than ever to streamline your workflow and optimize performance—all from within VS Code.

To see these features in action, check out our demo video on YouTube: https://aka.ms/vscode-mssql-ignite-demo.

We’d love to hear your thoughts on the new features! If you have any feedback or run into issues, please let us know by creating a GitHub issue at https://aka.ms/vscode-mssql-bug. Alternatively, join our community, participate in discussions, or contribute to the extension at https://aka.ms/vscode-mssql-repo.

Happy coding!