In this article, we’re going to be taking a look at Query Builder for SQL Server, from Devart. See what it has to offer for users like you and me and if it is a tool that you can integrate into your professional workflow.
Installation
You can get SQL Query Builder by clicking on one of the “Get Trial” buttons. However, I highly recommend clicking the one that says “dbForge SQL Tools Professional Trial” because that will also include the whole suite of SQL Server Management Studio extensions, which adds a whole lot of extra value.
Note: As you can see, the site states that the tool requires .NET Framework 4.5.2 or higher installed, so make sure that’s the case so that you can follow along. On top of that, by now you probably already figured out that this will be a Windows-only thing, but that shouldn’t be a showstopper to give the tool a chance.
Since it’s the very first time that we will be opening the tool, then let’s click on “New”:
On the very next screen, this is the view that we are presented:
In the left section, we can see an area that says “Database Explorer”, which is probably where the connection to our SQL Server instance has to be specified.
In the upper-center section, we simply see some sort of grid and a text that says, “You can drag objects from Database Explorer here.”.
The bottom section has several options (Selection, Joins, Where, Group By, Having, Order By).
So to give the tool an initial shot, let’s first create the connection to the SQL Server instance that we will be targeting (in my case, for demonstration purposes, I’ll go with the default instance I have setup):
Let’s click first on this icon with the green cross.
You’ll see a regular window where you have to specify basic connection string parameters. The cool part is that you can categorize your SQL Server instance, either using any of the 4 presets (Development, Production, Sandbox, and Test) or create your categories (as I already have).
If you click on the “Test Connection” button, a popup will trigger stating that you have successfully connected to your instance, and it will even tell you the SQL Server version detected (if the specified values are the correct ones, of course).
If the connection succeeds, then hit the “Connect” button, and you’ll be taken back to the view with the grid, but with the connection to the instance open and with the list of databases currently hosted in there.
Now, let’s do exactly what the text in the grid section says - drag and drop objects from the “Database Explorer” section.
So I have 2 tables in my database called db1, and one of them has a foreign key to the other one. Let me drag first the table called “table1”:
Pretty straightforward, I get a window with the name of the table, and the list of fields, and it shows me a small key icon next to the field that currently is the Primary Key.
But you might be wondering, “why I’m not able to see the data type of each field in that area”? Well, it turns out that you can see that when you click a field and take a look at the information that appears in the “Properties” area, right below the “Database Explorer section”.
Ok, now let me drag the other table to see what happens:
It automatically detects that there’s a relationship between them (the foreign key I mentioned before), nice!
But you know what, the tool is called “Query Builder”, not “Diagram Builder”,… so this might not be really what it is intended for, so let’s explore a bit more to see what we’re able to find:
So if you start ticking the checkbox for each column, then things will start happening in the bottom section of the screen. As you can see, it will list each one I’ve ticked, and I can give it an alias.
So just by doing that, I’m getting the vibe that a query is being auto-generated for me, based on my selection of columns and the information I provide, so let me see if I can find it:
I guess it was right there all along (thanks, Captain Obvious):
Ok, looking good so far. But what else can I do here? Let’s right-click on this window to find out:
All right, I’ll click the “Execute” option to see what it does:
A new tab called “Data” appears, and you can see the result of the query that you just saw previously. It will also specify the specific data type of each column.
With that alone, I would have had enough to call it a day because the tool does so much with so little action from the user’s end, but I’m curious enough to see if I can find even more cool things to share with you, so buckle up:
If I right-click on the result set, go to “Data Layout” and then click on “Card View”, this is what I see.
You know what, this is the very first time (no joke) I’ve seen something like this. Even if you might argue that it is not the best way to consume/visualize the data, you cannot argue with the fact that it is something that differentiates Devart from the rest, and I’m not talking just for this feature, but as a whole in general.
What if I need to export the result set to generate a report or whatever? We can right-click on the result set and click on “Export Data…”.
We get the following set of file formats to export our data:
This is what my PDF looks like after I complete the process:
As you can see, I can even go ahead and format the look of my table, sweet!
This was just the “tip of the iceberg”. You can do so much more with the tool, but I will leave it here for today, so you can think of this post as an appetizer ;).
Final thoughts
● My first impression when I saw the tool was, “ok, I guess this is just another database diagraming tool”, but boy, I was wrong, Query Builder is way more than that. In this article, I showed you the very basics, and as much as I would’ve liked to show you everything, it would’ve been way too much to digest for the reader.
● If you’d like to go deeper on the tool, I highly recommend going to their official documentation center.
● I see a massive particular use case for this tool. If you are a business that hires interns or junior database developers, then this is a perfect fit because they can practice and enhance their skills with Query Builder, something that is very valuable, especially in these modern times where the demand for data engineers has never been so high!
Top comments (0)