The SQL Server Management Studio is truly an Integrated Development Environment. There are innumerable utilities and tools available within SSMS which are yet to gain a footing. Today, I will present how the Query Designer can be useful to develop ad-hoc queries – without writing a single line of T-SQL code!
Today’s post is thanks to Chintak Chhapia (blog), who is a dear friend & colleague from the day we joined office - 7 years ago!
Let’s assume we need to query the Sales information in the AdventureWorks2008R2 database and attempt to list which Employee contributed to the sale. The traditional approach is:
- Launch SSMS
- Connect to the desired instance of SQL Server
- Begin with SELECT…
Now that we have written the first clause – SELECT, we have two options:
- Write the query by using Intelli-sense to reduce key-strokes
- Go with the dreaded SELECT * FROM….
Both of the above involve some writing, and when using Intelli-sense, we need to know about the table aliases, columns need to be searched for within the pop-up list. In short, writing a query would involve some typing and for most people, this would make query writing slower.
Not to mention that they query may or may not be formatted – the formatting would depend upon the beliefs of the query writer.
The Query Designer
SQL Server provides us a Query Designer using which we can write a query without a single keystroke. Here’s how:
- Login to your SQL Server instance using the SSMS
- Press Ctrl+Alt+Q or go to Query –> Design Query in Editor
- The Query Editor would launch. Notice that the SELECT…FROM is ready in the result pane (towards the bottom)
- Scroll down and select the tables you need (you can multi-select using the Ctrl key). Let’s select the tables – HumanResources.Employee, Sales.SalesPerson, Sales.SalesOrderHeader
- Once the tables are selected, click on “Add” and then “Close”
- Notice that SQL Server automatically identified the links between the tables, and developed the “FROM” clause for us – ready with all the joins in place
- You can hover over the table links in the diagram pane to see the nature of the join (shown in the diagram is an INNER JOIN between HumanResources.Employee and Sales.SalesPerson)
- Now, simply check the required columns from each table in the diagram pane and notice how SQL Server builds the “SELECT” clause of the query for you
- TIP #1 – Columns are added to the “SELECT” clause as you click the required columns. You can therefore control the column ordering by controlling the order in which you check-off the columns
- TIP #2 – You can then configure the sorting options, sort order, filter criteria and a lot of other things. Notice that the Query Designer will add the respective clauses as applicable
- Once done, simply click OK
- You will be returned to the SSMS with the newly constructed, formatted query available for execution
SELECT Sales.SalesOrderHeader.SalesOrderID, Sales.SalesPerson.TerritoryID, HumanResources.Employee.BusinessEntityID, Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.Status, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.OnlineOrderFlag,
Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.SalesOrderNumber, Sales.SalesOrderHeader.CustomerID,
FROM HumanResources.Employee INNER JOIN
Sales.SalesPerson ON HumanResources.Employee.BusinessEntityID = Sales.SalesPerson.BusinessEntityID INNER JOIN
Sales.SalesOrderHeader ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
WHERE (Sales.SalesOrderHeader.OnlineOrderFlag = 0)
ORDER BY Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesPerson.TerritoryID DESC
That’s it! You are all set – just click the Execute button, and the query will be executed!
Query Designer is for pre-existing queries also
The Query Designer is not just for new queries. If you have an existing query in SSMS, just select the entire query, and use the Ctrl+Alt+Q shortcut key or use the Query –> Design Query in Editor menu option. You will see that your query is now available in the Query Designer, ready for some key-stroke-less, graphical editing.
Until we meet next time,
Be courteous. Drive responsibly.