T-SQL Tuesday 143 – Two personal go-to scripts

Reading Time: 4 minutes

For my T-SQL Tuesday contribution this month I want to share two personal go-to scripts that mean a lot to me.

This months T-SQL Tuesday is hosted by John McCormack. John has invited us to write about our go-to handy short scripts.

To clarify, John does not mean the GOTO syntax available in SQL Server. Which I think should only be used in other programming languages or computers from the eighties. On that note, RIP Sir Clive Sinclair.

Anyway, you can find out more about the invite to share personal go-to scripts by clicking this link about the T-SQL Tuesday 143 invitation or on the image below.

First personal go-to script

My first personal go-to script is one that has helped me out a lot over the years. Because I have used it a lot to identify missing indexes. I know there are a few different versions available online that you can use. However, I tend to use the one that comes with Glenn Berry’s Diagnostic Queries.

It is so easy to use. I’m not sharing the snippet of code on here because I want to encourage people to download the entire diagnostic script instead. Just download the script that is relevant for your version of SQL Server and search for ‘Missing indexes’.

Of course, you need to use some personal judgement when reviewing the results. Especially if you get a lot of recommended indexes for the same table. It’s worth looking at the other queries in the script as well.

I’ve been a big fan of this snippet of code since I first saw an earlier version of it in the blue ‘SQL Server MVP Deep Dives‘ book. Which was penned by 53 MVPs. It has been a personal go-to script for me for some time now and has helped me out a lot of times over the years. I recommend it to anybody who is investigating performance issues with SQL Server.

In fact, I flipped through this book whilst writing this post since I own a copy. Parts of it might be an interesting read for some of you. So, if you are one of those people who likes to soak up all SQL knowledge you might want to buy this book. Especially since the royalties from it go to charity.

For those interested, there is also a ‘SQL Server MVP Deep Dives, Volume 2‘ book.

Second personal go-to script

My second personal go-to script is one that I use a lot these days. It is from a SQL file that I have within a Database project, which you can see below.

CREATE TABLE [dbo].[SQLDeploy]
(
  [Id] INT NULL,
  [columnb] char(2)
)

This personal go-to script probably does not look like much until I show it in Azure Data Studio to give it some context.

This view probably looks familiar to anybody who has attended an Azure DevOps or GitHub session that I co-present with Sander Stad. Because I use this script right at the start of a couple of demos that I do.

I start by changing a column in this script. I then save the file and synchronize the local Git repository that the file is in with a repository in either Azure Repos or GitHub. Which starts a deployment pipeline.

For example, the update starts the below pipeline that I covered in another post. That post covers how to deploy to multiple SQL Server database types from one Database Project using Azure DevOps.

One of the main reasons why this is a personal go-to script for me is because it is a very effective way to start a demo. Showing this piece of SQL also gives me a chance to highlight the fact that all the SQL scripts in a Database project contain a CREATE statement.

You can test this for yourself by downloading GitHub repository that I have created which contains a Database Project you can use for Azure DevOps deployments. It is called AzureDevOps-AzureSQLDatabase.

You can also download a version I created with a GitHub Actions workflow template instead. Which is called GitHubActions-AzureSQLDatabase.

Final words

I hope me sharing two of my personal go-to scripts has made interesting reading for this months T-SQL Tuesday contribution.

I wanted this post to contain a combination of scripts for SQL Server performance and deployments. Because both are very relevant in 2021. In fact, I covered deployments being relevant recently in a post about the increase in demand for Data Platform automation.

Of course, if you have any comments or queries about this post feel free to reach out to me.