Rajendra Gupta
Visual Studio Code Snippet example

Visual Studio Code (VS Code) for SQL Server development

July 16, 2020 by

In the previous article, Getting started with Visual Studio Code (VS Code), we took a detailed overview of the popular code editor. It supports various programming languages t-SQL, Python, PHP, AWS CLI, PowerShell, etc. We need to use extensions in the VS code to work with these languages. For example, if we open the T-SQL script, it recommends you for the below extension.

We can work on SQL scripts without extension as well, but it does not give added functionality to work on T-SQL. In the below image, we see that you do not get different color codes for the database object. It treats the whole script as a regular file.

Visual Studio Code script

Click on recommendation, and it opens the recommended extension for SQL Server. You can search for a specific extension in the marketplace.

SQL Server extension

Click on Install for SQL Server (mssql) extension. It installs, enables the extension globally along with the SQL tools service for the mssql extension.

Install SQL tools service

Now, let’s add a new connection to SQL Server in VS code. Click on Add Connection.

add a new connection

It opens a prompt to ask for a server name. You can also specify ADO.NET connecting string.

Enter a server name

Press Enter, and you get prompt for a database name. It is an optional field. By default, if we do not enter any database name, VS code connects to the master database.

Enter database name

Next, select the authentication mode from either as SQL Login or Integrated. For a Windows authentication, choose an integrated method. If you choose SQL login, it asks for the credential details of SQL login as well.

authentication mode

Specify a profile name for this connection to SQL Server.

profile name

Press Enter. It connects to SQL Server, and it shows the databases, security, and server objects. You can expand a database to view its objects such as tables, views, functions, stored procedures, etc.

Connect to SQL Server in the VS code

View connection details in User Settings JSON

VS code stores the connection information in a JSON file. To view JSON file content, navigate to File-> Preferences -> Settings.

View connection details in User Settings JSON

In the settings page, click on MSSQL config… from the left-hand menu. It shows the MSSQL:Connections menu.

MSSQL connections

Click on the Edit in settings.json link. It opens the mssql connections information that we entered earlier in the VS code.

Edit in settings.json

Suppose you have a lot of connections profiles in your Visual Studio Code. You can copy these connections profile and paste it into the new VS code setting file. It can save our efforts in setting up profiles on new machines or for your colleagues.

Let’s explore a few exciting features of VS code SQL Server extension.

T-SQL IntelliSense features in Visual Studio Code

VS code SQL editor supports the t-SQL IntelliSense feature similar to the SSMS and Azure data studio. As soon as you start typing the code in the SQL editor, it gives you suggestions for the keywords, schema object names such as table, views, columns, functions. You also get help for the parameters in stored procedures or functions. It also supports code formatting and style rule inference.

Linting

Visual Studio code editor highlights any errors and warning in the T-SQL as soon as you write it. For example, if you want to select the records from a table but that table doesn’t exist in the source database, you get an error message stating: Invalid object name.

T-SQL Linting

Similarly, if you want to execute a stored procedure that requires you to specify a parameter value, you can hover your mouse, and it suggests you the parameters in that stored procedure.

Stored procedure parameter

Peek Definition/Go to Definition in Visual Studio Code

Many times, you need a reference to check the schema object definitions. It can be objects such as tables, stored procedures, and functions. You do not need to browse the object in the explorer, check the definition, and come back to the editor again.

Suppose you are retrieving records from a SQL table but wanted to check the columns in that table. Select the object, and you get two options – Peek Definition and Go to definition.

Peek Definition

Click on Peek-> Peek definition, and you get the object script in the pop-up. You can view the script for your reference and close the script definition to return to the previous window.

Suppose you want to execute a stored procedure, but you are not sure about its parameters and their datatypes. You can check the stored procedure definition to check its parameters, script, as shown below.

Peek Definition/Go to Definition

Go to definition

Similar to the peek definition, select the object and click on Go to Definition. It opens the object script in a new query window. You can take a reference for the script or save it for your future reference.

Code Snippets

VS code includes several useful codes for performing useful t-SQL statements in the SQL Server database. These code snippets are for adding a new column, creating a new database, a new table, dropping a table.

You get the list of code snippets once you type the SQL keyword in the editor.

Visual Studio Code Snippets

Select the required code snippets, and you get the T-SQL to perform the activity. For example, to create a new database, click ok the sqlCreateDatabase, and it gives the following code. It highlights the inputs required by the user.

Visual Studio Code Snippet example

Execute SQL query and view results in different formats

Visual Studio Code provides options to view the result and save it in the CSV, Excel, JSON formats. Write any query and press Execute. You can view query output in the results section and its corresponding message in the messages section. To save the results, click on the required output format, specify a location and it gives you output in that format.

Execute SQL query

Suppose you execute multiple batches in a single query window. In SQL Server, we separate the batches with the Go keyword. You get the output of both batches in the single results window. It might be challenging to view multiple results together. VS Code provides an option to maximize the results window when we use multiple batches. For example, in the below screenshot, check the maximize button in the second batch output.

Maximize option

Click on the Maximize button, and it shows the result of that query batch in the full screen of the results section. You can click on Restore to switch back to the standard output window.

Restore output window

Script table menu options

In SSMS and Azure data studio, we can right-click on a table and view the top 1000 records. It also gives the option to generate the script to create and alter objects. Visual Studio Code also supports these features for the SQL Server database.

Right-click on the desired table, and you get these options.

  • Select top 1000 records
  • Script as Create
  • Script as Alter

Script table menu options

SQLCMD mode in Visual Studio Code

VS code SQL Server extension also supports executing queries in SQLCMD mode. You can define variables, interact with the operating system, and execute the queries.

For example, in the below query, we define variables to hold the database name, table name, the output format using the nm:Setvar function.

By default, VS code disables the SQLCMD mode to run the queries. If we try to run the SQLCMD mode query, it does not recognize it and gives you the following error message.

SQLCMD mode

Click on the SQLCMD:off to turn it on and execute the query. You get your expected results, as shown below.

Turn on SQLCMD mode

Conclusion

In this article, we explored the integration of Visual Studio Code and SQL Server to help developers writing queries efficiently. Developers can use a single query editor tool for writing codes in multiple languages. It eliminates the headache of using a separate editor for each language. It supports IntelliSense, SQL CMD mode, linting, Peek definition features to give you a better development experience.

Rajendra Gupta
2,404 Views