Different Options for Query Results in 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.

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.

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.

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:

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.

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:

Next Steps

• Use these option along with options in these SQL Server Management Studio tips to customize SSMS for your needs.
• Read Using Object Explorer Details and Object Search Feature of SQL Server 2008 Management Studio
• Read Performance Analysis Using SQL Server 2008 Activity Monitor Tool
• Read Hiding System Objects in Object Explorer in SQL Server Management Studio