It’s another T-SQL Tuesday! Thanks to Mikey Bronowski (t|b) for hosting. Our challenge:
I would like you to write about the tools that help you at work, those that helped you the most or were the most effective.
Whenever there is talk of switching tools, I always here someone say, “once we are on <insert tool>, we won’t have X problem anymore.” This statement is one of my pet peeves. It’s not the tool that solves the problem. It’s making sure we implement and use the tools properly to help solve the problem. The tools themselves are just that – tools. If we don’t understand the problem, then we’re just shifting the problem to another tool.
There area so many tools that get implemented at work these days. While I use them to varying degrees, I am not always using these tools on a daily basis. And in many cases, I’m not the person responsible for setting them up. I’m a SQL Developer so my daily toolset is much smaller.
Honestly, the vast majority of my time is split between Management Studio (SSMS) or Azure Data Studio. I’m pretty simple\straightforward this way. I started playing a lot more with Azure Data Studio over the past year, but I find I’m not able to make the switch to using it full time. It really depends on the task that I need to do.
So what tasks do I do often and which tool do I use?
Minor SQL script editing: SSMS or Azure Data Studio
This really depends on what other tasks I’m doing at the time. Whatever I was working in before is where I’ll do these changes.
Major SQL script editing: SSMS
The main reason I like using SSMS for major code changes is partly because I’ve become so used to it. But the real reason is that I have SQL Prompt installed. I just started using it in the past 2 years but I get why people who have used it don’t want to go without it afterwards. When I’m writing major changes and I need to insert into a table or execute a stored procedure, SQL Prompt will create my insert list or list out the parameters for me to populate and it saves time. The other feature I like is the ability to hover over an object and have Redgate SQL Prompt tell me where the synonym points to or list out the columns in the table or view as a grid or as the CREATE statement. Or being able to see the code inside the procedure or function without having to call sp_helptext. As I’m learning new databases, this is invaluable to me. I also like having the tab history so I can go back and find that SQL I used last week that I didn’t think I would need again and didn’t save. There is a SQL Prompt extension for Azure Data Studio, but it’s not as extensive as what’s available in SSMS.
SQL Agent Jobs: SSMS
I don’t have to do this sort of thing often but for administrative tasks, SSMS is still the winner.
Connecting to certain environments as a different Windows user: SSMS
Every now and then, I have to connect to an environment with a Windows user that is not my regular login for security purposes. The recommended installation for Azure Data Studio is for the local user, which was run as my regular login. We don’t treat Windows logins the same way as a SQL login so we have to run the application as that other user. But this means I would have to reinstall Azure Data Studio so I can have all users access it.
Admittedly, this may be an issue of where I’m too lazy to do the reinstall. But when I think about how else I use Azure Data Studio – like using the Explorer to open local files or connect to source control, I really need to run it as me. So it’s simpler for me to use SSMS for this.
Source Control: Azure Data Studio
As I said above, I use Azure Data Studio for source control. In fact, <SPOILER ALERT> my final demo for my “Practical Starter Guide to SQL Notebooks” is to check in changes to scripts from the session to my GitHub. I don’t have to do anything to fancy in Git – I just need to do the basics: pull down from main, create a branch, stage changes and check into branch, push branch so I can create a PR, then switch back to main. Oh, and do comparisons between the changes I’m about to make with what’s check in. It’s all there and simple. And if I need something more, I may use TortoiseGit in Explorer windows to get the logs or rename files, etc.
PowerShell: Azure Data Studio
I’m not doing anything to complex with PowerShell. I’ve installed dbatools but it’s more to make sure I have them if I find a case where I need them. But often I’m using PowerShell to run a script. So there something nice about making a quick change to a script and then going to the terminal at the bottom of the screen to run my change.
SQL Notebooks: Azure Data Studio
Right now, you can only create notebooks from SSMS so Azure Data Studio is really the only option to run them. I don’t have too many notebooks but I like having them handy.
Minor Performance Tuning: SSMS
I know you can do the graphical showplans in Azure Data Studio. But SSMS is my comfort zone. If I need to do performance tuning, this is where are I start
Major Performance Tuning: Plan Explorer
If I have to do major performance tuning, I ditch SSMS for Plan Explorer. I had to do a major rewrite for performance reasons and I essentially used Plan Explorer as my tool. I loved being able to make small, incremental changes to see where I could get the most impact. I liked being able to note what change I made so I could keep track. And if anyone had any questions, I could save the session and share the file so someone else could open it and see what I had done. I’m sure that I barely used half of the features available to me but it made my job that much easier because I could see the improvements or the backward steps with each change I made. I wasn’t expecting to use Plan Explorer for this sort of task but I highly recommend it.
Yes, I know I said I was pretty simple and stayed within SSMS or Azure Data Studio. But as you can see, those tools get a lot of extra help from other tools. It’s still a relatively small toolset, but they help me get the job done.
Thanks again to Mikey for giving us such a great topic. Can’t wait to see what other tools people use to help them do their jobs. I’m sure I’m missing a few.
Deb the DBA 
I too use ADS 🙂
LikeLike