SQL Server 2016: Query Designer
Use the Query Designer to build complex queries across multiple tables without writing any code.
SQL Server Management Studio includes the Query Designer to assist in building queries. It is a visual tool that allows you to select the tables and columns you want in your query, as well as any filtering criteria.
No need to write any SQL code — the Query Designer will generate that for you.
Use the Query Designer to Build a Simple Query
We'll now use the Query Designer to build a simple query. If you've been following along in this tutorial, you'd now have a database with three tables — all of which contain data. And because we've established a relationship between these tables, we can now run queries across all three, returning related records.
-
Open the Query Designer
Open a new query window by clicking on New Query in the toolbar.
Then select Query > Design Query in Editor... from the top menu.
If you can't see the Query option in the top menu, click inside the query window first. This will change the top menu items to be query-related options.
-
Add the Tables
Here, you select which tables you'd like in your query.
Select all three and click Add.
Click Close to close the dialog box.
-
Design the Query
You will now see the selected tables, and their relationships, in the Query Designer. Feel free to click and drag them around to provide a better visualization of their relationship with each other.
You can also re-size each pane by clicking its edge and dragging it up or down.
How to Design a Query
In the top pane (the Diagram Pane), click each column that you want to include in the query (whether you want to display it or not). Each column you select in the top pane will automatically appear in the middle pane.
In the middle pane (the Grid Pane or Criteria Pane), use the Output checkbox to indicate which columns will be returned in the results. Use Sort Type to specify the order of the results by a given column. You can use Sort Order to specify which column will be sorted first, second, etc. Use Filter to add filtering criteria to filter the records returned.
The bottom pane (the SQL Pane) dynamically generates the SQL statement that your query produces. This is the statement that will be run when you close the Query Designer and execute the query.
Our Example
In our example, our query will return all albums (along with their genre, artist, and genre) that were released in the last ten years. The criteria to achieve this is >DATEADD(year, - 10, GETDATE()). The query will sort the results by the release date in descending order.
Here's a close-up of the Criteria Pane:
You can change the order of the columns by clicking and dragging them up or down.
If you're reading this long after this tutorial was written, you might need to adjust the criteria to go back 20 years or more before you get any results.
Alternatively, you could add something a bit more modern to the music collection :)
Make sure you keep this query open in the query window because next, we will save it as a view.