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.
Click on recommendation, and it opens the recommended extension for SQL Server. You can search for a specific extension in the marketplace.
Click on Install for SQL Server (mssql) extension. It installs, enables the extension globally along with the SQL tools service for the mssql extension.
Now, let’s add a new connection to SQL Server in VS code. Click on Add Connection.
It opens a prompt to ask for a server name. You can also specify ADO.NET connecting string.
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.
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.
Specify a profile name for this connection to SQL Server.
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.
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.
In the settings page, click on MSSQL config… from the left-hand menu. It shows the MSSQL:Connections menu.
Click on the Edit in settings.json link. It opens the mssql connections information that we entered earlier in the VS code.
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.
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.
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.
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.
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.
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.
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.
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.
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
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.
|
1 2 3 4 5 |
:setvar Table Person.Person :setvar Database AdventureWorks :setvar Value "FirstName +' '+ LastName AS Name" USE $(Database) SELECT $(Value) FROM $(Table) |
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.
Click on the SQLCMD:off to turn it on and execute the query. You get your expected results, as shown below.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023