Last updated on September 28th, 2022 at 09:33 am
As a Microsoft Endpoint Manager Configuration Manager (MEMCM / MECM/ SCCM / ConfigMgr) Administrator we share many hats. The other day I was showing a ConfigMgr Administrator how to write a query and also how to create drill-throughs from within his reports. I began by recommending that reports should be created in SQL Server Management Studio (SSMS). Then the finished SQL Server query should be copied over to SQL Server Data Tools (SSDT). I was surprised when one of the first questions that I was asked was, “Where in SSMS is the Query Designer window”? so I asked him to show me as I wasn’t too sure what that meant,
I must admit that it’s been a long time since I’ve looked at the it interface within SSDT. So I wasn’t sure where it was in SSMS. I told him I would get back to him about it and he suggested that this would make a good blog post topic.
Getting started with Query Designer Video
Query Designer is a great way to visualize query designs as you build the query. It allows you to see what’s being created and it also lets you change options or settings. I’ll demonstrate Query Designer in this 2-min. video.
Step-By-Step Guide
You might not notice this at first, but as you change items within open window and the query is automatically updated. In the above screenshot I do not have a comment column, but I want to add one. Once I select the column it is added automatically to the query below (see the purple arrows).
I think this is a good way to learn how to write a query and how each option or setting affects a query, but be mindful that this will NOT teach you how to write queries. Query Designer cannot teach you style or format guidelines, nor will it employ best practices when it comes to using alias names.
I took the two previous screenshots from SSDT, but remember I was asked whether or not SSMS had something similar. The window above is called Graphical Query Designer, so I searched for that in the online docs. Here are two articles that I found online: Graphical Query Designer and Graphical Query Designer User Interface.
Now that I know that this feature is called Graphical Query Designer in SSDT, I can find it within SSMS.
Where to find it!
Where is it?
In SSMS, open a new query window. In the canvas area, right-click and then select Design Query in Editor… (alternatively you can type Ctrl-Shift-Q) and Query Designer opens.
On a side note, why is it called Design Query in Editor… on the menu. But the name changes to Query Designer within the window header and then it is called Graphical Query Designer within the online docs? I have no idea why, but I personally dislike it when something like this has multiple names. It makes it hard to follow and that is why I keep referencing it with different names.
At this point, I will show you some of the basic items found within it and how to use them.
The Add Table window will popup when you first access it. Select the View tab and then add the views that you want within your query.
You can do this by first selecting the view name (v_R_System_Valid) and then clicking on the Add button. Repeat this step for each view that you want within your query. When you are finished click on the Close button.
From this point on you would perform your normal tasks, such as adding columns to the query, setting-up your Joins, Aliases, Sort Orders and Filters. As you perform each step you can see that your query will change in the query section of the window.
Final Thoughts
In an upcoming blog post, I will give you five time-saving tips on how to use Query Designer. So look for it coming soon!
I hope that you have found this information useful. If you have any questions, please feel free to contact me @GarthMJ. Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!