Quickstart: Use SQL Server Management Studio to connect and query an Azure SQL database

In this quickstart, you'll use SQL Server Management Studio (SSMS) to connect to an Azure SQL database. You'll then run Transact-SQL statements to query, insert, update, and delete data. You can use SSMS to manage any SQL infrastructure, from SQL Server to SQL Database for Microsoft Windows.

Prerequisites

An Azure SQL database. You can use one of these quickstarts to create and then configure a database in Azure SQL Database:

Single database Managed instance
Create Portal Portal
CLI CLI
PowerShell PowerShell
Configure Server-level IP firewall rule Connectivity from a VM
Connectivity from on-site
Load data Adventure Works loaded per quickstart Restore Wide World Importers
Restore or import Adventure Works from BACPAC file from GitHub

Important

The scripts in this article are written to use the Adventure Works database. With a managed instance, you must either import the Adventure Works database into an instance database or modify the scripts in this article to use the Wide World Importers database.

Install the latest SSMS

Before you start, make sure you've installed the latest SSMS.

Get SQL server connection information

Get the connection information you need to connect to the Azure SQL database. You'll 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 a single database or the fully qualified server name next to Host for a managed instance. To copy the server name or host name, hover over it and select the Copy icon.

Connect to your database

In SMSS, connect to your Azure SQL Database server.

Important

An Azure SQL Database server listens on port 1433. To connect to a SQL Database server from behind a corporate firewall, the firewall must have this port open.

  1. Open SSMS. The Connect to Server dialog box appears.

  2. Enter the following information:

    Setting     Suggested value Description 
    Server type Database engine Required value.
    Server name The fully qualified server name Something like: mynewserver20170313.database.windows.net.
    Authentication SQL Server Authentication This tutorial uses SQL Authentication.
    Login Server admin account user ID The user ID from the server admin account used to create the server.
    Password Server admin account password The password from the server admin account used to create the server.

    connect to server

  3. Select Options in the Connect to Server dialog box. In the Connect to database drop-down menu, select mySampleDatabase.If you leave the drop down to default, the connection is made to master database.

    connect to db on server

  4. Select Connect. The Object Explorer window opens.

  5. To view the database's objects, expand Databases and then expand mySampleDatabase.

    mySampleDatabase objects

Query data

Run this SELECT Transact-SQL code to query for the top 20 products by category.

  1. In Object Explorer, right-click mySampleDatabase and select New Query. A new query window connected to your database opens.

  2. In the query window, paste this SQL query.

    SELECT pc.Name as CategoryName, p.name as ProductName
    FROM [SalesLT].[ProductCategory] pc
    JOIN [SalesLT].[Product] p
    ON pc.productcategoryid = p.productcategoryid;
    
  3. On the toolbar, select Execute to retrieve data from the Product and ProductCategory tables.

    query to retrieve data from table Product and ProductCategory

Insert data

Run this INSERT Transact-SQL code to create a new product in 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. Select Execute to insert a new row in the Product table. The Messages pane displays (1 row affected).

View the result

  1. Replace the previous query with this one.

    SELECT * FROM [SalesLT].[Product]
    WHERE Name='myNewProduct'
    
  2. Select Execute. The following result appears.

    result of Product table query

Update data

Run this UPDATE Transact-SQL code to modify your new product.

  1. Replace the previous query with this one.

    UPDATE [SalesLT].[Product]
    SET [ListPrice] = 125
    WHERE Name = 'myNewProduct';
    
  2. Select Execute to update the specified row in the Product table. The Messages pane displays (1 row affected).

Delete data

Run this DELETE Transact-SQL code to remove your new product.

  1. Replace the previous query with this one.

    DELETE FROM [SalesLT].[Product]
    WHERE Name = 'myNewProduct';
    
  2. Select Execute to delete the specified row in the Product table. The Messages pane displays (1 row affected).

Next steps