Why would you use SSMS/T-SQL over PowerShell (PoSh)? When is T-SQL directly a better option than PoSh? That's a question I ask myself regularly as I see articles and blogs that discuss how to accomplish a particular task using one tool or the other. There is plenty of overlap in the capabilities for each language when it comes to working with SQL Server, so this is a decision I think about regularly. This is especially true if you use dbatools.

There also appears to be a bias towards one tool or the other for each individual. Many people traditionally have used T-SQL to accomplish most database tasks, and they tend to always look for a solution with a script in SSMS. Others are excited by PoSh and I have seen plenty of questions on the SQL Server Central forums asking how to structure their code in that language. In both cases, there is no shortage of people that argue that you should use T-SQL instead of PoSh or vice versa.

Personally, I think that there are lots of development items where I'd use T-SQL. For any sort of schema change, most data changes, and a lot of database administrative tasks, I would use T-SQL first. Trying to alter a table in PoSh vs. T-SQL doesn't make sense to me. Now the deployment of these changes is something where I'd use PoSh to run the T-SQL, which is what we do in the Redgate Deploy tools.

I was with a panel recently and all the individuals on the panel said they wouldn't use PoSh over SSMS for much of anything. The exception is where a task involved working with files or folders in the file system. PoSh excels here, and for work that might delete old files or move files from one folder to another, PoSh is preferred.

I think the defining line for me is whether I need to accomplish a task inside of SQL Server or outside of it. When I cross instances or work with the file system, then PoSh is my preferred method. I can use xp_cmdshell or a linked server as well as anyone, but I prefer not to. Anything inside SQL Server, usually has me reaching for SSMS instead of VS Code.