Use Visual Studio Code to create and run Transact-SQL scripts on Linux

APPLIES TO: yesSQL Server (Linux only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This article shows how to use the mssql extension for Visual Studio Code to develop SQL Server databases in Linux.

Install and start Visual Studio Code

Visual Studio Code is a graphical code editor for Linux, macOS, and Windows that supports extensions.

  1. Download and install Visual Studio Code on your machine.

  2. Start Visual Studio Code.

    Note

    If Visual Studio Code does not start when you are connected through an xrdp remote desktop session, see VS Code not working on Ubuntu when connected using XRDP.

Install the mssql extension

The mssql extension for Visual Studio Code lets you connect to a SQL Server, query with Transact-SQL (T-SQL), and view the results.

  1. In Visual Studio Code, select View > Command Palette, or press Ctrl+Shift+P, or press F1 to open the Command Palette.

  2. In the Command Palette, select Extensions: Install Extensions from the dropdown.

  3. In the Extensions pane, type mssql.

  4. Select the SQL Server (mssql) extension, and then select Install.

    Install the mssql extension

  5. After the installation completes, select Reload to enable the extension.

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.

  1. Select File > New File or press Ctrl+N. Visual Studio Code opens a new Plain Text file by default.

  2. Select Plain Text on the lower status bar, or press Ctrl+K > M, and select SQL from the languages dropdown.

    SQL language mode

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.

Tip

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.

  1. Press Ctrl+Shift+P or F1 to open the Command Palette.

  2. Type sql to display the mssql commands, or type sqlcon, and then select MS SQL: Connect from the dropdown.

    mssql commands

    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.

  3. Select Create Connection Profile to create a new connection profile for your SQL Server.

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

    1. 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. If you need to specify a port, use a comma to separate it from the name. For example, for a local server running on port 1401, enter localhost,1401.

      Note

      You can also enter the ADO connection string for your database here, press Enter, optionally name the connection profile, and press Enter again to connect and create the profile.

    2. Database name (optional): The database that you want to use. To create a new database, don't specify a database name, and press Enter to continue.

    3. Authentication Type: Press Enter to select SQL Login.

    4. User name: Enter the name of a user with access to a database on the server.

    5. Password: Enter the password for the specified user.

    6. 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.

    7. Profile Name (optional): Type a name for the connection profile, such as localhost profile.

    After you 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.

  5. Verify your connection in the lower status bar.

Connection status

Create a SQL database

  1. In the new SQL file that you started earlier, type sql to display a list of editable code snippets.

SQL snippets

  1. Select sqlCreateDatabase.

  2. In the snippet, replace DatabaseName with TutorialDB:

    -- 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
    
  3. Press Ctrl+Shift+E to execute the Transact-SQL commands. View the results in the query window.

Create database messages

Tip

You can customize the shortcut keys for the mssql commands. See Customize shortcuts.

Create a table

  1. Delete the contents of the code editor window.

  2. Press Ctrl+Shift+P or F1 to open the Command Palette.

  3. Type sql to display the mssql commands, or type sqluse, and then select the MS SQL:Use Database command.

  4. Select the new TutorialDB database.

    Use database

  5. In the code editor, type sql to display the snippets, select sqlCreateTable, and then press Enter.

  6. In the snippet, type Employees for the table name and dbo for the schema name.

  7. Create the columns as shown in the following code:

    -- Create a new table called 'Employees' in schema 'dbo'
    -- Drop the table if it already exists
    IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
    DROP TABLE dbo.Employees
    GO
    -- Create the table in the specified schema
    CREATE TABLE dbo.Employees
    (
       EmployeesId        INT    NOT NULL   PRIMARY KEY, -- primary key column
       Name      [NVARCHAR](50)  NOT NULL,
       Location   [NVARCHAR](50)  NOT NULL
    );
    GO
    
  8. Press Ctrl+Shift+E to create the table.

Insert and query

  1. 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
    

    Tip

    While you type, use T-SQL IntelliSense to help complete the statements. T-SQL IntelliSense

  2. Press Ctrl+Shift+E to execute the commands. The two result sets display in the Results window.

    Results

View and save the result

  1. Select View > Editor Layout > Flip Layout to switch to a vertical or horizontal split layout.

  2. Select the Results and Messages panel headers to collapse and expand the panels.

    Toggle headers

    Tip

    You can customize the default behavior of the mssql extension. See Customize extension options.

  3. Select the maximize grid icon on the second result grid to zoom in to those results.

    Maximize grid

    Note

    The maximize icon displays when your T-SQL script produces two or more result grids.

  4. Open the grid context menu by right-clicking on the grid.

    Context menu

  5. Select Select All.

  6. Open the grid context menu again and select Save as JSON to save the result to a .json file.

  7. Specify a file name for the JSON file.

  8. Verify that the JSON file saves and opens in Visual Studio Code.

    Save as JSON

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).

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.