In Part 2 of the coverage of SQL Complete for SQL Server, I continued with the review of what the tool has to offer for SQL Server database developers (you can check it out here). Therefore, it is time for me to continue with the review of this awesome tool, so let’s get right into it!
Statement Expansion
Have you ever started writing a SELECT * statement only to realize that you do need to list the columns in the table(s)? Of course, before even writing your TSQL statement, you can right-click on your table in Management Studio and pick “Select Top 1000 Rows”, but what if you already went straight into typing your query?
Let’s see what Statement Expansion can do for us:
Here’s the query I’d like to use as an example. I’m specifying a JOIN operation on purpose to demonstrate that Management Studio can’t help you out in cases like this, but SQL Complete definitely comes to the rescue!
In a typical JOIN statement, you sometimes want to know what columns are involved in the operation to choose the ones you really want to return.
All you have to do is put the cursor next to the “” and a tooltip will appear indicating that you can just press *Tab** to do the magic.
Awesome stuff right!
SQL Code Formatting
When you are building dynamic TSQL code (whether in a Stored Procedure that will be executed frequently or an ad-hoc dynamic query) often times you want to work with it to either give maintenance to it or to perform the respective troubleshooting when a related issue is reported. Sometimes the end result is a query that is hard to work with and you have to spend time formatting it in a way that is digestible enough for you to do your work, which is precisely where SQL Code Formatter shines.
To demonstrate the feature, I have written a one-line query to see what exactly SQL Complete does for me.
If I click on the “Format Document” option, this is what I end up with:
I know that there are 3rd party websites that perform the exact same task, but it is something that is baked right into the tool and I find it very convenient to have right at my fingertips. Additionally, what if the server where you are executing your query doesn’t have an internet connection (which is a very common thing in a production environment)? Point for SQL Complete!
On top of this, SQL Complete gives you the option to format queries inside.sql files that you might have within a specific directory. Let’s take a look at a quick example:
I have created a directory called Queries in my Desktop folder.
Both files have an unformatted TSQL statement.
To access the feature, you click on the SQL Formatter… option within the SQL Complete option in the menu bar.
_*I apologize for the poor DPI scaling presented in the following screenshots. It is very likely that it will display correctly when you give it a shot. _
It's a nice option to have around if you ever have the need for it.
SQL Refactoring
Variables Renaming
Sometimes, when you have to deal with stored procedures and/or functions that you have to refactor, a common thing that you do is rename the variables used, whether it is for best practices or to adhere to a specific naming convention within your organization. Attempting this directly through SQL Server Management Studio can be a bit inconvenient.
This is a sample function I have created for demonstration purposes. Let’s suppose that I want to change the name of the variable @age to @result.
In SQL Server Management Studio, I go to the Find and Replace option and specify that I will be replacing the word age with result (yes, I know that the inconvenience I’m about to report is easily solved by replacing the whole variable name @age instead of just the word age, but it is something that can happen to anyone).
After proceeding with the replace, I can see that not only were my variables successfully changed, but it also changed the name of the function and my comment.
To avoid this, let’s use the renaming feature provided by SQL Complete. To achieve this, simply highlight the word of the variable that you want to change (in my case, I highlight the word “age”), right-click on it and click the Rename… option.
After doing that, you can proceed with the word replacement in the word that you highlighted. Note that a dotted square will appear in all the words where the change is going to be applied and a tooltip will appear stating that you can hit Enter/Tab to complete the operation.
And this is the end result after proceeding with the modification. As you can see, only the matching variables were modified, and the name of my function and my comment were left intact.
Semicolon Insertion
I know, this sounds like an extremely trivial thing because most of us omit the use of semicolons as a statement terminator when we are writing our queries. However, based on Microsoft’s official documentation, semicolons will be required at some point in a future release of SQL Server. With that said, if such a day comes, you need to be prepared so that your applications don’t start shooting sparks all over the place.
To do this, simply right-click on your query page and click the “Insert Semicolons” option and voila!
Final Thoughts
● In this 3rd part, we continued to explore very useful features that can definitely improve the productivity of database developers over time. It is remarkable the amount of features that we have looked so far throughout the series, considering the fact that we have been talking about an extension for SQL Server Management Studio and not a stand-alone tool!
● Given the fact that SQL Complete is stuffed with a ton of useful features, it will require a time investment for any new SQL Server developer to grasp everything the tool has to offer, but the return from that time investment is totally worth it, especially since the Devart team is constantly addressing bug fixes to have the end product as polished as possible.
Top comments (0)