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.
For this post, I'm going to assume that you're familiar with writing SQL queries and have used a code editor.
- First tip: Become familiar with the Command Palette
- Quickly comment lines or blocks of code
- Turn a block of code into one line
- Expand * to all columns in a SELECT *
- Hide a region of a query
- Search and Replace with Regular Expressions
- Transform to Uppercase or Lowercase
- Change all occurrences of text
- Move a line up or down
- Copy a line up or down
- Make many changes at the same time
- Work on one query while referring to another
- Compare differences
- Zen Mode
- Auto Save
- Get information about a Database Object
- Find Database Objects fast
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.
Comment out the current line:
- Windows: ctrl + /
- Mac: cmd + /
Add a block comment:
- Windows: alt + shift + a
- Mac: opt + shift + a
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.
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.,
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.
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:
- Open the Command Palette and type "replace."
- Choose the "Replace" command.
- Set the search to use Regular Expressions.
- 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).
- 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.
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
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
The same goes for copying a line.
- Windows: alt + shift + down or alt + shift + up
- Mac: opt + shift + up or opt + shift + down
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.
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.
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.
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
Side by side editing of the same file
Side by Side editing also works for the same file, handy when working on a long query spanning greater than the screen height.
This functions just like Side by Side editing, but instead, you drag the same file or Open Editor group from the Explorer sidebar onto the desired area.
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:
- From the Explorer sidebar, right-click on the file and click Select for Compare
- From the Explorer sidebar, right-click on the file for comparison and click Compare with Selected
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.
Turn on Auto Save, so you'll never forget to save your work.
Auto Save is disabled by default. To enable:
- Click the File menu
- Tick Auto Save
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.
- highlight an Object
- right-click, choose Peek Definition (Windows: alt + f12, Mac: opt + f12.
Need to search for Database objects quickly? Use the Database Object Search.
To access the search:
- Open the Connections sidebar
- Expand a connection to view all Databases in the connection
- Highlight a Database, right mouse click, and select Manage
- 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: