The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
How many options could there be with regards to query results? Well, surprisingly to many, there are quite a few. You can even setup SSMS to instantly export results from the query window directly to a custom delimited text file.
Running a query in SSMS generates results as pictured here. Typical columns and rows. Chances are you know you also know your immediate options with the grid. You can widen the columns, select blocks, and on right click of selected rows you have the option of including the column titles as a header row in your copy/paste operation. Tip: selecting all rows can be done by clicking the upper most left hand cell (see my red x) or right click select all.
Selecting to copy makes this grid easy to paste directly into Excel. Pasting elsewhere however has unpredictable results with regards to formatting. Dropping them in an email with word wrap can get very confusing.
Many SSMS users are not aware there are additional options with regards to the format and handling of results. The three most easily accessible are highlighted above.
1. Results To Grid (default) = also enabled by CTRL+D
2. Results To Text = also enabled by CTRL+T
Answer to paste problem above: For a small result set that you’d like to copy/paste into an email or a Word Doc, “Results to Text” is a good option for a more nicely formatted block that you can then copy/paste as appropriate.
With lots of rows, say thousands to even millions, you’re going to either want to use the export wizard we talked about yesterday or use the third option.
3. Results to File = also enabled by CTRL+SHFT+F
Results To File:
When the “Results to File” option is enabled you will be prompted with a ‘Save As’ dialog when executing your query instead of a Results window. By default this file receives an .rpt extension, but it creates a simple text file. You can also override the .rpt by specifying your own extension such as .txt in the file name box. The format of the text file is the same as what appears on screen when returning “Results to Text”. Psst, remember that for later.
Modifying the options related to “Results to File” is accomplished in Tools->Options->Query Results. Right off the bat you’ll notice that there are a set of options for “Results to Grid” and “Results to Text” but not “Results to File”. Keep in mind however, as stated above, your “Results to Text” options also control “Results to File”. Consider whatever you do here to affect the format of both options.
As an example you could set up a delimited result file by selecting the delimited format option such as comma or tab delimited or your own by selecting a custom delimiter and then specifying whatever delimiter you’d like – i.e. the pipe character. There are a lot of additional format options here. Take a minute to check all your options while reviewing this screen. Right align on numeric values for example. Pet peeve of mine, numeric values should always be right aligned.
Knowing your options with regards to text output should spark your curiosity as to what other options there may be that could enhance your result sets. Hop over and check your options with regards to the traditional grid output. When you do you might notice the option to enclose string values in quotes when exporting as .csv.
So wait… if it’s the “Results as Text” options that also affect how “Results to File” are formatted then why is this option in the “Results as Grid” page. The answer is buried in right click… isn’t is always?
Go back and run a query in “Results to Grid” mode again. Now right click in the grid. You’ll notice one of your options is “Save As”. Select that and you’ll see that one of your Save As options is a csv file. The quote string option above affects the “Save As” option in “Results to Grid” – not the “Results to File” options you modify in “Results to Text”.
Let’s make sure you got this. You can create a comma delimited file by modifying your “Results to File” options in the “Results to Text” option window, but if you want quotes to qualify your strings when exporting a comma delimited file, you must update your options in “Results to Grid” instead and use Right Click=> “Save As”. Clear enough? Sheesh??
If I had my druthers I would combine all the “Results to File” options into a single option box and then make them the same whether you were using this option directly or selecting “Save As” from one of the other options. Just an idea. Don’t let this one confusing aspect push you away from exploring your result options however. There is a lot of good stuff in there.
Another result option I really like is the option to save a query along with it’s results. That comes in very handy when vetting results with others. They can see both what you got, and how you got it.
That’s it for today – catch tomorrow where we dig into running SSMS in SQLCMD mode. There are some good reasons to do that very thing from time to time.