Skip to content

quickstart-sql-database.md

Latest commit

 

History

History
155 lines (107 loc) · 6.68 KB

quickstart-sql-database.md

File metadata and controls

155 lines (107 loc) · 6.68 KB
title Quickstart: Connect and query an Azure SQL database using SQL Operations Studio (preview) | Microsoft Docs
description This quickstart shows how to use SQL Operations Studio (preview) to connect to a SQL database and run a query
ms.custom tools|sos
ms.date 03/08/2018
ms.prod sql
ms.reviewer alayu; sstein
ms.suite sql
ms.prod_service sql-tools
ms.component sos
ms.tgt_pltfrm
ms.topic quickstart
author yualan
ms.author alayu
manager craigg

Quickstart: Use [!INCLUDEname-sos] to connect and query Azure SQL database

This quickstart demonstrates how to use [!INCLUDEname-sos] to connect to an Azure SQL database, and then use Transact-SQL (T-SQL) statements to create the TutorialDB used in [!INCLUDEname-sos] tutorials.

Prerequisites

To complete this quickstart, you need [!INCLUDEname-sos], and an Azure SQL server.

  • [Install [!INCLUDEname-sos]](download.md).

If you don't already have an Azure SQL server, complete one of the following Azure SQL Database quickstarts (remember the server name, and login credentials!):

Connect to your Azure SQL Database server

Use [!INCLUDEname-sos] to establish a connection to your Azure SQL Database server.

  1. The first time you run [!INCLUDEname-sos] the Connection page should open. If you don't see the Connection page, click Add Connection, or the New Connection icon in the SERVERS sidebar:

    New Connection Icon

  2. This article uses SQL Login, but Windows Authentication is also supported. Fill in the fields as follows using the server name, user name, and password for your Azure SQL server:

    Setting Suggested value Description
    Server name The fully qualified server name The name should be something like this: servername.database.windows.net
    Authentication SQL Login SQL Authentication is used in this tutorial.
    User name The server admin account This is the account that you specified when you created the server.
    Password (SQL Login) The password for your server admin account This is the password that you specified when you created the server.
    Save Password? Yes or No Select Yes if you do not want to enter the password each time.
    Database name leave blank The name of the database you want to connect to.
    Server Group Select If you created a server group, you can set to a specific server group.

    New Connection Icon

  3. If your server doesn't have a firewall rule allowing SQL Operations Studio to connect, the Create new firewall rule form opens. Complete the form to create a new firewall rule. For details, see Firewall rules.

    New firewall rule

  4. After successfully connecting your server opens in the Servers sidebar.

Create the tutorial database

The following sections create the TutorialDB database that is used in several [!INCLUDEname-sos] tutorials.

  1. Right click on your Azure SQL server in the SERVERS sidebar and select New Query.

  2. Paste the following snippet into the query editor and click Run:

    IF NOT EXISTS (
   SELECT name
   FROM sys.databases
   WHERE name = N'TutorialDB'
)
CREATE DATABASE [TutorialDB]
GO

ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON
GO

Create a table

The query editor is still connected to the master database, but we want to create a table in the TutorialDB database.

  1. Change the connection context to TutorialDB:

    Change context

  2. Paste the following snippet into the query editor and click Run:

    [!NOTE] You can append this to, or overwrite the previous query in the editor. Note that clicking Run executes only the query that is selected. If nothing is selected, clicking Run executes all queries in the editor.

    -- Create a new table called 'Customers' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
DROP TABLE dbo.Customers
GO
-- Create the table in the specified schema
CREATE TABLE dbo.Customers
(
   CustomerId        INT    NOT NULL   PRIMARY KEY, -- primary key column
   Name      [NVARCHAR](50)  NOT NULL,
   Location  [NVARCHAR](50)  NOT NULL,
   Email     [NVARCHAR](50)  NOT NULL
);
GO

Insert rows

  • Paste the following snippet into the query editor and click Run:

    -- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
   ([CustomerId],[Name],[Location],[Email])
VALUES
   ( 1, N'Orlando', N'Australia', N''),
   ( 2, N'Keith', N'India', N'keith0@adventure-works.com'),
   ( 3, N'Donna', N'Germany', N'donna0@adventure-works.com'),
   ( 4, N'Janet', N'United States', N'janet1@adventure-works.com')
GO

View the result

  1. Paste the following snippet into the query editor and click Run:

    -- Select rows from table 'Customers'
SELECT * FROM dbo.Customers;

  2. The results of the query are displayed:

    Select results

Clean up resources

Other articles in this collection build upon this quickstart. If you plan to continue on to work with subsequent quickstarts, do not clean up the resources created in this quickstart. If you do not plan to continue, use the following steps to delete resources created by this quickstart in the Azure portal. Clean up resources by deleting the resource groups you no longer need. For details, see Clean up resources.

Next steps

Now that you've successfully connected to an Azure SQL database and ran a query, try out the Code editor tutorial.