When working with SQL Server Management Studio (SSMS), you may come across a situation where you run a query but no results are displayed. This can be quite confusing, especially if the same query works fine on another computer. In such cases, the issue is most likely related to the SSMS settings rather than the server itself.

One possible reason for this behavior is the “Discard Results After Query Execution” option in SSMS. This option, when enabled, automatically discards the query results after execution. It can be a useful feature in certain scenarios, such as performance tuning exercises or when debugging execution plans.

To enable or disable this option in SSMS 2012, follow these steps:

1. Go to Tools > Options > Query Results > SQL Server > Results to Grid > Discard Results After Query Execution.
2. When enabled, this option will discard the results after the execution.

Disabling this option can improve performance by using less memory. However, it’s important to understand why someone would want to enable or disable this feature.

One common use case for enabling this option is during performance tuning exercises. When working with execution plans or tuning indexes, you may not need to see the results every time. By enabling this option, you can discard the results and focus on analyzing the performance impact of your changes.

Let’s take a look at an example using the AdventureWorks database:

USE AdventureWorks2012
GO

-- With "Discard Results After Query Execution" enabled
SELECT * FROM Sales.SalesOrderDetail
GO 10

-- With "Discard Results After Query Execution" disabled
SELECT * FROM Sales.SalesOrderDetail
GO 10

As you can see, enabling the “Discard Results After Query Execution” option allows you to run the query without displaying the results. This can be particularly helpful when dealing with large amounts of data, as it reduces the overhead of displaying and processing the results.

It’s important to note that enabling this option does not reduce the IO or CPU usage for SQL Server. It simply discards the results after execution, making it a useful tool for debugging on the development server.

Next time you encounter a situation where your query is not displaying any results in SSMS, remember to check the “Discard Results After Query Execution” option. It might just be the solution to your problem.