Azure Data Studio (ADS) is a lightweight IDE built on Visual Studio Code. I've written a few articles on how ADS works, shown here:
- Getting Started with Azure Data Studio
- Getting Comfortable Writing Code in Azure Data Studio
- Using Notebooks in Azure Data Studio
- Using Azure Data Studio with TFVC
- Using Azure Data Studio with Git
- Editor Tips and Tricks for Azure Data Studio
If there are other features you're interested in, let me know. This article continues the series, looking at some of the tips and tricks that might help you work a little more effectively in Azure Data Studio. I'll cover some of the ergonomic features, as well as how different languages might be used in ADS. The main sections in this article are:
ADS has changed quite a bit since my last article, with lots of updates. There's one every month, as well some hotfixes. Keep up to date with these, as the team at Microsoft is constantly evolving things.
ADS is designed to be a lightweight, quick and easy to use editor for coding T-SQL. There are a few neat things that I have found to make it easier to work with code that I want to show here. We'll look at some editor tricks, handy extensions, and some customization.
If you're like me, sometimes you get lots of code in a file. While we would all love to see procedures and batches of code limited to what's on the screen, that's often not possible. It sometimes seems like I'll be working with two pieces of code and I have lots of stuff in the way. Suppose I have some code, I can put my cursor in the left sidebar, and I'll see some arrows.
If I click on any of these down arrows, a section of code will collapse. This can make it much easier to look at some code and hide other code, like parameter checks.
In SSMS, some of you may know that you can use Shift+Alt with arrow keys to work with the same space across multiple lines. For example, I can use ALT+Shift+arrows to highlight more than one line in SSMS.
That doesn't work the same in ADS. In ADS, if I want this to work, I need to use CTRL+ALT and then down arrow. This gives me separate cursors.
I can then let go of these key and hold down Shift to arrow and select multiple characters.
As in SSMS, I can type different items then in this space. Or delete things. However, what about this code. I see this type of structure often in work I might do.
INSERT INTO Workout (WorkoutDate, ActivityGroup, WorkoutTime) VALUES ('8/1/2020', 'Swim', '38:00'), ('9/1/2020', 'Swim', '37:20'), ('10/1/2020', 'Swim', '36:15'), ('11/1/2020', 'Swim', '36:35'),
If I wanted to edit the dates, maybe change to 2019 because I've mistyped this or am testing something. Using the multi-line trick doesn't work as well, because we have the dates in different places on the different lines (off by one character). I can hold down the ALT key and click in a few places to add additional cursors. This lets me pick the places where I want the same edit.
Once I have the cursors in the right place, I can then let go of ALT, hold Shift, and then use the arrow keys to select the characters I want to edit.
What if I have a typo in the code in many places. I can use multiple mouse cursors automatically with CTRL+D. Look at this code:
INSERT INTO Workout (WorkoutDt, ActivityGroup, WorkoutTime) VALUES ('8/1/2020', 'Swim', '38:00'), ('9/1/2020', 'Swim', '37:20'), ('10/1/2020', 'Swim', '36:15'), ('11/1/2020', 'Swim', '36:35'), GO select workoutdt from Workout where workoutdt = '8/1/2020'
I've mis-typed the WorkoutDate as WorkoutDt. If I want to edit this, I can search and replace, but I find that cumbersome at times. Here I can select some characters, like the WorkoutDt word. Note the other instances are highlighted, but not selected.
If I press CTRL+D twice more, I'll see all the items selected, with separate cursors. Each times I click CTRL+D, it selects the next occurrence of the characters.
Now I can type and correct this quickly. This is sometimes easier, sometimes harder than search and replace, but it's worth experimenting with in your work.
Imagine you write some code like this:
INSERT INTO Workout (WorkoutDt, ActivityGroup, WorkoutTime) VALUES ('8/1/2020', 'Swim', '38:00'),
I do this all the time for test data, and I need to copy the values line. I can hold down Shift, then arrow up to highlight the line, then CTRL+C, then down arrow, then CTRL+V to paste this. Not hard, but a neat trick in ADS I like.
If I have the cursor in the line with the data, I can hold ALT+Shift and down arrow. This is what I'd do for a multi-line cursor in SSMS. However here, it duplicates the line. That is cool.
Play around with the up and down arrows, and you'll get the line in above or below your cursor. With the multi-line editing, this lets me set up test data quickly.
Let's imagine I'm working on some code, maybe I've got this query in a proc. I realize I really want a variable. What I can do is just start typing the code I need in that spot and then move it. Here, I've typed the declaration and check below the query.
I can highlight this code (mouse or Shift+arrows). Once it's highlighted, I can let go of Shift (because that creates a copy) and hold ALT+up arrow. This will move the code up one line. If I up arrow 5 times, I'll see the code moved above the query.
If you're like me, you end up with many tabs open at times. Finding code can be a pain, especially if you haven't necessarily named the file intuitively. CTRL+F opens the find within this file, but if you want to search across files, you need to use CTRL+Shift+F. This opens the sidebar search. If you type something, like INSERT, and hit Enter, you'll see all the results across files.
Note: I had CTRL+Shift+F globally mapped to Evernote and had to disable it, since I dislike global shortcuts like this.
Second note: SQL Search is a better search if you want to look inside your database.
I'll talk about PowerShell below, but one thing I do it use the Terminal at times in ADS. However, the default font is too small for my old eyes. Just like in VS Code, I can fix this with the command palette and the settings item.
In here, there are a number of settings I can alter, including the Terminal font (under the Features menu).
This takes effect immediately when you leave the font box focus, and I have found 18 to work well for me.
If you need to zoom in the editor code, which I also do, you can search zoom in the command palette, or CTRL+ + ( CTRL+ -).
The main extension I found handy is the Keymap extension. This is available from the extensions sidebar, and this gives you SSMS like behavior. I have decade of muscle memory for CTRL+E to execute queries, so I really need this.
I work for Redgate, so I have SQL Prompt installed on my system. There is a SQL Prompt extension, that gives you formatting and snippets from your SSMS install. Those are the two more heavily used features for me, so getting them in ADS is nice.
I also find SQL Search to be very handy inside a database, especially a large one.
A few of the other ones that I find handy are the sp_whoisActive extension from Adam Machanic, and the First Responder Kit from Brent Ozar. As you can see in the image above, there are 51 recommended ones I don't have installed.
There are lots of other extensions to do specific tasks, and Microsoft is releasing lots of new functionality as ADS extensions, such as the Big Data Cluster tooling, so play around and look (search) in the extensions tab for items that might help your job.
This article continues the Azure Data Studio series I have with a focus on productivity enhancements. These are tips and tricks that make ADS work better for me. The editor items to search and work across lines are especially efficient for me.
I still haven't made it my main editor, but I do continue to work with it and I am glad to see Microsoft evolving it as a xplat editor since I know SSMS will never move away from Windows. We certainly do need some tools for Linux as I expect to see some instances installed there over time.