SQL Server extension for Visual Studio Code
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
This article shows how to use the mssql extension for Visual Studio Code (Visual Studio Code) to work with databases in SQL Server on Windows, macOS, and Linux, as well as Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The mssql extension for Visual Studio Code lets you connect to a SQL Server, query with Transact-SQL (T-SQL), and view the results.
Create or open a SQL file
The mssql extension enables mssql commands and T-SQL IntelliSense in the code editor when the language mode is set to SQL.
Select File > New File or press Ctrl+N. Visual Studio Code opens a new Plain Text file by default.
Select Plain Text on the lower status bar, or press Ctrl+K > M, and select SQL from the languages dropdown.
Note
If this is the first time you have used the extension, the extension installs the SQL Tools Service in the background.
If you open an existing file that has a .sql file extension, the language mode is automatically set to SQL.
Connect to SQL Server
Follow these steps to create a connection profile and connect to a SQL Server.
Press Ctrl+Shift+P or F1 to open the Command Palette.
Type sql to display the mssql commands, or type sqlcon, and then select MS SQL: Connect from the dropdown.
Note
A SQL file, such as the empty SQL file you created, must have focus in the code editor before you can execute the mssql commands.
Select the MS SQL: Manage Connection Profiles command.
Then select Create to create a new connection profile for your SQL Server.
Follow the prompts to specify the properties for the new connection profile. After specifying each value, press Enter to continue.
Connection property Description Server name or ADO connection string Specify the SQL Server instance name. Use localhost to connect to a SQL Server instance on your local machine. To connect to a remote SQL Server, enter the name of the target SQL Server, or its IP address. To connect to a SQL Server container, specify the IP address of the container's host machine. If you need to specify a port, use a comma to separate it from the name. For example, for a server listening on port 1401, enter <servername or IP>,1401
.
By default, the connection string uses port 1433. A default instance of SQL Server uses 1433 unless modified. If your instance is listening on 1433, you do not need to specify the port.
As an alternative, you can enter the ADO connection string for your database here.Database name (optional) The database that you want to use. To connect to the default database, don't specify a database name here. Authentication Type Choose either Integrated or SQL Login. User name If you selected SQL Login, enter the name of a user with access to a database on the server. Password Enter the password for the specified user. Save Password Press Enter to select Yes and save the password. Select No to be prompted for the password each time the connection profile is used. Profile Name (optional) Type a name for the connection profile, such as localhost profile. After you enter all values and select Enter, Visual Studio Code creates the connection profile and connects to the SQL Server.
Tip
If the connection fails, try to diagnose the problem from the error message in the Output panel in Visual Studio Code. To open the Output panel, select View > Output. Also review the connection troubleshooting recommendations.
Verify your connection in the lower status bar.
As an alternative to the previous steps, you can also create and edit connection profiles in the User Settings file (settings.json). To open the settings file, select File > Preferences > Settings. For more information, see Manage connection profiles.
Encrypt and Trust server certificate
The mssql extension for VS Code v1.17.0 and later includes an important change to the Encrypt property, which is now enabled (set to True) by default for MSSQL provider connections, and SQL Server must be configured with TLS certificates signed by a trusted root certificate authority. In addition, if an initial connection attempt fails with encryption enabled (default), the mssql extension will provide a notification prompt with an option to attempt the connection with Trust Server Certificate enabled. Both the Encrypt and Trust server certificate properties are also available for manual editing in the user settings file (settings.json). The best practice is to support a trusted encrypted connection to the server.
For users connecting to Azure SQL Database, no changes to existing, saved connections are needed; Azure SQL Database supports encrypted connections and is configured with trusted certificates.
For users connecting to on-premises SQL Server, or SQL Server in a Virtual Machine, if Encrypt is set to True, ensure that you have a certificate from a trusted certificate authority (e.g. not a self-signed certificate). Alternatively, you may choose to connect without encryption (Encrypt set to False), or to trust the server certificate (Encrypt set to True and Trust server certificate set to True).
Create a database
In the new SQL file that you started earlier, type sql to display a list of editable code snippets.
Select sqlCreateDatabase.
In the snippet, type
TutorialDB
to replace 'DatabaseName':-- Create a new database called 'TutorialDB' -- Connect to the 'master' database to run this snippet USE master GO IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = N'TutorialDB' ) CREATE DATABASE [TutorialDB] GO
Press Ctrl+Shift+E to execute the Transact-SQL commands. View the results in the query window.
Tip
You can customize the shortcut keys for the mssql commands. See Customize shortcuts.
Create a table
Delete the contents of the code editor window.
Press Ctrl+Shift+P or F1 to open the Command Palette.
Type sql to display the mssql commands, or type sqluse, and then select the MS SQL: Use Database command.
Select the new TutorialDB database.
In the code editor, type sql to display the snippets, select sqlCreateTable, and then press Enter.
In the snippet, type
Employees
for the table name.Press Tab to get to the next field, and then type
dbo
for the schema name.Replace the column definitions with the following columns:
EmployeesId INT NOT NULL PRIMARY KEY, Name [NVARCHAR](50) NOT NULL, Location [NVARCHAR](50) NOT NULL
Press Ctrl+Shift+E to create the table.
Insert and query
Add the following statements to insert four rows into the Employees table.
-- Insert rows into table 'Employees' INSERT INTO Employees ([EmployeesId],[Name],[Location]) VALUES ( 1, N'Jared', N'Australia'), ( 2, N'Nikita', N'India'), ( 3, N'Tom', N'Germany'), ( 4, N'Jake', N'United States') GO -- Query the total count of employees SELECT COUNT(*) as EmployeeCount FROM dbo.Employees; -- Query all employee information SELECT e.EmployeesId, e.Name, e.Location FROM dbo.Employees as e GO
While you type, T-SQL IntelliSense helps you to complete the statements:
Tip
The mssql extension also has commands to help create INSERT and SELECT statements. These were not used in the previous example.
Press Ctrl+Shift+E to execute the commands. The two result sets display in the Results window.
View and save the result
Select View > Editor Layout > Flip Layout to switch to a vertical or horizontal split layout.
Select the Results and Messages panel headers to collapse and expand the panels.
Tip
You can customize the default behavior of the mssql extension. See Customize extension options.
Select the maximize grid icon on the second result grid to zoom in to those results.
Note
The maximize icon displays when your T-SQL script produces two or more result grids.
Open the grid context menu by right-clicking on the grid.
Select Select All.
Open the grid context menu again and select Save as JSON to save the result to a .json file.
Specify a file name for the JSON file.
Verify that the JSON file saves and opens in Visual Studio Code.
If you need to save and run SQL scripts later, for administration or a larger development project, save the scripts with a .sql extension.
Next steps
- If you're new to T-SQL, see Tutorial: Write Transact-SQL statements and the Transact-SQL Reference (Database Engine).
- Develop for SQL databases in Visual Studio Code with the SQL Database Projects extension
- For more information on using or contributing to the mssql extension, see the mssql extension project wiki.
- For more information on using Visual Studio Code, see the Visual Studio Code documentation.