Different Results output patterns – SQL Server

In Microsoft SQL Server Management Studio (SSMS) understanding and managing result output patterns is crucial for efficient database management. SSMS provides various options to customize how query results are displayed and saved. By default, query results are shown in a grid format but users can also choose to output results to text or save them to a file.

In this article, we will explore these different result output patterns in SQL Server and learn how to configure them to suit our needs.

Setting Default Destination for Results:

After logging in to Microsoft SQL Server Management Studio (SSMS), select the ‘Tools‘ Menu and the last menu option will be the ‘Options‘ menu. From this option, we can set the default destination for results.

Options:

When we click Tools -> Options menu the ‘Options‘ popup window as below is displayed. In this on the left there is a option called ‘Query Results’. Under this click ‘SQL Server‘ option, by default this option is selected. On the right side we can see the option to set the default destination for results.

Go to the option ‘Default destination for results‘ on the right side and under this heading there is a list box with 3 options. By default the ‘Results to grids’ option is selected. But there two more options ‘Results to text‘ and ‘Results to file‘ are available. We can click and see the these three options and change the default destination as required.

This window also has the option for setting the ‘Default location for saving query results’, which is needed when we set the destination option as ‘Results to file’ and in this destination the query result will be saved. The ‘Documents’ folder of the windows user is set as default.

There is also a third option from where we can set the below option ‘Play the Windows default beep when a query batch completed‘. When this is selected, a beep sound will come when a query is executed from query window.

There is also a button ‘Reset to Default’ at the bottom, on click of this, all default settings will be restored and any changes done under options settings will be removed.

Below we will see how the results output is displayed for each of the three options and what are the available options which can be set for these.

1. Results to Grids

When the default destination for query results is set to ‘Results to grids‘, then any sql query run will be displayed in a ‘Grid’ format as below in the query window.

Options for Results to grid:

Goto Query Results -> SQL Server -> Results to Grid

Select the ‘Results to Grid‘ option on the left side and the settings for this option will be displayed on the right side.

There are many options for displaying the result set in grid format as below:

Include the query in the result set: This option displays the SQL query under ‘messages‘ tab in the results section.

Display results in a separate tab: This is an interesting option when selected, this can display the result in a separate tab, which can be useful for viewing and scrolling long result set. It will look like the below with all three options ‘Edit’, ‘Result’, ‘Message’ on the top.

The maximum size for the result set can be set based on XML or Non-XML data.

2. Results to text

The default destination for results can be set as ‘Results to text‘ from ‘General‘ option. When this option is selected the output format for the result set will be in text format without grids as below. And the Output format is ‘Column Aligned’.

Below is the sample of general settings for Results to text:

Options for Results to text:

The ‘Results to Text‘ option can use different settings as below.

Output format: The SQL query result output format when displayed as text can be different types like: Comma delimited, Column Aligned, Tab delimited, Space delimited and custom delimited.

As an example, below is the output when we select the option ‘Comma delimited’.

In the above sample the other options like ‘Include column headers in te result set‘, which displays the column headers. The ‘Include the query in the result set‘ option when selected, displays the original query displayed on top above query result.

The option ‘Display results in a separate tab‘, when selected shows the query result in a separate tab instead of below the query window, results pane. Also, the maximum number of characters is set to 40 ( in this example) for each column to display more columns in the result without horizontal scrolling. The default size for a column is 255.

3. Results to File

The results to file option can be selected to send the query result to a file, which will be save in the default location set for saving query results as shown in the below example. This is the default location but the folder location can be changed to any other as needed.

Save File Name for Results to file:

When ‘Results to File‘ option is set as default for query results output, then when the sql query is executed from the query window, the file save popup window will display to provide the file name to save the results as below:

Once the file name is given and ‘save’ button clicked the results will be saved in the default location given to save the file.

Below is the sample output of results in file:

Overall, In this article we have discussed about the ‘Different results output patterns‘ available in MS SQL Server for query execution and viewing the results. we have learned in detail about the three main options for the output namely ‘Result to Grid‘, ‘Result to Text‘ and ‘Result to File‘ with all the additional options available for each type. Always the ‘Result to Grid‘ option is set as default for displaying query result but the different output options can be used when we need to display the query results differently, based on use cases.