Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #156–Ready for Production

    tsqltuesdayIt’s a busy time for me, but it’s also T-SQL Tuesday blog party day. I’m rushing a bit as I forgot about this (thanks for the reminder, Deb) and had to help on the ranch this morning.

    In any case, a moment of Zen.

    20221108_082454

    Now, for the T-SQL Tuesday post to answer Tom’s invitation.

    The Quality Bar

    Lots of code gets through to production. I suspect many of us agree that not all of this is production quality. Often we find issues with code that doesn’t perform well or even meet the specs of what we required.

    I haven’t had to put much code in production in the last decade, but I do remember doing so, and I remember supporting code. To me, the code quality that defines production is this:

    Does it make my phone ring?

    If the answer is no, it’s production quality. If it does, then it’s not. That’s how I’ve worked on things in the past, and it’s served me well.

    You might argue, Steve, that doesn’t help. How do I know what code will meet that metric? There’s no easy answer there. You need to know the system, the requirements, the clients, and the workload. There is a lot that goes into deciding how to build code and what commands, structures, architecture, etc. is suitable.

    A few examples. In SQL Server, we avoid cursors because they are not efficient. However, if I have very rare processes, or sometimes on-offs, a cursor might work fine. If it doesn’t overload the server, runs fast enough, and gets the job done, why not?

    Another example is using CTEs to pre-aggregate some totals so that I can write a simpler query for a report with related data that isn’t aggregated. This might not be very efficient, and might create a lot of logical reads. However, if the report isn’t a problem with the server workload, is it production quality? I think it is.

    The caveat to this is you also need to know data growth. What is production quality today might not be in a year. Potentially we need to refactor code later. That’s fine for me if the situation changes, but it’s not fine if I can forecast this being a problem and I have a better technique, perhaps with some WINDOW functions and less CTEs. I don’t want to defer work unless I don’t have a choice. If I can write better code today, I should.

    This also means I ought to be learning more about how to produce better code from others on a regular basis.

  • T-SQL Tuesday #155 Round Up

    It’s time to look back at the 155th blog party. I was the host this month, asking about Dynamic SQL. I got quite a few responses, which I’ve gone through and summarized below. If I’ve missed someone, please ping me.

    The Round Up

    There are some great posts, so if you are interested in any of these areas, click through and read the original post.

    Rob Farley is first, as usual. He’s in Australia, so often he gets to publish close to the start of the day in the world. This month Rob writes about the dangers of dynamic SQL and how you can actually not take values of object names from the user. Instead, use their input to search sys.objects and get the value there.

    Vitaly Bruk writes about how single use plans with EF cause issues, and how to solve them.

    Richard Swinbank talks about how to use dynamic SQL to generate SQL code from metadata. Something I’ve used quite a bit in the past.

    Erik Darling takes time to discuss the datatypes used with dynamic SQL and how you might deal with the requirement for NVARCHAR when building the string.

    Ajay Dwivendi has a method for gathering SQL Server health metrics using dynamic SQL.

    Aaron Bertrand gets a post in, despite being on vacation. He writes about ow you might use dynamic SQL to execute code across all databases.

    Brent Ozar adds comments to Dynamic SQL. He suggest you should as well.

    Oliver Van Steenlandt writes about a couple of cases. Managing different levels of aggregates and for building ETL scripts.

    Raul Gonzalez reminds us of the security issues with dynamic SQL and SQL Injection.

    Reitse Eskins has a lot of schemas and uses dynamic SQL to build GRANT scripts.

    Shane O’Neill shows a few Dynamic SQL tricks to make it easier to debug.

    Gerard Jaryczewski is new to the T-SQL Tuesday party, but skips in with a solution to a nightmare for a SQL developer.

    Josh Smith gives us a three act play that is amusing to read.

    Nigel Foulkes-Nock takes a moment to examine the foreach db procedure.

    Kevin Martin writes about a search procedure you don’t have to write, because it’s generated.

    Ken Fisher notes that if we generate code, we need to generate comments as well.

  • T-SQL Tuesday #155–Using Dynamic SQL for SQLCMD

    It’s that time of month, and I’m the host this month. I wrote the invitation last week and now its’ time to answer. I’m actually using an example from the past that I was reminded of. That was the basis for the invitation as well.

    SQL Slammer

    I don’t know how many of you remember SQL Slammer, but it hit my company, JD Edwards, over a weekend. I was away on holiday, coming home Sunday night and got called into the office that night.

    One of the challenges with JD Edwards was that we used MSDE extensively. It was a part of some of our products, and developers had it on various machines, lots of multi-instances, and in all sorts of development servers. The worm crippled our network.

    We also had non-standard installs, so when we got a patch from Microsoft, it didn’t work because we weren’t in the c:\Program Files\… that they expected.

    I had to some some fancy dynamic stuff to get the patch to work. First, we used some queries in SMS (Systems Management Server) to find all the places where we had MSDE and SQL Server services. This wasn’t too hard, and I had a list of hosts and instances from here. Now the hard part.

    I needed to query all these instances and find out where things were installed, as well as get some patch information back. The SQL itself wasn’t too hard, but connecting to and querying all these machines wasn’t simple. This was the pre-PowerShell  era. and VBScript wasn’t as easy, or bulletproof, to write.

    Excel to the Rescue

    I’d used Excel to help with this type of thing in the past. I would  put in some data in a column in this case, the hosts and instances. Then I’d add the same value in other columns, like SQLCMD. Then I would concat these together to make a string I could run. I also included T-SQL code in here, as I’d be querying various tables inside the engine.

    I also had an output part of the command, so that when I copied the contents of my final column, I had hundreds of SQLCMD command calls that would query all our instances and return data in a way that we could use to run the patch.

    Double dynamic code!

    I put these in a batch file, ran them, and then we had results that could be used in a similar process with the MS patch to patch all machines.

    A long 2-3 day of getting systems patched and slowly turning our network back on.

  • T-SQL Tuesday #155 –The Dynamic Code Invitation

    tsqltuesdayApologies for the late invitation. A minor snafu has me hosting again.

    This is the monthly blog party where someone hosts and you all write a response. I’d like to think this is one where lots of you have a story or a situation that worked out. Write a post on 11 Oct and post a comment here.

    The Invitation

    I saw a post recently where someone noted they used Excel to help build dynamic SQL for their job. I thought that was a) creative, and b) similar to something I’ve done. In fact, that will be my post for this month.

    However, while many of the experts decry dynamic SQL as a poor way of solving problems, it is not going away. In fact, it works really well for many situations and problems, albeit not necessarily a high volumes of data. There also are security concerns.

    My invitation this month is to write about producing SQL dynamically in some way. Let us know about any of these things:

    • a problem you solved
    • a creative use of technology to build SQL
    • security concerns
    • a place where dynamic SQL failed you
    • a way to convert dynamic SQL to something cleaner
    • anything else that relates to code producing code

    The Rules

    Only a few rules.

    • publish on 11 Oct 2022 sometime.
    • use the logo above and link back to this post
    • leave a comment or trackback/pingback here
    • Use the #tsql2sday hashtag to tag your post on Twitter, LinkedIn, etc.
    • Encourage others to blog

    That’s it. Have fun and I look forward to reading your responses.

    If you want to host, ping me on twitter (@way0utwest) or LinkedIn or email or anything really.