Azure Data Studio - SQL Code Snippets

In this article, we’ll learn to how to create and utilize the SQL Code Snippets for Transact SQL using the Azure Data Studio. Moreover, we’ll also learn the process to create our own custom SQL Code Snippets for any command that we require and wish to use that aren’t already available. This article is a part of the Azure Data Studio Series. Learn more about Azure Data Studio from the articles linked below.

Azure Data Studio Article Series

1. Azure Data Studio 
2. Azure Data Studio – Connecting To Azure SQL Database 
3. Azure Data Studio – Create, Query and Delete in Azure SQL Database 
4. Azure Data Studio - Create, Query And Delete In SQL Server 
5. Azure Data Studio - SQL Code Snippets 

Azure Data Studio

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.

Transact SQL(T-SQL)

T-SQL is one of the most widely used SQL Derivatives. It is known as a transactional language used to define how things are to be done. T SQL is one of the easiest and most effective way to get things done. It is importantly used to create applications and also to add business login into the application the backend systems.

Code Snippets

Creating databases and database objects can be mundane trivial tasks for data engineers over time. Solving this tedious process, Code Snippets enable easier method of doing the same task. Code Snippets are basically templates to make these actions easier. Appropriate syntax can be generated with ease using code Snippets. Azure Data Studio enables this functionality. Let us learn to build these code snippets and the ways to use them.

Built-in SQL Code Snippets in Azure Data Studio 

Let us explore the built-in SQL Code snippets already available in Azure Data Studio.

Step 1

Open the Azure Data Studio.

Step 2

You need to connect to either of Database Servers from localhost following Azure Data Studio - Create, Query And Delete In SQL Server or Azure SQL Database following the article.

Step 3

Now, under the Connections and specific database you want to work on, Right Click under Connections and Click on New Query. Here we choose the SQL Server database localhost.

Step 4

Now, the new query has been opened. In order to access these built-in snippets, let us first type ‘sql’.

Here, we can see all the available options of different templates enabled with this snippet.

Let us choose, sqlCreateTable.

Step 5

We can see, the Table Creation query has been added.

-- Create a new table called '[TableName]' in schema '[dbo]'
-- Drop the table if it already exists
IF OBJECT_ID('[dbo].[TableName]', 'U') IS NOT NULL
DROP TABLE [dbo].[TableName]
GO
-- Create the table in the specified schema
CREATE TABLE [dbo].[TableName]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [ColumnName2] NVARCHAR(50) NOT NULL,
    [ColumnName3] NVARCHAR(50) NOT NULL
    -- Specify more columns here
);
GO

Here, we need to make a few changes for the TableName that we desire. Simply double click on and all of the rest in the query will be highlighted.

We require to change all of them as TestDB. We can do this one at a time or simply right click and choose, Change all Occurrences.

-- Create a new table called '[TestDB]' in schema '[dbo]'
-- Drop the table if it already exists
IF OBJECT_ID('[dbo].[TestDB]', 'U') IS NOT NULL
DROP TABLE [dbo].[TestDB]
GO
-- Create the table in the specified schema
CREATE TABLE [dbo].[TestDB]
(
    [Id] INT NOT NULL PRIMARY KEY, -- Primary Key column
    [Name] NVARCHAR(50) NOT NULL,
    [Location] NVARCHAR(50) NOT NULL
    -- Specify more columns here
);
GO

Now, we are ready to run the query.

Step 6

Once we run the query, we can see the message.

Creating Custom SQL Code Snippets

Step 7

Open the Command Palette with (Ctrl+Shift+P).

Step 8

 Type ‘snip’ and choose the Preferences: Configure User Snippets.

Step 9

Now, look for ‘sql’. Once we have found the sql(SQL), select it.

Step 10

We are now directed to the sql.json file.

Here, we place our snippets for sql commands where snippets are defined with a snippets name that includes prefix, body and description.

Step 11

Here, we write and define our snippets with name sqlSelectTop5 and sqlCreateTable2 which perform special task to select Top 5 from all records in table and creates table.

{
	"Select top 5": {
   "prefix": "sqlSelectTop5",
   "body": "SELECT TOP 5 * FROM ${1:TableName}",
   "description": "User-defined snippet example 1"
   },
   "Create Table snippet":{
   "prefix": "sqlCreateTable2",
   "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 snippet example 2"
	  }
	  }

Once, the snippet is written, save the sql.json file.

Step 12

Now, let us check in our Query. Type in sqlSelect and you’ll find the new sqlSelectTop5 command right there.

Select the snippet and the command can be seen written with so ease.

SELECT TOP 5 * FROM TableName

Conclusion

Thus, in this article, we learned to use the built-in SQL Code Snippets in Azure Data Studio and also learned to create our custom SQL Code Snippets. This will enable easier and efficient working with SQL in Azure Data Studio for any data engineers and developers. The benefits of this feature are monumental with the developers and engineers having the power in their hand to choose to create snippets as per their ease to make their workflow convenient in a day-to-day basis.