formatting T-SQL

  • drew.allen

    SSC Guru

    Points: 76520

    Eric M Russell - Wednesday, August 8, 2018 7:12 AM

    Code reviews are great, but depending on the volume and velocity of development, it's usually not possible.

    It is always possible.  What you mean is that it's not expedient.  Saying it's not possible implies that your company doesn't have a choice, but it does.  It always does.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Eric M Russell

    SSC Guru

    Points: 125009

    drew.allen - Wednesday, August 8, 2018 7:52 AM

    Eric M Russell - Wednesday, August 8, 2018 7:12 AM

    Code reviews are great, but depending on the volume and velocity of development, it's usually not possible.

    It is always possible.  What you mean is that it's not expedient.  Saying it's not possible implies that your company doesn't have a choice, but it does.  It always does.

    Drew

    I'm saying it's not always possible for the DBA code review each T-SQL object deployed to each production server. Where I work, there are maybe 100 people across the enterprise writing SQL code. Of course each development team should have their own internal review of code they are writing. The problem however is that, because SQL is an integration language (much like JavaScript or PowerShell) most SQL coders don't do so at high proficiency level.

    But commenting and even rules based execution plan optimization checks can be integrated into the Continuous Integration process. In some organizations, if it doesn't happen there, it won't happen.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • drew.allen

    SSC Guru

    Points: 76520

    Eric M Russell - Wednesday, August 8, 2018 8:07 AM

    I'm talking about code reviews by the DBA.

    It doesn't matter.  It's still a choice that someone has made.  When the code blows up in prod, do you want someone to be able to point to you and say that you deployed it without reviewing it, or would you rather have an email chain where you point out that the code needs to be reviewed and someone else told you to skip the review?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Eric M Russell

    SSC Guru

    Points: 125009

    drew.allen - Wednesday, August 8, 2018 8:25 AM

    ...
    It doesn't matter.  It's still a choice that someone has made.  When the code blows up in prod, do you want someone to be able to point to you and say that you deployed it without reviewing it, or would you rather have an email chain where you point out that the code needs to be reviewed and someone else told you to skip the review?

    Drew

    In my organization, generally speaking, the DBA doesn't deploy T-SQL objects, unless it's something like a DML operations that would write millions of rows. Scripts get promoted through the change control process based on sign off by QA and managers (so there is a chain of ownership) and then deployed via a CI tool called Octopus or DbUp. However, there is no point in the process that requires DBA intervention, review, or approval. That's why my thinking is in terms of automated T-SQL verification checks integrated into the deployment process.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Lynn Pettis

    SSC Guru

    Points: 442138

    Eric M Russell - Wednesday, August 8, 2018 8:07 AM

    I'm talking about code reviews by the DBA.

    Only time I get to review any SQL code is after it has been put into production and the customer complains.  So much of that could be avoided if the developers at least sent me their code and asked me to review it.  I am not part of the engineering team so there is no requirement for that even though I am the only SQL Server "expert" (don't like calling myself that) in the company.  I guess they like to keep me a secret.

  • Luis Cazares

    SSC Guru

    Points: 183540

    You should add a step for a review before pushing changes to the branch that will be deployed or as an approval just like the one you have for QA and managers. The fact that there's currently no DBA intervention was a choice by management, not something that is impossible to do.

    Branches being deployed, ha ha ha, if only we were developing and deploying from branches, with known test sets of data that we were confident in and actually used in testing.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ed.elliott

    SSC Rookie

    Points: 39

    Break down those barriers!

    I've been on the opposite side where we have had DBA's who always looked busy and were never interested in what we did unless there was a problem, it would have been great of the DBA's worked with us - you can be that DBA!

     

    ed

  • Jeff Moden

    SSC Guru

    Points: 994517

    ed.elliott wrote:

    Break down those barriers! I've been on the opposite side where we have had DBA's who always looked busy and were never interested in what we did unless there was a problem, it would have been great of the DBA's worked with us - you can be that DBA!   ed

    I AM that DBA... I sit with the Developers, turn peer reviews of code into personalized mentoring (sometimes, we'll get the group involved), and I drop just about everything if a Developer needs help on something or has a question.

    Part of the reason why I'm like that is because I used to be a Developer and still partially (about 25%) am when it comes to T-SQL.  I also set them up on the Dev box with sys-admin privs with the understanding that they shouldn't do anything like backups, restores, setting up privs, etc (the stuff that a System DBA would normally do) without checking with me first.  And, that box gets backed up the same way as prod, which has saved our donkey more than once.

    IIRC, it was David Poole that said "If you're the first person people come to with database problems rather than the last, you might be an exceptional DBA".

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • ed.elliott

    SSC Rookie

    Points: 39

    nice!

    I hadn't heard't heard "If you're the first person people come to with database problems rather than the last, you might be an exceptional DBA" before but I really like it 🙂

     

    ed

  • David Burrows

    SSC Guru

    Points: 64493

    Jeff Moden wrote:

    IIRC, it was David Poole that said "If you're the first person people come to with database problems rather than the last, you might be an exceptional DBA".

    They do only because I am the only person that remotely resembles a DBA, but I am far from exceptional. :-/

    Far away is close at hand in the images of elsewhere.
    Anon.

  • This was removed by the editor as SPAM

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1134

    I have used Poor Man's T-SQL Formatter, in different editors, works nicely. Now I mostly use SQL Prompt, from Toolbelt in SSMS. The SQL Prompt Core, is free and may suite your needs.

  • Jeff Moden

    SSC Guru

    Points: 994517

    jonas.gunnarsson 52434 wrote:

    I have used Poor Man's T-SQL Formatter, in different editors, works nicely. Now I mostly use SQL Prompt, from Toolbelt in SSMS. The SQL Prompt Core, is free and may suite your needs.

    I've tried the PoorSQL.com in the past.  It works well for smaller stuff but for a lot of the more complicated stuff that you'll find on forum posts and in real life, it never completes.

    Of course, I haven't found one yet that follows or can be made to follow my "River Format" (including SQLPrompt) where the main key words are right aligned in a "column" on the left of the window and the rest is left aligned in a "column" that follows.  For example...

    --===== Create a table of random Month Names and Years.
    -- This is NOT a part of the solution. We're just building test data here.
    -- Creates 1 Million random pairs for 30 years (2000 through 2029).
    DROP TABLE #TestTable
    SELECT TOP 1000000
    [MonthName] = CONVERT(VARCHAR(9),DATENAME(mm,DATEADD(dd,ABS(CHECKSUM(NEWID())%365),0)))
    ,[Year] = CONVERT(CHAR(4),ABS(CHECKSUM(NEWID())%30)+2000)
    INTO #TestTable
    FROM sys.all_columns pc1
    CROSS JOIN sys.all_columns pc2
    ;
    SELECT *
    FROM #TestTable
    ; ^
    |
    |<---- The "River" of spaces is here.
    |

    Still and considering some of the code I've seen and had to work with, anything that makes the code more readable and consistent for capitalization of SQL keywords and some form of indentation is always a huge help.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Jeff Moden

    SSC Guru

    Points: 994517

    David Burrows wrote:

    Jeff Moden wrote:

    IIRC, it was David Poole that said "If you're the first person people come to with database problems rather than the last, you might be an exceptional DBA".

    They do only because I am the only person that remotely resembles a DBA, but I am far from exceptional. :-/

    You might be mistaking what "Exceptional" means in this case.  It doesn't mean "All Knowing" about everything having to do with SQL Server.  It means that you're always willing to help others in what they do and will go the extra mile to do so.

    It's like the MS MVP award.  A lot of people mistake it as someone that is an expert in SQL Server and that's NOT what it is.  It's a "public service" award based on volume of posts/blogs/articles and participation in events like SQLSaturday, etc.

    Don't get me wrong... There are a whole lot of MVPs that actually ARE experts in what they do/post about but there are also a lot of MVPs that don't actually get it right when it comes to WHAT they post... but they're still MVPs for trying to help, sometimes based on shear volume of posts.

    You say the folks in your shop come to you because you're the closest thing to being a DBA there is.  They could just as easily go to "Yabingooglehoo" for answers but, instead, they seek you out.  While it's not my job to judge, I do "follow" you on these forums for a reason... you're posts over the last more than a decade have always been helpful and you've always been thoughtful in your responses.

    You, sir, actually are an "Exceptional DBA" (and exceptional person) and I appreciate every one of your posts.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    Jeff Moden wrote:

    jonas.gunnarsson 52434 wrote:

    I have used Poor Man's T-SQL Formatter, in different editors, works nicely. Now I mostly use SQL Prompt, from Toolbelt in SSMS. The SQL Prompt Core, is free and may suite your needs.

    I've tried the PoorSQL.com in the past.  It works well for smaller stuff but for a lot of the more complicated stuff that you'll find on forum posts and in real life, it never completes. Of course, I haven't found one yet that follows or can be made to follow my "River Format" (including SQLPrompt) where the main key words are right aligned in a "column" on the left of the window and the rest is left aligned in a "column" that follows.  For example...

    --===== Create a table of random Month Names and Years.
    -- This is NOT a part of the solution. We're just building test data here.
    -- Creates 1 Million random pairs for 30 years (2000 through 2029).
    DROP TABLE #TestTable
    SELECT TOP 1000000
    [MonthName] = CONVERT(VARCHAR(9),DATENAME(mm,DATEADD(dd,ABS(CHECKSUM(NEWID())%365),0)))
    ,[Year] = CONVERT(CHAR(4),ABS(CHECKSUM(NEWID())%30)+2000)
    INTO #TestTable
    FROM sys.all_columns pc1
    CROSS JOIN sys.all_columns pc2
    ;
    SELECT *
    FROM #TestTable
    ; ^
    |
    |<---- The "River" of spaces is here.
    |

    Still and considering some of the code I've seen and had to work with, anything that makes the code more readable and consistent for capitalization of SQL keywords and some form of indentation is always a huge help.

    I generally use the SQLinForm plugin for Notepad++, works fine for large code sets and most SQL flavors.

    😎

    JM: "You, sir, actually are an "Exceptional DBA" (and exceptional person) and I appreciate every one of your posts."

    Fully agree!

     

Viewing 15 posts - 31 through 45 (of 48 total)

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