An Introduction To Azure Data Studio

In this article, we will see some of the features of the newly introduced Azure Data Studio. This is a cross-platform, lightweight database tool for data professionals and developers who are using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux.

Azure Data Studio offers a modern editor experience, code snippets, source control integration, and an integrated terminal. We can say, this might be a full replacement for traditional SSMS (SQL Server Management Studio) in future.

 

Please download Azure Data Studio from this Link.

 

Connect and Query Azure Data Studio.

 

 

 

Enter your server details. Here, I am connecting with my SQL Server 2017 instance. I am using my Windows Authentication to connect server.

 

 

 

It will show all your database details. I have only one user-defined database in my SQL server. The other four are system databases.

 

 

 

It will also show the database size details in a very attractive way.

 

 

Using T-SQL Code Snippets in Azure Data Studio

One of the major advantages in Azure Data Studio is its T-SQL Code Snippets. It has many built-in Code Snippets available.

We can use T-SQL Code Snippet to Create a new Database. Please open a new SQL Query window (Ctrl+N) and type “SQL”

Please select “sqlCreateDatabase” snippet and press Enter key.

 

 

-- Create a new database called 'DatabaseName'
-- Connect to the 'master' database to run this snippet
USE master
GO
-- Create the new database if it does not exist already
IF NOT EXISTS (
    SELECT [name]
        FROM sys.databases
        WHERE [name] = N'DatabaseName'
)
CREATE DATABASE DatabaseName
GO

It will automatically fill the code snippets to the Query editor. You can give a valid name to the Database and execute the Query.

 

-- Create a new database called 'DatabaseName'
-- Connect to the 'master' database to run this snippet
USE master
GO
-- Create the new database if it does not exist already
IF NOT EXISTS (
    SELECT [name]
        FROM sys.databases
        WHERE [name] = N'SarathDB'
)
CREATE DATABASE SarathDB
GO

We can create our own T-SQL Code Snippets very easily. Please open Command Pallet (Ctrl+Shift+P) and type “snip”

 

 

Choose SQL snippet. (sql.json file)

 

 

 

I have added two code snippets in this file. One is for selecting the top 100 records from any table, and the other is for creating a new table. You can add any number for code snippets in this file. Please note that we can define any number for parameters for each code snippet also.

 

 

{   "Select top 100": {
        "prefix": "sqlSelectTop100",
        "body": "SELECT TOP 100 * FROM ${1:TableName} ORDER BY ${2:ColumnName}",
        "description": "Select Top 100 Records from a Table"
    },
    "Create Table snippet":{
        "prefix": "sqlCreateTableSarath",
        "body": [
        "-- Create a new table called '${1:TableName}' in schema '${2:SchemaName}'",
        "-- Drop the table if it already exists",
        "IF OBJECT_ID('$2.$1', 'U') IS NOT NULL",
        "DROP TABLE $2.$1",
        "GO",
        "-- Create the table in the specified schema",
        "CREATE TABLE $2.$1",
        "(",
        "   $1Id INT NOT NULL PRIMARY KEY, -- primary key column",
        "   Column1 [NVARCHAR](50) NOT NULL,",
        "   Column2 [NVARCHAR](50) NOT NULL",
        "   -- specify more columns here",
        ");",
        "GO"
        ],
       "description": "User defined snippets: Create a new Table"
    }
}

We can check the new code snippets in Query editor window. Please type “SQL” again

 

Please note the newly added two code snippets listed in Query editor. You can choose any of the new code snippets and press the Enter key. Our new code snippet will be loaded successfully.

 

 

-- Create a new table called 'TableName' in schema 'SchemaName'
-- Drop the table if it already exists
IF OBJECT_ID('SchemaName.TableName', 'U') IS NOT NULL
DROP TABLE SchemaName.TableName
GO
-- Create the table in the specified schema
CREATE TABLE SchemaName.TableName
(
   TableNameId INT NOT NULL PRIMARY KEY, -- primary key column
   Column1 [NVARCHAR](50) NOT NULL,
   Column2 [NVARCHAR](50) NOT NULL
   -- specify more columns here
);
GO

Adding new Extensions to Azure Data Studio

You can add more extensions easily by clicking the “Settings” icon and then clicking “Manage Extensions” button.

 

 

 

SQL Server Import is one of the recommended extensions by Azure Data Studio.

 

 

 

You can click the “Install” button.

 

 

 

After some time, the new extension will be installed successfully.

 

 

We can use this SQL Import tool to import data from CSV or JSON file to SQL database.

 

Please right-click the Database and choose “Import Wizard” to start.

 

 

 

Please specify the Input Filename.

 

 

 

You can Preview the Data here.

 

 

 

We can modify the columns if needed.

 

 

After making the modifications (if needed) in the columns you can click the “Import” button.

Our Import process will be finished soon.

 

 

You can use the SQL Query to select data from a new table.

 

 

SELECT * from dbo.employee

You can see the Query Plan by clicking the “Explain” button.

 

 

It will open a Query Plan tab. You can see the Query plan summary by moving the mouse cursor to “SELECT” area.

 

 

 

You can also view the entire index scan details by moving the mouse cursor to “Clustered Index Scan” area.