Azure Data Studio has quite a few extensions that are very useful. Over the past six to eight months I have completed a number of posts on quite a few of these extensions. So far one of my favorite extension is the PoorSQL Formatter. It provides great assistance in the formatting of Transaction SQL code. What it doesn’t do, is help identify code that is not in line with best practices. This is where the TSQL Checker comes into play.
This extension was developed by Daniel Janick. Daniel is a Microsoft MVP from the Austin, TX area. He can be found at @SQLSME on Twitter.
You might be wondering exactly what does this extension check. According to the extension documentation this is what it checks:
“TSQL checks for hints, select * from options”
Of course “SELECT *” is a very well known problem in query design. Hints and options can also create significant performance issues as well. What exactly is a hint? According to Microsoft’s documentation a hint is something that will over ride the execution plan that the query optimizer might select for a query. Here is a link to Microsoft’s documentation on hints. Hints will act on the four DML statements, SELECT, INSERT, UPDATE and DELETE. Hints should only be used as a last resort and only by experienced developers or DBAs.
While this extension is still in preview mode, it can still be very useful. To install it you will click the “Install” button, green button below. This will appear when you are looking at the documentation of the extension. When you do, you will be asked to open the GitHub sight for this extension.
When you click open, you will be taken to the GitHub site for this extension. You will want to download the tsqlchecker-0.0.1.vsix file. It is highlighted below. When saving it, save it in a location where it will not be overwritten.
To install this extension, simply go to the File menu and then go to “Install extension from Vsix file”. While some extensions require a restart of Azure Data Studio, this extension does not.
The purpose of this extension is to help you identify code that is not inline with best practices. In order to do so, the extension needs to mark the offending code in some manner. The extension will do this one of three ways.
-
- Highlighting the code with a box:
- Highlighting the code in yellow:
- Highlighting the code in red:
- Highlighting the code with a box:
Let’s take a look at some of the code that the TSQL Checker will flag.
Note: This code was take from the Microsoft documentation. This really isn’t a post on what these things do, just how TSQL Checker flags them. Refer to the Microsoft documentation for more information on these.
SELECT *
MAXDOP
FORCESEEK
FORCESCAN
LOOP JOIN
MERGE JOIN
OPTIMIZE FOR
HASH JOIN
For all of these warnings, the developer has also added a popup that will provide additional information. When you float the cursor over the warning you will see somethings similar as you is in the image below.
I really like this extension. It reminds us of code that is less than ideal and should be reviewed. Remember, few if any of these hints are recommended for production use. If you run into a situation that you think these are part of the solution, just do your homework first. Research and see if there is a better way to address the problem.
Thanks for visiting my blog!!!