Jared Picou

Life. Tech. And the pursuit of happiness.

Tag: SSMS

  • How to use snippets to save hours of typing in Azure Data Studio

    Working with ad-hoc queries on a daily basis can lead to a lot of wasted keystrokes if you are not careful. And I mean a LOT of wasted keystrokes. Snippets assist with this by allowing you to make customized, reusable queries (or pieces of queries) that you can recall quickly and efficiently.

    Initial Setup

    Microsoft will do a much better job explaining the setup of these snippets. Here’s a footnote 1 that showcases the setup.

    After this initial setup, a new JSON file is made that will hold your snippets. To access this file for configuration through Azure Data Studio, in the Settings cog at the bottom left corner, there’s an option for User Snippets. Click this and choose the Existing Snippet of sql.json. The Microsoft article also provides an example layout of a snippet to use as a guide.

    This idea can also be applied to SSMS and I’m sure many other query editors, but Azure Data Studio is what I’ve been using lately so I’m writing this based on it. The setup and editing process may be different for each application. Check each application’s documentation to confirm how the setup should take place.

    Using the JSON File

    You can think of each snippet as an entry in this JSON file. Each snippet is organized as this:

    "Snippet Name":{
      "prefix": "",
      "body": [""],
      "description": ""
    }

    The Snippet Name is the simple name of your snippet. It’s good to give each snippet a descriptive name that helps you recognize what it does and contains some regularly used key words.

    The snippet’s prefix is the shortcut you can type in the SQL query editor to utilize the snippet. This can be different than the snippet name if you want to use abbreviations or some other method to call these snippets.

    The snippet’s body is where the actual script is put. The body contains multiple lines, each wrapped in double quotes, inside of brackets. When calling the snippet, it will appear exactly as you have it organized in this section.

    Related to the body section, there are also placeholders that can be added to the script. This is great to use where you know you need to add a variable for the script to function properly. I’ll put an example of this later.

    The snippet’s description is however much explanation you want to add to the snippet. When choosing a snippet, the description will appear next to it, further describing what it does.

    Example Snippet

    Let’s say I have a query that I use to check a user’s account before making changes to it. I don’t need to see all columns, only the ones I’m interested in. So, my query may look like:

    SELECT
      uProd.username, 
      uProd.email, 
      uProd.lastLoginDate, 
      uStat.statusDesc, 
    FROM
      current_Users_Production uProd
      LEFT OUTER JOIN users_Status uStat 
        on uStat.statusID = uProd.statusID
    WHERE
      email = '[email protected]' 

    While it’s not complex, it will still take me a minute or two to write this out each day and format it to my liking to run it. Of course I could save it in a .sql file and load the file each day to run it. Personally, I don’t care for managing a lot of individual .sql files for simple queries that do not require a lot of logic. This particular query would benefit from being a snippet instead.

    The snippet would look something like this:

    "CheckUserInProduction":{
      "prefix": "prod_chkusr",
      "body": [
        "SELECT"
        "  uProd.username,"
        "  uProd.email," 
        "  uProd.lastLoginDate,"
        "  uStat.statusDesc," 
        "FROM"
        "  current_Users_Production uProd"
        "  LEFT OUTER JOIN users_Status uStat"
        "    on uStat.statusID = uProd.statusID"
        "WHERE"
        "  email = '[email protected]'"
      ],
      "description": "Review user information before updating."
    }

    Once I save my sql.json file, I can then open a new query window, type ‘prod_chkusr’, press Enter, and this script appears just as is, saving me all of that typing!

    If it takes me 2 minutes to write this query every day for 5 days each week, then I just saved 40 minutes a month. This may sound insignificant, but we should consider how many daily or common scripts we write every day that can become a snippet. 40 minutes could easily turn into 4 hours a month, which can be put towards more important tasks or coffee breaks.

    Using Placeholders

    Referencing the above example, there’s a piece of information that I need to fill in for the query to function, the Email parameter in the WHERE clause. After inserting my snippet, I will need to click here, highlight it, and retype the email address that I’m actually interested in. That’s where placeholders shine.

    Instead of putting ‘[email protected]’ in my snippet, I can replace it with a placeholder. Placeholders are formatted as:

    ${number:label}

    The number is the tab stop number. A placeholder with a number 1 will be the first location of the cursor when the snippet is inserted, then 2, then 3, etc. A placeholder with a number of 0 will be the last stop after all of the other stops are cycled through.

    The same number can be used in multiple places as well. Let’s say I had a UNION query that needed this email address to be placed in two WHERE clauses. I can put the same placeholder number in both locations so when I fill one in, the other is filled in automatically.

    The label is just a description of what the placeholder should be. In our case, since I only have 1 parameter and I need it to be an email address, I could replace my WHERE clause with the following:

        "WHERE"
        "  email = '${1:userEmailAddress}'"

    When I insert the snippet in my query editor, this will be highlighted for me immediately (due to the tab stop number 1) to type the email address I’m interested in. Using placeholders strategically can add good flexibility to snippets.

    Snippet vs Stored Procedure

    There are times where keeping a reusable query is better in a Store Procedure or Function. To help differentiate between the two, think of snippets as time saving tools made just for you and your own method of work. They will contain queries that you personally use often and organized in a way that makes the most sense to you.

    Stored Procedures and Functions are stored in your database and allow other users and programs to access them if needed. Snippets are stored locally on your workstation and are only available to you, unless you purposely share them elsewhere. This is also true with backups where stored procedures and functions are typically backed up with the database. The sql.json file is backed up based on your workstation’s backup procedures.

    Backing Up your sql.json File

    As I began to rely heavily on my snippets, I found that the sql.json file that holds them all is quite valuable to me. Yes, I could recreate my snippets over time for myself, but that would (of course) take more time.

    To keep things simple, I use a batch file to copy my sql.json file from my PC to my secondary hard drive as a backup location. Your operating system may achieve this differently, and I’m sure there are many ways to do this. If don’t have a backup system in place on your PC and you just want to backup this individual file, you could use Task Scheduler to run this batch file once a day. It just keeps a single copy of the file in a different location.

    $sqlJSONFile = "C:\Users\<yourname>\AppData\Roaming\azuredatastudio\User\snippets\sql.json"
    $sqlJSONBackup = "<your backup location>\sql.json"
    
    Remove-Item -Path $sqlJSONBackup
    Copy-Item -Path $sqlJSONFile -Destination $sqlJSONBackup

    In this example, you would replace <yourname> with your username and <your backup location> with where you want to save the file at.

    Putting it to Use

    Making the snippets in Azure Data Studio is not complicated but utilizing them correctly can be challenging at first. Each person will utilize them differently, so it’s up to you to find the best way snippets can fit in your day-to-day duties working with queries. Once your system is setup, you’ll be shocked at how much time and typing you’re saving by utilizing snippets.

    Try this exercise to get started with snippets and start reaping the benefits:

    • For week 1, keep a simple log of all of the ad-hoc queries you write throughout each day. Anything that’s not saved in a .sql file or in a stored procedure / function can be considered an ad-hoc query.
    • At the end of the week, review the log and find the 3 queries that you ran the most times. These are great first candidates to become snippets!
    • Make each of these queries a snippet in your sql.json file. Look at them objectively and come up with good names, prefixes, formatting, and placeholders for each.
    • For week 2, start using these 3 snippets in your daily work, but continue to keep a log of your other ad-hoc queries (not counting the ones already in your snippets file).
    • At the end of week 2, once again add the top 3 ad-hoc queries to your snippets file.
    • Continue to week 3 and 4 using the same procedure.

    By the end of week 4, you should have (hopefully) 15 snippets to utilize each day that no longer need to be typed over and over again. If you find that you’re still typing a lot of ad-hoc queries, repeat the exercise and continue to build your snippet library to your liking.

    Didn’t come up with 15? Don’t worry! That just means your ad-hoc queries that month were not very repeatable and maybe not needed as a snippet. Try the exercise again when you feel that you’re typing the same thing over and over again

    I hope this idea has saved a little time in your day, added some organization to your scripts, and provided some inspiration to find other efficient practices.

    Resources

    1. https://learn.microsoft.com/en-us/azure-data-studio/code-snippets#creating-sql-code-snippets ↩︎