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


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.

Advertisements

One thought on “SSMS – Query Designer – Ad-hoc query writing/editing made simple

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s