Rajendra Gupta
Azure Data Studio download link

SQL Code Snippets in Azure Data Studio

April 16, 2019 by

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.

Azure Data Studio download link

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.

SSMS Templates

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.

SSMS Templates

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.

Code Snippets examples

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.

Code Snippets examples

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.

Code Snippets examples to create a new database

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.

Code Snippets examples to create new database

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.

Code Snippets examples

Similarly, we can use sqlGetSpaceUsed Code Snippet from the list to get space used by a specified table.

Code Snippets list

Code Snippets examples to get space for a 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

Configure User Snippets

It opens a list of available Snippets file language format. Select the SQL option from this list.

Configure User Snippets language

It opens SQL.json file to configure SQL Code Snippets.

Configure User Snippets example

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

Suppose we want to select top 10 rows from a specified table. In the following code,

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.

User Snippets example

Press Enter and you can see query mentioned inside the snippet.

User Snippets example

We can specify the schema and table name to execute this query.

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.

In the following image, you can see we can configure multiple Code Snippets in Azure Data Studio.

Configure multiple User Snippets example

You can see both configured Code Snippet in the following image.

Configure multiple User Snippets example

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.

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.

Configure multiple User Snippets example

In the new query window, we can see this Code Snippet.

User Snippets example

Click Enter and You get drop-down values to choose appropriate values.

User Snippets example with 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

Copy and Paste the code in SQL.json and save it. Now go back to the New Query window and call the Code Snippet.

Custom Code Snippet with multiple lines of codes

We can see the Code Snippet in the following screenshot.

Code Snippet

Once you click enter, you can see the complete query.

Execute Code Snippet

We can execute to get the required result set.

Output of Code Snippet

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

1,782 Views