Voice of the DBA

Author: way0utwest

  • Using T-SQL over PoSh

    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.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Daily Coping 20 Apr 2022

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to get active singing today (even if you can’t sing).

    I can’t sing. My wife says I’m not too bad if I’m not trying to hit the notes, but I don’t think that’s an endorsement.

    Fortunately, I am often in the car alone running errands. I have a few playlists, but here are the ones I’ll sing out loud today:

  • Daily Coping 19 Apr 2022

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to relax your body and mind with yoga, tai chi, or meditation.

    I love yoga, and I tell the kids I coach that I wish I’d gotten into it at 20 years old. I haven’t done tai chi, though I would like to try. I struggle with meditation, but I have friends that love it.

    I try to practice yoga 3-4 times a week, most of the time in a group class. I see beginners in there all the time, and I applaud them. I can’t recommend it enough. If you don’t want to invest in a class somewhere or you feel embarrassed, try a Yoga with Tim or Yoga with Adrienne class in the privacy of your home.

    For me, I’m heading to the gym for a yoga class.

  • Visualizing SQL

    While I think SQL is interesting, I know some people struggle with the way the language work. Someone at work posted a link to this site: https://animatesql.com/

    I think the idea is this site helps you visualize how a SQL query works. It’s not free form, and I can’t just write any SQL, but you choose a keyword and then a sample query is shown. If you press Visualize, it walks through how this query is processed.

    Below, I choose the WHERE keyword and then pressed visualize. The sample query had a NOT before the parenthesis, which I removed.

    2022-03-30 09_13_50-Animate SQL

    The animation then works through each row, showing whether it fits the criteria. Here I have captured what things look like in the middle:

    sqlviz

    Eventually this goes through the process again with the SELECT part of the query and pulls results from the yellow rows, according to the columns listed in the query.

    If you try the different keywords, you see different visuals and queries. You can modify them slightly, and the comments in the query window let you know what you change. The modifications are small, but they do let you see how well things work in different ways, such as if you change the value or remove a logical operator, as I did.

    As I tried a few different items, it made sense to me, but I wonder if it would for someone that was learning. I think that this ought to have some explanation of what is happening (i.e. comparing the name and rating in each row to be null or not, and matching the criteria). For the GROUP BY, I really think someone might not understand what they are seeing without some explanation.

    I also think the lack of NULL in the data, and showing a blank, is highly misleading as well.

    This looks like something a developer built, and I think it is creative and helpful, but like many 1.0 things, this needs some evolution and improvement.