Azure Data Studio tips and tricks

Azure Data Studio (ADS) has a slew of features that can improve the speed and efficiency of writing SQL. Listed below are some of the tips and tricks I've picked up since making the switch from SQL Server Management Studio.

Prerequisites:

For this post, I'm going to assume that you're familiar with writing SQL queries and have used a code editor.

Tips:

First tip: Become familiar with the Command Palette

The Command Palette is the searchable prompt where Azure Data Studio's many commands are accessed. Commands do things such as modify your current code, connect to a data source, or change a setting.

To access the Command Palette in Azure Data Studio press:

• Windows: ctrl + shift + p
• Mac: cmd + shift + p

The Command Palette is designed for performing actions from the keyboard. Familiarising yourself with this tool will help you get a lot more out of Azure Data Studio.

Quickly comment lines or blocks of code

Comment out the current line:

• Windows: ctrl + /
• Mac: cmd + /

Add a block comment:

• Windows: alt + shift + a
• Mac: opt + shift + a

Turn a block of code into one line

Got a compact statement that could fit on one line? Use the Join Lines command. On Mac, the shortcut for this is control + j

On Windows, the Join Lines command is not bound to a keyboard shortcut but can be set in Keyboard Shortcuts.

Expand * to all columns in a SELECT *

Known as Star Expansion, this takes an asterisk in the SELECT clause and replaces it with all column names.

To trigger Star Expansion:

• Place the cursor beside the asterisk *
• Trigger IntelliSense (Windows: ctrl + space, Mac: ctrl + space). You should see a concatenated list of all column names
• Press Enter to replace * with all column names.
• This also works for tables, i.e., SELECT table.*

Hide a region of a query

Want to hide a region of a query, e.g., hide a sub-query while editing the outer query. Try Code Folding.

• Windows: ctrl + shift + [, to unfold ctrl + shift + ]
• Mac: opt + cmd + [, to unfold opt + cmd + ]

ADS determines a foldable region by evaluating the indentation of lines. A foldable region starts at a line whose next line has a greater indent and ends when the indent is the same as the starting line.

Search and Replace with Regular Expressions

With a Regular Expression search and replace, we search for a text pattern instead of exact text, which lets you modify more text in one go.

To search and replace:

1. Open the Command Palette and type "replace."
2. Choose the "Replace" command.
3. Set the search to use Regular Expressions.
4. Enter the search pattern and replace as required.

The search and replace below will remove open and closing square brackets from a query in one action (if you, like me, find the square bracket syntax noisy and hard to read).

Transform to Uppercase or Lowercase

To change:

• Highlight some text
• Bring up the Command Palette (Windows: ctrl + shift + p, Mac: cmd + shift + p) and type either "upper" or "lower"
• Press Enter to transform.

Transforming the case is even more useful when you combine with multiple selections.

Further, the commands can be bound to a keyboard shortcut reducing the need to use the Command Palette.

Change all occurrences of text

Want to rename something, and in all the places it's used, e.g., rename a variable? Try Change all occurrences.

• Windows: ctrl + f2
• Mac: cmd + f2

Move a line up or down

We don't need to cut and paste to move a line. This shortcut also works with blocks of selected text.

• Windows: alt + up or alt + down
• Mac: opt + up or opt + down

Copy a line up or down

The same goes for copying a line.

• Windows: alt + shift + down or alt + shift + up
• Mac: opt + shift + up or opt + shift + down

Make many changes at the same time

If you want to make a change in many places simultaneously, then try Multiple Cursors.

To drop multiple cursors into a query is as easy as holding down the alt (Mac: opt) key and left-clicking with the mouse where you want the additional cursors.

Multiple highlighting

This same feature can highlight multiple bits of code. Holding down alt (Mac: opt) and double-clicking a word will add it to the currently highlighted selection.

Box selecting

A variation on this is Box Selection, highlighting text in a square or rectangular block. Use Box Selection by holding down shift + alt (Mac: shift + opt), then holding the left mouse button and drag where you want to highlight.

Work on one query while referring to another

Try Side by Side editing.

The easiest way to do this in ADS is:

• From the Explorer sidebar, drag and drop either a file or Open Editor group to the desired area
• Alternatively, drag and drop an editor tab to the desired area

Compare differences

Need to see the differences in queries or see what might have changed between two files? Let ADS show you with Compare differences.

To compare the differences:

1. From the Explorer sidebar, right-click on the file and click Select for Compare
2. From the Explorer sidebar, right-click on the file for comparison and click Compare with Selected

Zen Mode

If you want to focus on a query without the extra distractions of File Menus, notifications, etc., use Zen Mode.

Activate/Deactivate Zen Mode from the Command Palette.

Auto Save

Turn on Auto Save, so you'll never forget to save your work.

Auto Save is disabled by default. To enable:

1. Click the File menu
2. Tick Auto Save

Get information about a Database Object

Use Peek Definition to quickly get information about an Object, such as column names and data types, without leaving the editor and hunting around in the Object Explorer.

To use:

1. highlight an Object
2. right-click, choose Peek Definition (Windows: alt + f12, Mac: opt + f12.

Find Database Objects fast

Need to search for Database objects quickly? Use the Database Object Search.

To access the search:

1. Open the Connections sidebar
2. Expand a connection to view all Databases in the connection
3. Highlight a Database, right mouse click, and select Manage
4. Start typing the object you want to search for.

The search also supports filtering. To filter for a specific type of Database Object (e.g., table, stored procedure), we use the corresponding character followed by a colon. For example, t:users will search for all tables containing the text "users" in their name.

The following filters are supported:

• sp: stored procedure
• t: table
• v: view
• f: function

See also

• VS Code Tips and Tricks (VSCode and Azure Data Studio share the same code base)
• Gif recording via LICEcap
• Demo Database