Rediger

Quickstart: Use Visual Studio Code to connect and query Azure SQL Database or Azure SQL Managed Instance

Applies to: Azure SQL Database Azure SQL Managed Instance

Visual Studio Code is a graphical code editor for Linux, macOS, and Windows. It supports extensions, including the MSSQL extension for querying SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, and other platforms. In this quickstart, you use Visual Studio Code to connect to Azure SQL Database or Azure SQL Managed Instance and then run Transact-SQL statements to query, insert, update, and delete data.

Prerequisites

Install Visual Studio Code

Make sure you have installed the latest Visual Studio Code. For installation guidance, see Install Visual Studio Code.

Configure Visual Studio Code

To configure Visual Studio Code for connecting to Azure SQL Database or Azure SQL Managed Instance, you need to install the necessary extensions and dependencies based on your operating system. Follow the steps below for your specific OS to get started.

Windows

Load the MSSQL extension by following these steps:

  1. Open Visual Studio Code.
  2. Open the Extensions pane (or Ctrl + Shift + X).
  3. Search for sql and then install the SQL Server (mssql) extension.

For additional installation guidance, see MSSQL extension for Visual Studio Code.

macOS

For macOS, you need to install OpenSSL, which is a prerequisite for .NET Core that the MSSQL extension uses. Open your terminal and enter the following commands to install brew and OpenSSL.

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install openssl
mkdir -p /usr/local/lib
ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/

Linux (Ubuntu)

Load the MSSQL extension by following these steps:

  1. Open Visual Studio Code.
  2. Open the Extensions pane (or Ctrl + Shift + X).
  3. Search for sql and then install the SQL Server (mssql) extension.

For additional installation guidance, see MSSQL extension for Visual Studio Code.

Get server connection information

Get the connection information you need to connect to Azure SQL Database. You need the fully qualified server name or host name, database name, and login information for the upcoming procedures.

  1. Sign in to the Azure portal.

  2. Navigate to the SQL databases or SQL Managed Instances page.

  3. On the Overview page, review the fully qualified server name next to Server name for SQL Database or the fully qualified server name next to Host for a SQL Managed Instance. To copy the server name or host name, hover over it and select the Copy icon.

Set language mode to SQL

In Visual Studio Code, set the language mode to SQL to enable mssql commands and T-SQL IntelliSense.

  1. Open a new Visual Studio Code window.

  2. Press Ctrl + N. A new plain text file opens.

  3. Select Plain Text in the status bar's lower right-hand corner.

  4. In the Select language mode dropdown list that opens, select SQL.

Connect to your database

Use Visual Studio Code to establish a connection to your server.

Important

Before continuing, make sure that you have your server and sign-in information ready. Once you begin entering the connection profile information, if you change your focus from Visual Studio Code, you have to restart creating the profile.

  1. In Visual Studio Code, press Ctrl + Shift + P (or F1) to open the Command Palette.

  2. Type connect and then choose MS SQL:Connect.

  3. Select + Create Connection Profile.

  4. Follow the prompts to specify the new profile's connection properties. After specifying each value, press Enter to continue.

    Property Suggested value Description
    Server name The fully qualified server name Something like: <Contoso-server-name>.database.windows.net.
    Database name Your database name For the free tier path, enter the name of the free database you already provisioned. For the Adventure Works path, enter the database that contains the sample tables, such as mySampleDatabase.
    Authentication SQL Login This tutorial uses SQL Authentication.
    User name User name The user name of the server admin account used to create the server.
    Password (SQL Login) Password The password of the server admin account used to create the server.
    Save Password? Yes or No Select Yes if you don't want to enter the password each time.
    Enter a name for this profile A profile name, such as mySampleProfile A saved profile speeds your connection on subsequent logins.

    If successful, a notification appears saying your profile is created and connected.

Query an existing free tier database

Use this path if you connected to a new, empty Azure SQL Database free tier database.

  1. Run this query in the editor:

    SELECT DB_NAME() AS current_database;

  2. Confirm that the result is the name of the free database you provisioned, not master or another database. If it doesn't match, reconnect to the intended database before continuing.

  3. Follow Create a table, Insert rows, and View the data in the MSSQL first-query quickstart. These steps create dbo.Authors and query its five sample rows in your existing database. You need permission to create the table and modify its data. Skip that quickstart's generic Create a database step; CREATE DATABASE Library doesn't provision a free tier database.

  4. Follow that quickstart's Clean up resources instructions to remove only the sample table you created, after checking the database and table ownership. Don't drop your free database.

  5. Track your remaining allowance and review pause and overage behavior. Disconnect query editors and Object Explorer when you're done. Open connections can prevent idle auto-pause; disconnecting doesn't restore usage already consumed.

You can continue building in the same database. Query completion doesn't establish workload compatibility or guarantee that future usage stays within the free allowance. Don't run the following Adventure Works examples unless you separately chose the sample-database path and have its required tables.

Query data

The remaining query, insert, update, and delete examples use Adventure Works, not the empty free tier database path.

Run the following SELECT Transact-SQL statement to query for the top 20 products by category.

  1. In the editor window, paste the following SQL query.

    SELECT pc.Name AS CategoryName,
    p.name AS ProductName
FROM [SalesLT].[ProductCategory] pc
INNER JOIN [SalesLT].[Product] p
    ON pc.ProductCategoryId = p.ProductCategoryId;

  2. Press Ctrl + Shift + E to run the query and display results from the Product and ProductCategory tables.

    Screenshot of query to retrieve data from 2 tables.

Insert data

Run the following INSERT Transact-SQL statement to add a new product into the SalesLT.Product table.

  1. Replace the previous query with this one.

    INSERT INTO [SalesLT].[Product] (
    [Name],
    [ProductNumber],
    [Color],
    [ProductCategoryID],
    [StandardCost],
    [ListPrice],
    [SellStartDate]
 )
 VALUES (
    'myNewProduct',
    123456789,
    'NewColor',
    1,
    100,
    100,
    GETDATE()
 );

  2. Press Ctrl + Shift + E to insert a new row in the Product table.

Update data

Run the following UPDATE Transact-SQL statement to update the added product.

  1. Replace the previous query with this one:

    UPDATE [SalesLT].[Product]
SET [ListPrice] = 125
WHERE Name = 'myNewProduct';

  2. Press Ctrl + Shift + E to update the specified row in the Product table.

Delete data

Run the following DELETE Transact-SQL statement to remove the new product.

  1. Replace the previous query with this one:

    DELETE FROM [SalesLT].[Product]
WHERE Name = 'myNewProduct';

  2. Press Ctrl + Shift + E to delete the specified row in the Product table.

Related content

  • Sidst opdateret den