Using T-SQL over PoSh

  • Comments posted to this topic are about the item Using T-SQL over PoSh

  • I prefer to use Powershell, and Invoke-DbaQuery from within my Powershell scripts for things that require T-SQL. The source of all scripts is then in my Powershell library, instead of spread over two (or more) locations.

  • This is something where the question itself is kind of nonsensical, like what is better eating a steak or watching a good TV show.  There might be some overlap between them but functionally they're very different and can't be freely interchanged.

    Powershell is designed to interact with the OS, specifically windows, and provide a lot of useful OS level orchestration.  T-SQL is designed to interact with a sql engine.  That powershell can interact with SQL is a side effect of it's integration with the OS not what it's designed to do.  Now there definitely plenty of cases where you need both good OS level orchestration and complex SQL for the same task and that's the point where you would start combining the two, and I guess at that point the question becomes how much do you do in powershell vs SQL.

    And you also can't ignore SSIS which offers the same ability to combine some OS functionality itself with SQL ontop of some ETL ability, not to mention just calling powershell as well.

     

  • ildjarn.is.dead wrote:

    I prefer to use Powershell, and Invoke-DbaQuery from within my Powershell scripts for things that require T-SQL. The source of all scripts is then in my Powershell library, instead of spread over two (or more) locations.

    I love dbatools and that's a good way to get into T-SQL when you need it. The challenge often is ensuring you have scripts version controlled, available to others, and audited where appropriate.

  • ZZartin wrote:

    This is something where the question itself is kind of nonsensical, like what is better eating a steak or watching a good TV show.  There might be some overlap between them but functionally they're very different and can't be freely interchanged.

     

    Semi-agree. The question is more about where you draw the lines and how you decide when/where to use them. Not which is better or more useful.

  • Hate to admit I didn't even know what PoSh is...

    Rick
    Disaster Recovery = Backup ( Backup ( Your Backup ) )

  • dbatools.io is the main reason I jump into PoSh when interacting with SQL Server. Just so much value there. On the other hand, if a fancy crafted SQL is required for performance reasons, then wrapping T-SQL in a sproc to generate good query plans is what I do. Then, perhaps, use PoSh to call the sproc. I view PoSh as just another application level code that calls into SQL Server.

    Couple of other considerations. Need to have people on the team that are comfortable using and developing scripts in PoSh. And, if you work in an environment where internet access is limited (can't download files, can't update versions of tools, etc), and must go through levels of approvals just to be able to run a simple PoSh script.... well, straight T-SQL is the path of least resistance. 🙂

  • Steve Jones - SSC Editor wrote:

    ZZartin wrote:

    This is something where the question itself is kind of nonsensical, like what is better eating a steak or watching a good TV show.  There might be some overlap between them but functionally they're very different and can't be freely interchanged.

    Semi-agree. The question is more about where you draw the lines and how you decide when/where to use them. Not which is better or more useful.

    Right that's why it depends on what the job is doing, not which is inherently better.

    For example let's say I just need to run a bunch of SQL statements, well that makes sense in a stored procedure executed from say a SQL Agent task.  On the other hand maybe I need to do some file manipulations, well that makes sense to me as a powershell script scheduled in windows.

    Now for something more complex that say has a mix of file manipulations, maybe some other windows integrations like AD or web services as well as some complicated SQL it would very much make sense to have some the job in powershell and some of it in views/stored procedures etc in SQL server.  At that point I would look at what the driver is, is it some kind of OS level event?  Is it a SQL event?  What is the first thing the job does, IE the first point of potential failure.

    Another factor would be how the job will deployed, maybe I want to be able to run against multiple databases without having to deploy anything directly to them?

     

  • skeleton567 wrote:

    Hate to admit I didn't even know what PoSh is...

    PowerShell.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ZZartin wrote:

    This is something where the question itself is kind of nonsensical, like what is better eating a steak or watching a good TV show.  There might be some overlap between them but functionally they're very different and can't be freely interchanged.

    ...

    It's all about using the right tool for the right purpose.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • "When all you have is a hammer, everything looks like a nail".

    Rick
    Disaster Recovery = Backup ( Backup ( Your Backup ) )

  • Indeed. Part of what I wanted to convey is that you need to look at the problem and decide if it's a nail or screw.

    I think most people get using the right tool, but how do you know what the tool is? What is your guideline for choosing a hammer v screwdriver, or PoSh v T-SQL?

  • Steve Jones - SSC Editor wrote:

    Indeed. Part of what I wanted to convey is that you need to look at the problem and decide if it's a nail or screw.

    I think most people get using the right tool, but how do you know what the tool is? What is your guideline for choosing a hammer v screwdriver, or PoSh v T-SQL?

    I'm in an interesting situation of figuring out the right tool.  I just retired my last Windows XP machine and the last app on it was a package that uses an Access database.  So far I have been through three Access version reinstalls, each that has nicely upgraded the database for me, but the current application version still says the database is obsolete.

    Now here is an interesting aspect of this.  I can get my SQL Server to import the database easily.  I can open the tables and see all of the data.  The bad part is that all of the internal table names and data element names are in a foreign language, and there are lots of  many-to-many  tables, all linked with integer data only.

    Question is whether or not I can 'nail' this or am I 'screwed'.

    Rick
    Disaster Recovery = Backup ( Backup ( Your Backup ) )

  • My two favorite questions are becoming...

    1. Is there a reason why this MUST be done in PowerShell?

    2. Is there a reason why this MUST be done in Python?

    The answer both frequently boils down to "Well, it can't be done in T-SQL"...

    ... except that most of the time, it CAN and people just don't know how.

    And, it turns out they don't actually do a very good job in PowerShell or Python, either.

    A recent example I witnessed at one of the companies I do work for is that they decided to write some "awesome code" in Python.  It took them weeks to write it, it still has errors in it, and it's kinda of slow, etc, etc.

    What does the code do???

    Except for the errors they still have and the missing functionality it still has, it does the same thing as BULK INSERT except slower.

    People keep talking about not knowing a screw from a nail and using the tired old saw of "To a hammer, everything is a nail".  The real fact of the matter is not everyone can know everything or even the tools they're supposed to know.

    Whether the tool is T-SQL, PowerShell, Python, or whatever, make sure the decisions you're making aren't being made because your ignorant of the capabilities of the actual best tool for the job.

    Heh.... and as a bit of a sidebar for those that say passive/aggressive things like "Well, Jeff... SQL Server isn't the center of the universe, ya know"?  My answer would be "Ok... let's turn it off and see if you're right". 😉

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    My two favorite questions are becoming...

    1. Is there a reason why this MUST be done in PowerShell?

    2. Is there a reason why this MUST be done in Python?

    My questions are:

    1.      Is the job done?
    2.      Are the results what is needed?

    Rick
    Disaster Recovery = Backup ( Backup ( Your Backup ) )

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply