Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

Different Options for Query Results in SQL Server Management Studio


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server Management Studio

Problem

While looking through the new features and improvements in SQL Server Management Studio (SSMS), we found several options for displaying query results. In this tip we cover what options are available in SSMS and how they can assist you when dealing with query results.

Solution

Here are some of the different option settings in SSMS when dealing with result sets.

Change Default Output

SQL Server Management Studio currently supports query execution results to be displayed in three different ways: Results to Grid, Results to Text and Results to File.

By default SQL Server Management Studio is configured to display query results in Grid format. If you want to change the default to some other option follow these steps.

1. In SQL Server Management Studio, under Tools menu, click Options as shown in the snippet below.

there are several options in ssms for diplaying query results

2. In the Options dialog box, expand Query Results, expand SQL Server and then select General as shown in the snippet below. In the right side panel choose one of the three options shown below in the Default Destination for results drop down list and click OK to save the changes as shown in the snippet below. The changes will go into effect once you open a New Query window.

in the options dialog box, expand sql server

Query Result Options for Results to Text

In this format you have the option to display the results in a different tab as well as setting different output options.

In the Options dialog box, expand Query Results, expand SQL Server and then select Results to Text tab as shown in the snippet below. In the right side panel first select the checkbox for Display results in a separate tab and then select the checkbox for Switch to results tab after the query executes and then click OK. If you would like to also display the column name in the result set then choose the option Include column headers in the result set as shown in the snippet below.


query result options for results to text in ssma

The options you can use in the Text format are:

  1. Output format: - In the above snippet you could see that by default the output is displayed as columns aligned. Some of the other options that are available are Comma Delimited, Tab Delimited, Space Delimited and Custom Delimited. If you choose the Custom Delimited option in the Output format drop down list then you need to specify a character of your choice for the delimiter in the Custom delimiter text box.
  2. Include column headers when copying or saving the results: - If this option is selected, then whenever the results are copied to clipboard or it is saved to a file the column headers are also copied along with the results.
  3. Include the query in the result set: - The text of the query is displayed as part of query output under the messages tab.
  4. Scroll as results are received: - If this option is selected, then the display focuses on the most recently returned records at the end of the results set.
  5. Right align numeric values: - If this option is selected, then numeric values will be aligned to the right of the column.
  6. Discard results after query executes: - If this option is selected, then the query results are not displayed in the reviewing pane.
  7. Display results in a separate tab: - If this option is selected, then the result set after query execution will be displayed in a new tab instead of at the bottom of the query window.
    1. Switch to results tab after the query executes: - If this option is selected, then after the query execution the screen focus will be set to the results tab.
  8. Maximum number of characters displayed in each column: - This sets the maximum characters to display for any one column. So if you have a column that is 500 characters this will only show the first 256 if you keep the default setting.

Here is a sample output for the Text format:

sample output for the text format in ssms


Query Result Options for Results to Grid

In this format you also have the option to display the results in a different tab as well as setting different output options.

In the Options dialog box, expand Query Results, expand SQL Server and then select Results to Grid tab as shown in the snippet below. In the right side panel first select the checkboxes for Display results in a separate tab and then select the checkbox for Switch to results tab after the query executes and then click OK to save the changes.

query result options for results to grid in sql server

The options you can use in the Grid format are:

  1. Include the query in the result set: - The text of the query is displayed as part of query output under the messages tab.
  2. Include column headers when copying or saving the results: - If this option is selected, then whenever the results are copied to the clipboard or it is saved to a file the column headers are also copied along with the results.
  3. Quote strings containing list separators when saving .csv results: - This will enclose strings with double quotes if the string includes a comma.
  4. Discard results after execution: - If this option is selected, then the query results are not displayed in the reviewing pane.
  5. Display results in a separate tab: - If this option is selected, then the result set after query execution will be displayed in a new tab instead of at the bottom of the query window.
    1. Switch to results tab after the query executes: - If this option is selected, then after the query execution the screen focus will be set to the results tab.
  6. Maximum Characters Retrieved:
    1. Non XML data: - This is the maximum number of characters to retrieve for one column.
    2. XML data: - This is the maximum amount of data to retrieve for an XML data type.

Here is a sample output for the Grid format:

sample output in ssms
Next Steps


Last Update:


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 07, 2016 - 6:23:11 PM - Rick McCallum Back To Top

 Since upgrading to SQL 2012 I have been unable to get the record count to display in SQL Server Management Studio on the bar at the bottom of the query results and can't find the setting to switch it on, can you help with this


Thursday, May 30, 2013 - 1:05:31 PM - Amanda Back To Top

I'd like to be able to change the query options programmatically.  Any thoughts? 


Wednesday, May 23, 2012 - 8:17:56 AM - Ajesh Back To Top

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

sir,

   how i can solve this problem,pls help me


Saturday, May 19, 2012 - 1:26:51 PM - Jimo Back To Top

Sorry...I can see where I typo'd the @@rowCOUNT and put the o before the c, resulting in a 'bad word'...I hate to say it but my fast typing has gotten me into embarrasing trouble with that system variable before in a spec document :)


Saturday, May 19, 2012 - 1:24:08 PM - Jimo Back To Top

Hi,

Good info...but one thing I didn't see and REALLY would like--is there a way to force output at every PRINT or 'Results' event?

For example, I have a set of a dozen long-running querys in the window.  I want to see the @@rowo*** from the first one *NOW*--instead of seeing dump of all the results at once 20 minutes later after all the querys have run.

Is this possible?

Thanks,

--Jim


Thursday, April 19, 2012 - 1:09:50 PM - Noa Back To Top


I followed this article and changed to "Results to Text".
The article mentions that the changes will go into effect once you open a New Query window.
This did not happen, as I still get Grid results.
I then tried to close and re-open SSMS and the results are still in Grid output.
(I made sure the settings are still "Results to Text" ).

Am I missing something? (Using 2008)

 

Thanks.


Learn more about SQL Server tools