Azure Data Studio

In this Azure Data Studio Series article, we’ve learned about the basics of Azure Data Studio and how to install it, went through a step-by-step guide to connect Azure Data Studio to Azure SQL Database, learnt to connect and query to Azure SQL Database. Following the last article, this article dives in connecting and thus creating tables, working with queries and deleting the data in SQL Server from Azure Data Studio.

Azure Data Studio Article Series

Azure Data Studio is basically a database tool that is cross-platform routinely used by data engineers and professionals for both on-premises and cloud services throughout the operating system spectrum from Windows to macOS and Linux. There are numerous modern editor offerings with the Azure Data Studio from Code Snippets, IntelliSense, Integrated Terminal, Source Control Integration, and more. A great experience with charting of query results, customizable dashboards are supported built-in.

Now, let us learn to connect and query in our SQL Server using Azure Data Studio.

First of all, we need to start with the Installation of SQL Server. Let us learn to download and install in the SQL Server. The Developer Edition SQL Server 2019 is freely available. Download it by following the article, SQL Server 2019 – Download and Install.

Once we have setup our Azure Data Studio and SQL Server, we can go ahead.

Step 1

Open Azure Data Studio. We’ll be taken to Welcome Page like the following.

Click on New.

Step 2

Select New Connection under New button. A connection page will pop up.

Step 3

Fill in the details for the connection with Connection Type as Microsoft SQL Server, Server as localhost and Database as master. Put in username and password as you have setup if asked.

Once these details are filled in, click on Connect.

Step 4

Now, the SQL Server 2019 is connected to our Azure Data Studio. We can see at the top, the master database from the localhost server.

Now, Click on New Query.

Step 5

A New SQL Query page has now been opened. We can type in our query here.

Creating Database

Step 6

Here, we select the master database and under it, with the following SQL code in the Query Editor, we create a new database TutorialDB.

USE master
GO
IF NOT EXISTS (
 SELECT name
 FROM sys.databases
 WHERE name = N'TutorialDB'
)
 CREATE DATABASE [TutorialDB];
GO
IF SERVERPROPERTY('ProductVersion') > '12'
 ALTER DATABASE [TutorialDB] SET QUERY_STORE=ON;
GO

Click on Run to Execute the Code.

Step 7

Right click on localhost server and click on Refresh to see the update in Database.

We can see, a new database TutorialDB has now been created successfully. All the executed queries are also updated in the message section.

Creating Table

Step 8

First of all, change the database setting to TutorialDB.

Now, with the following code, we create a new Table named Customers with the Columns CustomerId, Name, Location, Email with the CustomerId set as the Primary Key.

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

Now, we can see, the table has now been created.

Inserting Row in Table

Step 9

With the following code, we add in values such as Ram, Nepal and so on to the respective columns of CustomerId, Name, Location and Email.

-- Insert rows into table 'Customers'
INSERT INTO dbo.Customers
 ([CustomerId], [Name], [Location], [Email])
VALUES
 ( 1, N'Ram', N'Nepal', N'[email protected]'),
 ( 2, N'Sita', N'United States', N'[email protected]'),
 ( 3, N'Hari', N'Nepal', N'[email protected]'),
 ( 4, N'Geeta', N'India', N'[email protected]'),
 ( 5, N'Chiu', N'Indonesia', N'[email protected]')
GO

Click on Run to execute the query.

Displaying Data in Table

Step 10

With the following code, we select the rows from the table and them view the data.

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

We can see, all the data we inputted priorly with names, location and email of 5 different Customers.

Deleting Data from Table

Step 11

-- Delete rows from table 'Customers'
DELETE FROM dbo.Customers;

We use the following query to delete the rows from the table thus deleting all the data.

Once, we Click on Run. The data are deleted.

To Check if the data is still up there in the table or not, use the Displaying Data query and you’ll obtain the result.

Thus, we can see, all the rows have now been.

Conclusion

Hence, we learned from this article to connect Azure Data Studio to SQL Server. Next, we learned to create database, create table and insert, query and delete data in the database.