SQLTwins by Nakul Vachhrajani

Home | About Nakul Vachhrajani | Archives

SSMS – Query Designer – Ad-hoc query writing/editing made simple

July 14, 2011 9:00 AM


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!

The scenario

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:

  1. Launch SSMS
  2. Connect to the desired instance of SQL Server
  3. Begin with SELECT…

Now that we have written the first clause – SELECT, we have two options:

  1. Write the query by using Intelli-sense to reduce key-strokes
  2. 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:

  1. Login to your SQL Server instance using the SSMS
  2. Press Ctrl+Alt+Q or go to Query –> Design Query in Editor
  3. The Query Editor would launch. Notice that the SELECT…FROM is ready in the result pane (towards the bottom)
  4. image
  5. 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
  6. Once the tables are selected, click on “Add” and then “Close”
  7. 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
  8. 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)
  9. image
  10. 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
  11. 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
  12. 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
  13. image
  14. Once done, simply click OK
  15. 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, 
                      Sales.SalesOrderHeader.AccountNumber
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!

image

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.

Spread the word around! Share via:

Posted by nakulvachhrajani

Categories: #SQLServer, Blog, Imported from BeyondRelational

Tags:

One Response to “SSMS – Query Designer – Ad-hoc query writing/editing made simple”

  1. Thanks for sharing Nakul.

    Like

    By Hardik Doshi on March 8, 2012 at 6:59 AM

Leave a Reply

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.