In the previous article, we examine Azure Data Studio, a new IDE from Microsoft that is lightweight and based on their Visual Studio Code editor. That article looked at installing and getting started with the tool. This article will talk about a few of the basics of how to work with the tool to write code against SQL Server databases.
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
It's the database, not the instance
As a longtime user of Management Studio (SSMS), I've gotten used to connecting to instances and working with all the databases stored on an instance. That changes a bit with ADS, which really has a database-centric point of view. That's a change, and we'll start looking at how this affects our work.
When I first open ADS, I see a blank screen and a side menu. Your side menu may default to the left, but I don't like the menu moving my code, so I toggled this to the right.
I have a few options on the main screen. Each of these helps me get started with code, though not without a little prep. If I click F8, this is the equivalent of clicking the top icon on the right, the server icon. When I do that, I get a list of connections that I have set up to instance, but really these are to specific databases.
For example, if I click the arrow next to "SQL2017", I see this:
Similar to my SSMS Object Explorer, but this is for a single database on this instance. Which one? I have no idea, though to be fair, this is my fault. I didn't give this a good name, so it's not apparent to me where this goes. I can find out, but I need to prepare for this. Let's add another database connection that's better structured.
Click the "New Connection" at the top to get started. I've shown this in the image below.
Now we get a dialog that allows us to set the normal connection details.
Yes, I have a SQL Server 2014 instance and I'm building a new connection to it. Most of the settings are the same as other connection dialogs, but the last two are interesting. I can group this, as I can in a CMS or in Registered Servers in SSMS. My LocalLab server group is listed, and I can pick that. I can also set up a new group, or use no group.
The last text box is for a name for this machine. I can pick really any name here and don't have to conform to the machine/instance format. I like this as with a large group of servers, I might want to have a more descriptive name, such as the one I've chosen below.
I would make sure I include the machine and instance in the name, but I can add things like PRODUCTION or other cues for which machine this is. The advanced tab is the normal set of connection properties, like encryption, that you might want to set.
Clicking "Connect" will allow me to connect to the server instance and will open up the database dashboard. I can see the recovery, last backups (database and log), compatability level and owner. I also get a set of tasks and a search widget that lets me look for different types ofobjects.
There is an "edit" pencil icon in the upper right corner of the screen. This allows you to "edit" the dashboard, which we'll discuss later. The interesting thing here is that my connection is to the database. If I want to look at another database, I have to open a new connection.
If I click the "New Query" button, I'll get a new query tab at the top. Note that this one has a file name as a title as well as the instance and type of connection. However, i can't see the instance or the full "Integrated" even if I grow the application to full screen. Even the tooltop is incomplete:
This is a normal query window, as I'd find in SSMS. I can write code here as I normally would, and let's do that.
Query Writing
To me, the main function of ADS is writing code and saving it. I'm less interested in managing an instance, though I get why there would be extensions to help with certain features as developers need to perform backups, add filegroups, etc.
If I start typing
I get intellisense, but the intellisense requires something to work with. For example, I can type "se" and I get the SELECT keyword first. A quick "tab" and I have the work entered.
As I move along, I get some keywords, but for something like this, I don't get any values:
I'm used to SQL Prompt, which would give me a list of something. Here's the view from SQL Prompt for the same code (hitting the space after FROM):
In any case, if I type "c", I do get some limited view of objects. I'm not completely sure what the criteria here is, as I only see one table that doesn't even start with "c".
If I go to the dashboard and search for tables that have a "C", I get this:
That's strange, and likely some bug in intellisense. Hopefully we'll get Prompt ported to ADS soon and I won't have to worry about this.
If I remove the C and pick "a", I get the table, dbo.abc, so something is amiss.
To execute the query, the default is F5. I would install the Keymap extension and get the expected SSMS behavior with CTRL+E. In any case, once the query runs, you get the results in a middle pane and then the messages below this.
There doesn't appear to be a way I've found to move results or send them to text. I can, however, close the messages tab by clicking on the little triangle next to "Messages". If I do this, the next time I run a query, the section remains closed.
I do have options on the right side of the results row. There are five icons there that give me these options:
- Save as CSV
- Save as Excel
- Save as JSON
- Save as XML
- Chart View
If you hover over any of these, a tooltip lets you know which is which. I've put the mouse over the fourth one here.
The first four will output your results into the format selected. The last one doens't work on many result sets. Clicking it on my results didn't do anything. I know this worked at one point, but it appears to be broken.
The Explain button at the top will display the query plan. This gives a graphical view when you click on it.
You can also click on the Top Operations button to see data in a tabular format.
There are buttons at the top to change the database, change the connection, cancel the query, just as there are in SSMS. ADS works quickly, and overall seems responsive. Some larger queries appear to take longer to run for some users, but it hasn't been that noticable for me.
Writing code is writing code. There is some basic intellisense, but it's limited. If you want to write SQL code and test it, this editor works really well. I find the response to be good and the editor appears stable. There aren't many options necessary or available in the editor, but it works well. You can highlight code and just run that section, copy paste work well, etc. It's an editor, basic, but it works.
Old Eyes
One of the things I do constantly with my browser is change the size of the text. I like to see a lot on the page, but sometimes I just can't read the font and need to make it larger. CTRL+= zooms me in and the same thing works in ADS. Here's the normal size my ADS started with:
Here is the view after hitting CTRL+= twice.
This may be hard to see in the screen shots, but the text is noticeably larger. Note that the side menu with a folder open is also larger.
We also get various color themes that we can set for the editor. That's important to some people and many have bemoaned the lack of themes in SSMS. In the File menu, we can open preferences and see the themes. CTRL+K, CTRL+T or a search in the command palette for "theme" and make choices. We get a few installed by default.
Here are how a few look. Here is Solarized Light
and here is Abyss
You'll have to experiment with which one helps you code better, but you get some options and can customize things if you desire.
Working with a Folder
One of the things I often do is save related code in the same folder. I used to do this for scripts that were needed, but now I often use a folder with git and version control. In ADS, I can open a folder and see all the code stored in that folder. For example, I opened a folder with a bunch of demo code for one of my presentations. Once I select the folder, I see the code on the side bar. I'll see all the files, including those that aren't .sql files I can edit.
The "Open Editors" section at the top of the file list (on the right) shows me which files are open in editing tabs. I can click on one of these to switch to that tab. I also see a note about which files have changes that are unsaved in them. That's a handy feature to let me know I've actually done work in a tab and might want to save my changes.
The other interesting thing is that one of my files had a red highlight. This means that ADS has detected a potential code problem. If I hover over the file, I'll see a note to this effect.
If I select this file, I see a red bar partially down the sidebar. This shows me there's an issue below the section where I'm editing.
When I scroll down to that section, I'll see this:
This code is perfectly valid. The ALTER FUNCTION works, but when I scroll up, I realize there's a SELECT query without a GO above this. Not a problem in a demo, but certainly this code won't run unless I highlight this section. A nice feature that ADS let's me know of potential issues in code.
There are more things to look at with folders, and certainly with version control integration, but this is a good start to working with your code.
The Good Things
There are some things I really like about ADS. Certainly it starts up quickly. If I close the application and then hit the Windows key, type "Azure" and hit enter on Azure Data Studio, the application loads in about 5-6 seconds on my machine. Compared to SSMS, I don't worry about updates or restarts that are needed becuase of the speed of the application. I prefer software the starts quickly, so this is much better than SSMS for me.
The second thing I like is that my query windows are opened back up. When I restarted ADS, I saw this:
The query that I hadn't saved, was opened. I get this with Tab History in SQL Prompt, but it's nice to have this by default as I sometimes close a window I didn't want to close. Now I do need to reconnect my window to a database, but it is nice that the editor can open quickly and keeps my last work there. This appears to work whether I've saved the file in the tab or not.
I can also open PowerShell files from the editor. Here I have a SQL file and a PoSh file open.
I can easily work with both, I get highlighting and color coding, and intellisense with PoSh, shown below.
As I do more work with both databases and PoSh, I like this. There's even an integrated terminal, just like in VS Code. For comparison, here's that PoSh file opened in SSMS.
The command palatte is also something I find more and more handy. I used to hunt through menus, but as I get used to CTRL+Shift+P, I more easily make changes to search my instance with SQL Search or change settings. I use a similar feature in a few other pieces of software and I think this is an improvement over menuing. Here's the command palatte in ADS, with my recent commands at the top.
Searching
One of the common things developers need to do is search their code. There is a search item on the menu bar and it works well. I opened a number of files and searched for a partial fragment. I got a set of results that showed me a partial line of code with the file in which it is found.
I don't often lose track of code, but it does happen. Being able to quickly search through some files is nice. There are options to use a RegEx, match case, or the whole word. I prefer the defaults to be loose, and they are. I can always be more strict, but I often am thinking about the problem, not things like casing, so I'm glad these defaults are set quite loose.
Annoyances
There are some things I don't like about ADS. It's far from perfect, and I'm not sure this is anywhere near a replacement for SSMS, but I know things will change over time. These are issues I have with the 1.4.5 version released in February 2019.
First, the results appears to be stuck as a grid value, and for each new window, with the messages below it. I often want to clear up as much real estate as possible, and occassionally want to get the resutls as text. I can't do that here.
Second, for coding, there is the hassle of having an object explorer ready to browse my instance. Sometimes I can't remember a name and search isn't a useful. However, the object explorer is linked to the connection. If I open a connection to the sandbox database, that's what I see in the object explorer. If I changed the query window to another database, I don't have a way to browse that database without setting up another connection. You can see that below.
Perhaps it's me, but I've gotten used to an instance view of a number of databases, and that's been a habit for decades. I often connect to a default database, knowing I might need to work in a couple of different databases on that instance. Here I need connections set up to get a browse view of other databases.
There are other ergonomic issues that are less important, but equally annoying. There's no "open folder" button for the Explorer menu. I have to remember to click CTRL+K, CTRL+O or open the File menu. Some of the small icons on windows disappear if I resize things, such as the various "save as" icons in results. I have to open and close something or resize again to get them back.
Other items sometimes don't work. I know this is a young editor, but it seems like some of the icons or items are flaky. In addition, the detailed documentation on how ADS works is quite sparse. Lots of features aren't described, with almost a view towards "experiment and figure it out" on the part of the Microsoft tools team. I don't like this, mostly because I get stuck sometimes and I'm trying to get work done. There is also a lot of information like this article, often with less details, that crowd out any chance to searching with Google to determine how the chart view should work or what parameters exist around it's behavior.
Conclusion
Overall, ADS shows promise, and I look forward to seeing it mature. I really want a better extension market, especially for the Redgate tools, and I'm hoping that comes soon. The editor starts and runs quickly, which is something I value. It seems fairly stable, and let's me focus on coding without many distractions.
That being said, this is a slightly different paradigm, closer to a text editor, and it requires some getting used to the flow. SSMS is more like an extension of the instance that I work with, which sometimes makes it easy to make mistakes in editing code. I've gotten used to that and so ADS feels foreign. As a development editor, this is a better paradigm, forcing me to work with code first, use version control, and focus on what I'm doing. If I get distracted with another project, I'd probably open another instance of ADS (in 5-6 seconds) and make a new connection there.
That's probably a better paradigm for work anyway.
In the next installment of this series, we'll look at integration with Version Control and how that affects your coding day.