I have maintained that SSMS has a lot of underappreciated features, and that most of the community is not utilizing SSMS to it’s maximum potential. Today, I will show you how a simple configuration change helped in speeding up one of the most common tasks – generating a csv from data stored in a table.

I had a requirement to extract some data out of a Microsoft SQL Server database, save it as a CSV (comma-separated value file) and then send it across to another team for further analysis. The logical order in which I addressed the task was:

1. Write the query
2. Export to a CSV
3. Send the CSV to the team in need

Write the query

Writing the query was simple, and for this example, let’s assume I came up with the following query:

USE AdventureWorks2008R2
GO
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

Export to a CSV

When it comes to exporting to a CSV file, I have multiple options

1. Use BCP (https://nakulvachhrajani.com/2011/05/02/bcp-amp-bulk-inserts-underappreciated-features-of-microsoft-sql-server/)
2. Edit the query to include comma-separators and then copy-paste to notepad
3. Use SSMS to generate the comma-separated output and directly copy it over to notepad

I will not be considering the point about using BCP because it has already been addressed by my previous post, and second because the server I was originally running on was production instance and bcp was not an option available to us.

Option #2 – Edit the query to include comma-separators and then copy-paste to notepad

We can modify the query to something similar (please note, there can be multiple, much better implementations. The script below is for demonstration purposes only).

USE AdventureWorks2008R2
GO
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

The problem that we run into is alignment. If the results are copy-pasted into Notepad, they would not turn out as expected, and some manual formatting will be required.

Options #3 – Use SSMS to generate the comma-separated output and directly copy it over to notepad

All this custom query editing may not be possible if this is the output of a stored procedure (changing the stored procedure may break the application). Instead, the simplest option is to run through the following:

1. Right-click in the Query editor window containing the query to be executed
2. Choose “Query Options”
3. 
4. In the Query Options window, navigate to the “Results” node and click on “Text”
5. Change the “Output format” to “comma delimited”
6. 
7. Now press “Ctrl + T” or go to Query –> Results to –> Text
8. Execute the query. Notice that the output is ready to be copy-pasted directly into Notepad as a CSV file – NO formatting operations need to be done
9. 

Isn’t this great? Have SSMS take care of all your troubles, and take the saved time to read something new on Just Learned!

Benefit:

The benefit of the method shown above is that it is specific to the particular query window. So, once the query window is closed, SSMS will start honouring the default settings under Tools –> Options.

Other cool SSMS productivity features:

I have written about other cool productivity improvements in SSMS here: Some underappreciated productivity features of SSMS. Also, you can search through my blogs for more content on SSMS & it’s utilities.

Until we meet next time,

Be courteous. Drive responsibly.

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. 
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. 
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. 
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!

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.

This week, I will go back to my trusted gateway to Microsoft SQL Server – the SQL Server Management Studio (SSMS). I will present before you two great features of SSMS which all administrators and developers would find useful.

SSMS Property Pages

When working on multi-server queries within the SQL Server Management Studio (SSMS), we often find the need to get a summary of the failed connections, number of rows returned, etc. Similarly, when studying a graphical query plan, many a times it is required to study the internals of a particular operator in greater detail.

One of the options by which we can explore further is to write a query (generally using a DMV) or using a textual execution plan. However, that does not have to be the case. Go to View –> Properties or hit F4 to launch the Properties window/page, which will help us in getting all of this information at the click of a mouse button.

The Properties Page consists of the following basic elements. This list stands true for all properties windows in SSMS.

1. Mode Selector – see the list of properties & their values in a categorized form, or as a alphabetical list
2. Property Name & Property Values columns
3. Description pane, which gives a brief description of the particular property selected by the user

In the screen-shot above, you can see the default property page that comes up for a query in the query editor.

Property Pages when studying Graphical Execution Plans

When studying Graphical Execution plans, the Property Pages are a very powerful feature. Let’s write a sample query against a test SQL Server instance. Next turn choose to display the Estimated Execution Plan (Query –> Display Estimated Execution Plan, or press Ctrl+L).

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderDetail.SalesOrderID, Sales.SalesOrderHeader.RevisionNumber, Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, 
                      Sales.SalesOrderHeader.Status, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderDetail.CarrierTrackingNumber, Sales.SalesOrderHeader.CustomerID, 
                      Sales.SalesOrderHeader.SalesPersonID, Sales.SalesOrderHeader.TerritoryID, Sales.SalesOrderHeader.AccountNumber, 
                      Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.Comment
FROM         Sales.SalesOrderDetail INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN
                      Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND 
                      Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND 
                      Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Person.Person ON Sales.SalesPerson.BusinessEntityID = Person.Person.BusinessEntityID

The estimated execution plan for this query on my instance is shown below:

Now, let’s assume we want to understand more about the “Clustered Index Scan” operator applied on the index [SalesPerson].[PK_SalesPerson_BusinessEntityID]. All we need to do is to select the operator, and invoke the Properties page, which would look something like this:

For better search, here’s what you can get from the properties page:

1. CPU cost
2. I/O cost
3. Number of executions
4. Number of rows
5. Operator cost
6. Rebinds
7. Rewinds
8. Row Size
9. Sub-tree cost
10. Forced Index usage
11. Logical Operation
12. Node Id
13. Database
14. Index
15. Index Kind
16. Schema
17. Table
18. Ordered
19. Output column
20. Parallel execution
21. Table cardinality

As you can see, this land-mine of information is just a mouse click away! That too, without a single query due to the use of graphical tools available out-of-the-box with SSMS.

So, the next time you are analyzing a query, or writing one, take a look at the Properties window – you will be surprised with what you will find.

Until we meet next time,

Be courteous. Drive responsibly.

Recently, I had the opportunity to review the newest SQL book, hot off the press – the SAMS Teach Yourself SQL in 24 Hours, 5^th Edition. The book has been written by Ryan Stephens, Ron Plew & Arie D. Jones, and includes coverage of both – Oracle and Microsoft SQL Server implementations.

For 10 years now, the SAMS Teach Yourself series has helped kick-start the careers of millions of professionals around the world, and this book continues the tradition.

First impressions

The first thing that I realized was that this book was not about the specific implementation, but it is about the ANSI SQL standard, which to me increases the appeal even more. I am a strong believer in the fact that once the standard is known, implementations are not that hard to conquer. Every vendor will have their own version of the standard, but at the end, it’s all about combining the knowledge of the standard and logic to create practical solutions. This book does a very good job at explaining the standard, and earns points from me right from the start.

I am a Microsoft SQL Server professional, and I felt quite at home going through the book. The book follows a very balanced approach by explaining the ANSI standard first, and then following up with Oracle, Microsoft SQL Server & MySQL implementation samples, while explaining the differences between each!

Anatomy of the book

The book has been arranged into 24 chapters (or “hours”) spanning 8 parts (start from the very basics of a SQL query and go all the way to cover complex queries, performance, security & high-profile applications). Each chapter comprises of the following:

• Step-by-step instructions
  • Every explanation is backed by a step-by-step walk-through of the SQL standard code
• Quizzes & Exercises
  • The best part of the book
  • I enjoyed working my way through the Exercises & the Bonus Exercises in the appendices
• “By the way” nuggets
  • These are interesting pieces of information, and would recommend spending some time thinking about them – each nugget has the potential to expand into hours of research
• “Did you know?” moments
  • There are some hidden secrets about SQL and its various implementations in the book. I won’t spoil the experience by giving examples
• “Watch out!” indicators
  • I would say, watch out for the Watch outs! – they are goldmines of information
  • Throughout parts 4 to 7 (Complex queries, Performance tuning, Security & summarized data structures), these parts will give experienced professionals a run for their money!

My recommendations to the reader

What I would recommend is that everyone – beginners and intermediates alike – must take the time out and run through the “Watch out!” and “By the way” sections. The Quizzes & Exercises (including Bonus Exercises) are also a must solve. Surprisingly, they gave me a flashback of all that I knew in Microsoft SQL Server – definitely worth spending the time.

For beginners though, I would recommend spending time out and gain background on entity relations (ER) and ER Diagramming. Once the reader knows what a relational database is, and what the key concepts are, this book would be a breeze. Remember that the book is about a standard and implementations, not about concepts.

Readers should not ignore the appendices either. They contain a summary of all commands learnt in the 24 hours, bonus exercises and answers to the exercises & workshops after each chapter.

Finally, here’s my biggest tip – don’t try to complete this in 24 hours back-to-back. The best ratio I found was to cover 1 hour (chapter) each day, and then spending the time to solve exercise, and conduct follow-up research/reading.

What I liked most?

I liked the fact that the book tackles following difficult concepts with ease:

• Data-types
• Complex Query & Performance tuning concepts
  • Go from the smallest table to the largest
  • Think about and arrange your WHERE clause to be the most selective – a concept also called as SARGability in the Microsoft SQL Server world
• Basic database and server security

If I had to change one thing…

If I had to change one thing in the book, I would add a chapter, or an appendix on basics of relational databases. Readers need to understand that at the end of the day, the only relationship that exists is a 1:1 relationship between instances of entities (even a 1:N is multiple 1:1 relationships). Therefore, it is essential that the reader understands that a relational database is at its best if the user designs them in such a way that the query filters can be designed to return one and only one row (i.e. are most selective).

The final score

I would give this book a sound score of 4.5/5. Happy reading and best of luck with SQL!

I would like to thank Pearson education for giving me the chance to review the book. Best of luck!

Until we meet next time,

Be courteous. Drive responsibly.