Like Visual Studio Code and your data lives in SQL Server? Microsoft has something for you

SQL Object Explorer, SQLCMD and more IntelliCode arrive for VS Code

Microsoft's determination to foist on the world ever more ways of connecting to SQL Server has continued unabated with a major update of the mssql extension for the ubiquitous Visual Studio Code.

Developers with a yearning for SQL Server are not short of options, with the venerable SQL Management Studio and upstart Azure Data Studio both making a decent fist of connecting to the data shack.

However, since many developers these days prefer the default-to-dark-mode world of Visual Studio Code, Microsoft has emitted a substantial update which brings the SQL Server Management Studio Object Explorer and SQL Server command line support to the platform as well as some AI in the form of IntelliCode.

Naturally, we had to take a look and pointed a copy of Visual Studio 1.39, updated with the new extension, at a local SQL Sever 2019 Preview to see how things worked.

First up, the Object Explorer does indeed work as advertised, inflicting the tree view found in Microsoft's other data botherers onto VS Code for trotting through a hierarchal view of a given SQL Instance.

Click to enlarge

For those that need such graphical fripperies (and since this was the most requested feature) it will be welcome, although for those more happy in the command line, SQLCMD mode is equally handy.

The feature turned up Azure Data Studio back in September and allows users to write and edit queries as SQLCMD scripts. Enabling SQLCMD mode makes the extension handy for script jockeys keen to drop in the odd command or two among the T-SQL.

However, while its implementation in SQL Server Management Studio has been considerably refined over the years, and even Azure Data Studio has an obvious "Enable SQLCMD" button in the query window, things in VS Code are a little less intuitive. A status bar switch can turn the thing on and off, and the team points to a toggle in the VS Code command prompt, but we found it all a little clunky.

It is, of course, the first iteration of the SQLCMD implementation, and we suspect the limitations imposed by the VS Code extension model are preventing some of the free-wheeling of the SSMS incarnation, so we'll cut it some slack.

The final major tweak is IntelliCode support, which improves on IntelliSense by popping up suggestions the algorithm has calculated that the user needs at the top of the list. It is a useful feature, but a stretch to say there is much AI involved.

It is, however, a noticeable improvement over IntelliSense, although does not yet appear to scan our horribly written triggers and stored procedures when guessing what we might be trying to do in a query, thank goodness.

Overall, if you're using Visual Studio Code and SQL Server, this is a handy tool and will save a lurch into SSMS or Azure Data Studio. The Object Explorer is nice to have, although the SQLCMD support remains a little jarring for those more used to a friendly button or query option. ®