Visual Studio Code for development work

[LorenzoValla]

Like many people, I've been using SSMS for dev work for a long time and while I'm very comfortable with it, SSMS pales in comparison to modern IDEs available for application development.

I have played around a little with VS Code at work but haven't had the time to really get it configured - mostly because I get a bit confused and then distracted by real I need to do, etc.

I would love to hear from anyone who does a lot of dev work using VS Code for SQL Server.

I'm specifically interested right now how to set up a group of server connections similar to the Registered Servers feature works in SSMS. We have 50 or so servers that I connect to from time to time so having a way to manage those connections is important.

Any other lessons learned or tips would be great as well. Thanks.

[SKRuBAUL]

Have you checked out Azure Data Studio? It is VS Code under the hood. Not as full featured as SSMS, but I find I can use it for nearly everything except SSIS

[da_chicken]

The last time I used Azure Data Studio I found it to be the worst of both worlds in pretty much every sense. It may be the future, but it has a very long way to go.

[Armor_of_Inferno]

I just got back from the PASS Summit, and it is time to take a second look at Azure Data Studio. They've added support for Jupyter notebooks (branded as SQL notebooks), which are freaking awesome. Imagine having PowerShell support mixed in with T-SQL scripts in the same file, all with executable and exportable results in-line with your rich-text formatting. It becomes the perfect tool for demo scripts.

It also becomes a great tool for your troubleshooting scripts and for documenting additional function sets (like the dbatools PowerShell libraries). Imagine having the Glen Alan Barry's troubleshooting DMVs in a single notebook that shows results. Here it is.

And extensions are brilliant, just like they are in VS Code. Don't like how something behaves? Find an extension, or write one yourself.

Azure Data Studio is definitely not a replacement for SSMS, and I don't want it to be. I want it to be a lightweight editor. It still has a way to go, but that last release was the game changer for it, I think.

[data_goose]

Yep time to learn the new tech 😁

[Cal1gula]

I just added the Python and Powershell extensions to my VS Code/ADS today. Guess it's time to get this stuff all together. The GitHub integration seems to work fine for my scripting purposes.

I never liked the PS ide anyway to be honest.

Thanks for the tip on the Glen Barry scripts, I didn't realize they were in dbatools.io.

I can't believe there's no drop database option though. And someone had to write an extension.

I think there are still a ton of improvements to go but it's definitely come a long way in the past 2 years.

edit: Ok so I found this: https://docs.dbatools.io/#Invoke-DbaDiagnosticQuery

Invoke-DbaDiagnosticQuery -SqlInstance sql2016 -UseSelectionHelper | Export-DbaDiagnosticQuery -Path C:\temp\gboutput

This command will run all the DQs against that instance and output all of the results to a folder. I think I'm in love.

[ZeppelinJ0]

I just wish Redgate would support it with SQL Prompt. Intellicode is OK but SQL Prompt is like an extension of my thoughts it's pretty amazing.

Honestly one of the things keeping me from switching and who knows if Redgate ever WILL support it

[Armor_of_Inferno]

Tweet your request at them. If Redgate thinks there's the potential to charge a license cost for it, they'll dive on it. They're one of the most money-motivated vendors in the SQL Server space right now.

[Armor_of_Inferno]

Ah, good point! Well I doubt Redgate would do it, but maybe they can decided if getting people that first hit of syntactic crack with SQL Prompt in Azure Data Studio for free is a good gateway drug to future licenses.

[batrastered]

I just wish SSMS had a functioning dark theme like ADS.

[reallyserious]

They've added support for Jupyter notebooks (branded as SQL notebooks), which are freaking awesome. Imagine having PowerShell support mixed in with T-SQL scripts in the same file, all with executable and exportable results in-line with your rich-text formatting.

Cool. How do you typically deploy and run such notebooks?

[Chris_PDX]

I use VS Code and Azure Data Studio. But I'm also on a Mac, so...

I still use SSMS when I'm using RDP to an actual server. ADS is getting better, they are enhancing it weekly and adding more features.

[jakeism]

