Introduction

Visual Studio Code (VSCode) is a free and light source code editor created by Microsoft. It can be installed in Windows, macOS, or Linux. Note that Visual Studio is not the same as Visual Studio Code. Visual Studio Code is a light software to create and it is free whereas Visual Studio is an Integrated Development Environment (IDE).

You can create HTML code, JavaScript, C#, Python, and SQL Server there. It is a popular tool to create code for developers. As a SQL Server expert, I prefer SSMS to handle my SQL Server databases. However, if you already work with Visual Studio Code, you may want to connect to SQL Server using the extension.

This tutorial will teach you how to work with the SQL Server using Visual Studio Code.

Download Visual Studio Code

Use this link to download Visual Studio Code: Download Visual Studio Code. There are versions for Windows, Mac, and Linux.

Once downloaded, run the installer and install VSCode.

Install the SQL Server Extension

To install the SQL Server extension in Visual Studio code, click on the Extensions icon.

In the search text box write SQL Server select the SQL Server (mssql) Extension from Microsoft and press the install button.

Connect to SQL Server

Once installed, close and open again the Visual Studio Code. Press the SQL Server icon to connect to SQL Server.

Press the + icon to add a connection.

Enter the SQL Server name.

Type the database name. In this example, we will use the Adventureworks2022. However, you can use any database of your preference. For more information about the adventureworks, refer to this related article.

You can use a SQL login, which uses logins created inside SQL Server, or Integrated authentication which uses the operative system authentication. Microsoft Entra ID is like an Active Directory id in Azure. For more information about authentication Methods refer to these links.

• SQL Server authentication methods, logins, and database users
• What is Microsoft Entra ID?

Finally, you can write a profile name for the connection.

Visual Studio Code options

When you right-click the connection you have the following options:

• New query is used to run a new query. For example, you could create tables, run a select, run system-stored procedures, or run any T-SQL sentence.
• Disconnect will disconnect Visual Studio code from the database.
• Remove will remove the connection in VSCode.
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.
• Create Project From Database generates .sql scripts with the Database code as a project.

Table options

When you right-click a table with VS Code, you have the following options:

• Select top 1000 will show the first 1000 or less rows in the table.
• Script as Create generates the T-SQL code to create a table.
• Script as Drop generates the T-SQL code to drop a table.
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.
• Create Azure Functions with SQL binding was an option when this article was written in the Preview version. Azure functions are solutions to write code in different languages using a serverless solution. Azure SQL bindings for Azure are used to integrate with your SQL data.

View Options

You can also manipulate and handle your views. A view is like a virtual table. It is a query with a name. You have the following options to handle views in VS Code:

• Select top 1000 will show the first 1000 or fewer rows in the view.
• Script as Create generates the T-SQL code to create the view.
• Also, Script as Drop generates the T-SQL code to drop the view.
• Script as Alter will generate the code to edit the view.
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.
• Create Azure Functions with SQL binding was an option when this article was written in the Preview version. Azure functions are solutions to write code in different languages using a serverless solution. Azure SQL bindings for Azure are used to integrate with your SQL data.

For more information about views, refer to this link: CREATE VIEW (Transact-SQL)

Programmability Options

Programmability contains Stored Procedures, Functions, Triggers, and other options.

In stored procedures, you have the following options:

• Script as Create generates the T-SQL code to create the stored procedure.
• Also, Script as Drop generates the T-SQL code to drop the stored procedure.
• Script as Alter will generate the code to edit the stored procedure
• Script as Execute will generate the code to execute the stored procedure
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.

For more information about stored procedures, refer to this link: Create a stored procedure

Functions

In VSCode, you have 3 options:

• Table-valued functions return the set as a table.
• Scalar-valued functions return a single value.
• Aggregate functions summarize and condense data.

You have the following options to edit functions.

• Script as Create generates the T-SQL code to create the functions.
• Also, Script as Drop generates the T-SQL code to drop the functions.
• Script as Alter will generate the code to edit the function.
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.

For more information about functions, refer to this link: What are the SQL database functions?

Triggers

In addition, you have triggers. You can create the following scripts with triggers:

• Script as Create generates the T-SQL code to create the trigger.
• Script as Drop generates the T-SQL code to drop the trigger.
• Refresh is commonly used when a new object is created or dropped. It will refresh the list of visible objects.

There are other objects like the assembles of user-defined data types, etc. You will be able to refresh them, but not modify the code.

Conclusion

As you can see, VS Code provides a nice extension created by Microsoft to create and manipulate SQL Server tables. Compared with SSMS, it is a very simple environment and it lacks several basic features to handle data. However, if you only need to create tables add data, and stored procedures and you do not need to do DBA stuff, the VS Code can be useful, especially if you work on it daily.