In today’s game, IT professionals are expected to be highly productive and very efficient with the resources within their grasp. However, in many cases, we as database professionals are left with the bare minimum tools to fulfill our duties, which is not perfect, to say the least.
But, what does it really mean to be productive? The definition from the web states the following:
“Productivity is a measure of how efficiently a person completes a task. We can define it as the rate at which a company or country produces goods and services (output), usually judged based on the amounts of inputs (labor, capital, energy, or other resources) used to deliver those goods and services.”
With that in mind, let’s take a look at how the SQL IntelliSense tool can help us DB Developers to increase our productivity.
Installation
You can go to Devart’s website to choose one of the download options for dbForge SQL Autocomplete. Here’s a screenshot of what you’ll see:
I recommend the first option as it’s directly the SQL Server Management Studio (SSMS) extension to get you going. The other two options are big gun installers that contain the rest of the suite if you’d like to check them out as well. But for the purposes of this article, the first option should be perfectly fine.
Use Cases
Disclaimer: I’ll be using Microsoft’s AdventureWorks sample database to demonstrate the capabilities of the tool.
Objects suggestions on the fly while typing
Immediately after you start typing your query, the tool will present a context menu so that you can easily spot the object you are looking for, and see the rest while you are at it.
But not only that. If you start clicking objects, you will see the context menu expanded to show additional relevant information for you.
This screenshot contains the information about the selected object (in this case, the Product table of the Production schema). You can see the list of columns, their data types, which allow NULL and which don’t, which are foreign keys referencing another table, and even an estimated row count.
This is very useful because it can save you several roundtrips to the Object Explorer in SSMS, expanding the tables and figuring out what you really need from all the participating tables of the query you are crafting.
A quick tip: if you hit the tab after selecting the object you intend to use, this SQL IntelliSense tool will complete the statement and assign an alias for you to carry on with your query:
Moving forward with the elaboration of the query, if I start typing the word JOIN and hit space, another context menu will appear. However, this time it will be a jaw-dropping one that lists all the tables that you can join the table with, even with the specific fields!
Again, if you hit Tab right after picking the one you want, it will be completed for you.
Information within the result grid
Giving continuity to the example I’ve been presenting, let me go ahead and execute the query from the previous screenshot:
Several things to remark here:
● At the bottom of the result grid, you’ll notice a gray bar with several results (MAX, MIN, AVG, SUM, etc.).
● When you either select a few rows of the same column or click the entire column, the true value of the feature will shine. Depending on the data type of the column and the number of rows selected, the values in the bar will change as well.
● In my particular case, I’ve selected the entire contents of the Name column, which tells me that the longest string is 32 characters long, the shortest one has 4 characters, the amount of rows selected is 1069, and that out of those 1069 records 432 are distinct. The AVG and SUM results will start reacting as soon as you pick a column with numeric data type values.
● To be honest, there were way too many times I needed to copy/paste the entire result set to an Excel sheet to obtain the same information SQL Complete puts right there within your sight. Awesome!
Long strings value visualization at a glance
How many times have you had the need to quickly visualize the contents of a column that holds long string values? When that happens you’re pretty much forced to copy/paste the values someplace else to look at them because stretching the column all the way to the right just doesn’t cut it. However, this SQL autocomplete tool includes the Show Data Viewer option in the result grid, so let’s see it in action.
Using the same result set from my previous screenshot, let me find a string that isn’t visualized entirely to quickly demonstrate the feature. All you have to do is right-click on the result grid and pick Show Data Viewer or press Crtl+Alt+D on your keyboard.
In the new window, you can visualize your entire content, no matter how long it is. Also, at the very bottom of the window, you’ll see how many chars conform to the string.
Now, there might be cases where you store image data in your database, which are usually saved as string representations. For this particular scenario data viewer really shines.
I’m going to perform a simple SELECT * from the Production.ProductPhoto table, which obviously should contain image data. After that, with the Data Viewer open, I’m going to select 1 random value from one of the photos columns. This is what I see:
This feature alone will save you quite a lot of time versus any traditional method you were previously using to quickly visualize the imagery data inside your database.
Final thoughts
I’ve showcased a very tiny set of awesome features that the Devart SQL autocomplete tool puts at our service. Every single feature available is definitely targeted toward helping us SQL Server DBAs to be highly productive with the daily database development tasks, so kudos to the Devart team!
I’m planning to create a series of articles covering as much as possible of the entire set of features just because it is totally worth it on its own, and because I’m sure that any SQL Server database developer out there will benefit from it.
Top comments (0)