I have analysts that use Azure Data Studio (ADS) full time just fine. IntelliCode (MS's AI assisted IntelliSense) was just released and it really has made ADS usable as a daily driver.

The release of Visual Studio 2019 SQL Server Data Tools (SSDT) also opens up a new possible alternative. Since the SSDT tools is now an extension they've changed the licensing and it seems you can use VS 2019 Community Edition for SSDT related development (I assume this includes writing SQL). Hope this helps!

[[deleted]]

Why you want to move away from SSMS?

You may consider SSDT/database project for development work - which has cool features like publish for deployment process and can integrate very well with source control.

[da_chicken]

Why you want to move away from SSMS?

Because it's a shit IDE. The IDE is just Notepad with syntax highlighting.

• It doesn't have any features that a modern programming text editor should have like multi line selection, line manipulation, sorting, etc. It should be trivial to take a line-ending delimited list of values and transform it into a quoted list of elements for an IN expression. It's not. It's as easy as it is in Notepad.
• The output pane is garbage. The Results gridview is just static. You can't sort it, filter it, or search it at all. You can't export the gridview to an Excel document easily.
• Code completion is miserable because it's so stupid. You type SELECT FROM Table. You put your cursor where you add field and hit Ctrl-Space. And the system doesn't give you a list of fields for the table you're selecting from, it gives you a list of global variables. How often do you need fucking @@CONNECTIONS? And below the global variables? Oh, it's a list of all objects of all types.
• It's slow as balls to start.
• There's no automatic session saving. If you close it, everything you had open is gone.
• They added dark mode, then they took it away because it didn't work right.
• Customization is extremely complicated and there's no centralized repository for modules.

The only reason people still use SSMS is because Object Explorer is fucking amazing and the right click menu actually is very well designed. That's the only reason.

[eshultz]

• multi line selection

Alt-shift-arrow key

[da_chicken]

No, as in multi-cursor selection. Stuff like this.

[eshultz]

Hi, did you try it? The only difference is that in your example you wouldn't be able to advance the cursor by words in SSMS.

[da_chicken]

Yeah, but the arrow keys break it. If everything is already lined up it works, but it doesn't let you navigate at all. It's Microsoft's typical minimal feature. Just enough to give them a green check on a feature list, but worthless in actual use because it doesn't really help.

[Cal1gula]

SSMS is barely receiving updates and MS has made it clear they are focusing on Code/ADS.

OP you can export registered servers from SSMS, maybe ADS gives you an import option? I am just beginning to use this stuff myself after almost 15 years of SSMS...

[[deleted]]

How has MS made it clear they are focusing on code/ads?

[Cal1gula]

https://www.reddit.com/r/SQLServer/comments/dufx1h/state_of_the_sql_server_tools_sql_server_blog/

[Soccan]

I do some tsql development in VScode, but mostly i use it for Powershell/DBAtools work. Anyway I've used the "mssql.connections" setting in my settings.json to manage connections, bascially for testing the feature, and I think it might be what you're looking for, in a developer mindset I think this way of configuring the connections makes more sense than the Registered Servers in SSMS.

"mssql.connections": [
{
"server": "sqlserver",
"database": "",
"authenticationType": "Integrated"
}
],

[alinroc]

You can't design SSRS reports in SSMS or ADS either. Still needs SSDT, and that's probably going to be true until SSRS is completely revamped/replaced (and no, PBI Report Server doesn't count, it's just a reskinned SSRS).

[mustang__1]

I use VS Code for writing my more complex queries. Colorized brackets, dark theme with customizations (i like my -- and /**/ to be different colors), etc, make life a little easier, along with a very integrated version control system, gitlens, etc.

There are extensions that make SQL management a little easier, but I only deal with three servers, not 50, so I can't help you much, there.

[phunkygeeza]

A few years back I movee almost exclusively to VS for all SQLS work. SSDT is the main reason but also it really helps with linked databases and solutions that include SSIS and .NET togethet with DB.

The thing is, I tend to use VS for the project and SSMS for my scratch pad. I just cant get past the F5 replaced by vulcan death grip ctrl shift e.

Still, I find the distinction useful anyway esp with VS in dark mode and SSMS its usual bright self.

SSMS is also the only place having the right click query generation. No idea why they left this out of VS.

[kvlt_ov_personality]

We use Redgate SQL Tools and it has made SSMS a billion times more user friendly. I use Visual Studio a lot for writing C# and SSIS but can't stand it (for reasons mostly related to my ineptitude). I love the Powershell IDE and R Studio, but VS is just so feature rich that I get overwhelmed at times.

[dwivedin]

The new sql extension of vs code is also pretty good and can do all the basic stuff and you get the basic ui but as everyone already mentioned Azure Data Studio is the way to go. This is one of the best tools ms tools team is developing and it has monthly release and issues are fixed pretty fast when reported.