This article will fully cover the code snippet SQL developer productivity feature in Azure Data Studio including a list of available snippets and examples of how to create custom code snippets
Azure Data Studio (initially called SQL Operations Studio) is a new client tool for SQL Server that works on the cross-platform tool. Starting from SQL Server 2017, we can install SQL Server on the Linux operating system as well. Usually, we use SSMS to connect with SQL Server, but you cannot install SSMS in Linux OS. Azure Data Studio is a development tool that you can run on Linux as well. Microsoft provides monthly release of Azure Data Studio for new features, bug fixes and enhancement over existing features.
You can download current release version 1.5.2(Release date March 22, 2019) from Microsoft Docs. Download Azure Data Studio on a suitable platform (Windows/MacOS/Linux) to work with SQL Server.
A few essential features of Azure Data Studio are as follows
- Modern query editor with fast IntelliSense, Code Snippets and Source control integration
- Marketplace Extensions to get new custom features
- Integrated terminal for PowerShell, SSH or command prompt
- Customizable dashboards
- Built-in charts for the query results
We use many custom scripts in SQL Server for database development, monitoring and administrative purpose. Usually, we store scripts in a folder and browse to the folder as per requirement. You can also place scripts on a shared drive so that team members can also use it. For a large number of scripts, we may need to open individual files to get a code preview.
In SSMS, we have Code Snippets available in terms of templates. For example, connect to a SQL instance and expand Databases.
Suppose we want to create a new stored procedure. We are not aware of the syntax for the stored procedures. We can right-click on Stored Procedures and then on New.
Once you click on a Stored Procedure, it opens a template to write a stored procedure. In the following screenshot, we can add the required variables and table names to write down the procedure.
Azure Data Studio contains new features to increase productivity in writing T-SQL queries. Code Snippets is one such important features of Azure Data Studio.
This article covers the following topics:
- Overview of Code Snippets
- Default Code Snippets
- Create a custom Code Snippets in Azure Data Studio
- Custom Code Snippets with variables
- Custom Code Snippets with multiple lines of codes
Overview of Code Snippets
We need to run common database queries to perform various tasks in a database environment. Some of these activities can be as follows.
- Query to create a database
- Table creation
- Query format for stored procedure
- Monitoring queries using dynamic management views
- We need to frequently view records from a table, procedures, and views.
We can use Code Snippets in Azure Data Studio to accomplish these tasks for us. We do not need to write complete code again and again. It provides code efficiency, standardization as well as time-saving in writing queries.
Let’s explore these Code Snippets in Azure Data Studio. Right click on a connected SQL Instance in Azure Data Studio and go to New Query.
Azure Data Studio provides intelligent suggestions to help you write the queries. These snippets start with word SQL. In a new query editor, type SQL and it populates a list of available Code Snippets.
We can use Up and Down arrow keys to move through all code snippets. It also gives a brief description once you choose any Code Snippet.
It also shows an information icon 
to get information about the particular Code Snippet and code inside it. Click on the icon to get a preview of query inside a Code Snippet.
It allows reviewing the information inside the Code Snippets. Press Enter. It opens a new query editor and copies the code for the particular code Snippet in it.
In this example, we can see the query to create a new database. It creates a new database if it does not exist in connected SQL instance. We need to enter the value of highlighted field ‘DatabaseName’ and execute this.
Similarly, we can use sqlGetSpaceUsed Code Snippet from the list to get space used by a specified table.
List of available Code Snippets in Azure Data Studio
We have various Code Snippets already present in Azure Data Studio.
- sqlAddColumn: Add a new column to a table
- sqlCreateIndex: to create a new index in a table
- sqlCreateStoredProd: Create a stored procedure
- sqlCreateTable : Create a new table
- sqlCreateTempTable: Create a new temporary table
- sqlCreateCursor: Declare a cursor
- sqlDeleteRows: Delete rows from a table
- sqlDropColumn: To drop a column from an existing table
- sqlDropDatabase: To drop a database
- sqlDropStoredproc: To drop a stored procedure
- sqlDropTable: To drop a table
- sqlInsertRows: To insert rows in a table
- sqlListDatabases: Get a list of databases
- sqlListTablesAndViews: Get a list of tables and views in the current database
- sqlSelect: Select rows from a table or view
- sqlUpdate: To update rows in a table
Azure Data Studio allows you to create your Code Snippets as well. Suppose you execute few commands regularly. You can create your Code Snippets in Azure Data Studio. We do not need to browse the directory and search for a particular.SQL file. We can directly call it with intellisense suggestions, enter required parameters and execute it.
Let’s explore steps to configure Code Snippets in Azure Data Studio.
Create a custom Code Snippets
In Azure Data Studio, go to View and click on Command Pallete. You can also use short cut key Ctrl+Shift+P.
In Command pallets window select Preferences: Configure User Snippets
It opens a list of available Snippets file language format. Select the SQL option from this list.
It opens SQL.json file to configure SQL Code Snippets.
- Each Code Snippet name should be unique
- We have three parts of a Code Snippet
- Prefix: It is the name of a code snippet.
- Body: We expand the body and insert it into the New Query window.
- Description: We can add a custom description to understand this easily.
The basic syntax of a Code Snippet is as follows.
|
1 2 3 4 5 6 7 8 9 10 |
"<name of the snippet>": { "prefix":"<sqlNameOfthePrefix>", "body":[ "<T-SQL code line 1 ${1:DefaultValue1}>", . . "< T-SQL code line N>" ], "description":"<Snippet description>" } |
Suppose we want to select top 10 rows from a specified table. In the following code,
|
1 2 3 4 5 6 7 8 |
{ "Select TOP 10 Rows": { "prefix": "sqlTop10Select", "body": "SELECT TOP 10 * FROM ${1:SchemaName}.${2:TableName}", "description": "it reads top 10 records from specified table" }, } |
Press Ctrl+S to save this SQL.json file. Now open another new query window and enter SQL. In the following screenshot, you can see a sqlTop10Select code snippet that we created in above step.
Press Enter and you can see query mentioned inside the snippet.
We can specify the schema and table name to execute this query.
|
1 |
SELECT TOP 10 * FROM HumanResources.Employee |
Suppose we want to know the reason for database transaction log holding reason. We can use a log_reuse_wait_desc column in sys.databases to see the information about transaction log no truncation reason. We can use the following code to create Code Snippet. Copy and paste the following code into SQL.json file.
|
1 2 3 4 5 |
"Select Log holding reason": { "prefix": "sqlLogReuse", "body": "select name,log_reuse_wait_desc from sys.databases where name=${1:DatabaseName}", "description": "We can get trasaction log holding reason with this snippet" }, |
In the following image, you can see we can configure multiple Code Snippets in Azure Data Studio.
You can see both configured Code Snippet in the following image.
In the previous examples, we created basic snippets in Azure Data Studio. Let us do further customization in the upcoming section.
Custom Code Snippet with Variables
In SQL Server, we can check backup or restore completion status using a percent_complete column of DMV sys.dm_exec_requests.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--Backup status SELECT session_id command, percent_complete FROM sys.dm_exec_requests WHERE command LIKE '%backup%'; --Restore status SELECT session_id command, percent_complete FROM sys.dm_exec_requests WHERE command LIKE '%restore%'; |
Suppose we want to create a Code Snippet to query backup or restore completion status. We want a drop-down list to choose from the keyword Backup or Restore. We can specify values in snippet separated by the |symbol.
In the following code, we define variable ‘%${1|BackUp, Restore|}%’ to choose from values specified in this.
|
1 2 3 4 5 |
"Backup or Restore Completion Status": { "prefix": "sqlBackupRestoreStatus", "body": "SELECT session_id command,percent_complete FROM sys.dm_exec_requests WHERE command LIKE '%${1|BackUp,Restore|}%'", "description": "We can get Backup or Restore Pecentage Completion Status" }, |
In the new query window, we can see this Code Snippet.
Click Enter and You get drop-down values to choose appropriate values.
You can use up and down arrow keys to select the required keyword. In the following screenshot, we picked keyword Restore.
Custom Code Snippet with multiple lines of codes in Azure Data Studio
In previous examples, we created a Code Snippet with a query having one line only. Writing a single line code is easy. But suppose we have a large piece of code that we want to convert into a Code Snippet in Azure Data Studio. It is entirely possible to create it, but you need to do extra configuration in the query of a particular Code Snippet.
In the following query, we want to get records from a Select statement. We need to take care of the following things.
- If the query contains multiple lines, we need to specify square brackets in the body of Code Snippets
- Each line should contain double quotes before and after the statement
- Each line should have a comma in last
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
{ "Output of Sales query": { "prefix": "SQLSales", "body":[ "Use [AdventureWorks2017]". "Go", "SELECT s.[BusinessEntityID],p.[Title],p.[FirstName] ", ",p.[MiddleName],p.[LastName],p.[Suffix],e.[JobTitle] ", ",pp.[PhoneNumber],pnt.[Name] AS [PhoneNumberType] ", ",ea.[EmailAddress],p.[EmailPromotion],a.[AddressLine1] ", ",a.[AddressLine2] ", "FROM [Sales].[SalesPerson] s ", "INNER JOIN [HumanResources].[Employee] e ", "ON e.[BusinessEntityID] = s.[BusinessEntityID] ", "INNER JOIN [Person].[Person] p ", "ON p.[BusinessEntityID] = s.[BusinessEntityID] ", "INNER JOIN [Person].[BusinessEntityAddress] bea ", "ON bea.[BusinessEntityID] = s.[BusinessEntityID] ", "INNER JOIN [Person].[Address] a ", "ON a.[AddressID] = bea.[AddressID] ", "INNER JOIN [Person].[StateProvince] sp ", "ON sp.[StateProvinceID] = a.[StateProvinceID] ", "INNER JOIN [Person].[CountryRegion] cr ", "ON cr.[CountryRegionCode] = sp.[CountryRegionCode] ", "LEFT OUTER JOIN [Sales].[SalesTerritory] st ", "ON st.[TerritoryID] = s.[TerritoryID] ", "LEFT OUTER JOIN [Person].[EmailAddress] ea ", "ON ea.[BusinessEntityID] = p.[BusinessEntityID] ", "LEFT OUTER JOIN [Person].[PersonPhone] pp ", "ON pp.[BusinessEntityID] = p.[BusinessEntityID] ", "LEFT OUTER JOIN [Person].[PhoneNumberType] pnt ", "ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]; ", "Go", ], }, } |
Copy and Paste the code in SQL.json and save it. Now go back to the New Query window and call the Code Snippet.
We can see the Code Snippet in the following screenshot.
Once you click enter, you can see the complete query.
We can execute to get the required result set.
Conclusion
In this article, we explored the Code Snippet feature of Azure Data Studio. We also studied creating custom Code Snippets with variables as well. I recommend you to configure the Code Snippet feature in Azure Data Studio so that you can benefit from this great productivity feature.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023