How to Hide Rows Affected Message in SQL Server - SET NOCOUNT

Probably, this is one of the simplest concepts for most of the SQL developers. If you are very frequent in performing the DML statements, then you would have seen the “rows affected” message in the result tab. In most cases, the rows affected message help us in rectifying the actual rows that are either inserted or updated. But, just hope that the stored procedure written by you doesn’t require a count of the rows being affected. In such cases, we can use a command as SET NOCOUNT to just hide them. Doing so can as well increase the performance of the query. To be very straight, most of my initial Stored Procedures were without the SET NOCOUNT command.

General syntax of using NOCOUNT for hiding messages:

SET NOCOUNT ON

General syntax of using NOCOUNT for showing messages:

SET NOCOUNT OFF

Let’s try with an example of rows affected message in the results tab.

I have created a table as SQLArena_Test and imported the data from the SQLArena_Test table to a test temp table. Here, we are seeing the rows affected message as I have not mentioned the SET NOCOUNT statement. 

Without SET NOCOUNT ON:

insert into ##Test

select * from SQLArena_Test

Here, the  SET NOCOUNT ON is not added, so the number of rows affected message will be displayed while importing the data.

With SET NOCOUNT ON:

SET NOCOUNT ON

insert into ##Test

select * from SQLArena_Test

Here, the  SET NOCOUNT ON is added, so the number of rows affected message will not be displayed while importing the data.

NOT IN Not Working in SQL Server

This may sound a little silly for SQL experts, but can really help for someone working newly on SQL queries. The NOT IN query is used when there is a need to select the data on the first table, but not the matching records on the second. While selecting such data, there are chances of getting just the NULL in the output though there should be records for display.  

Let’s now take a simple example as below for a demonstration:

select ID from Table1

where ID not in (select ID from table2)

From the above query, we have an empty result. Most of the time there is confusion when there are millions of records and the NULLS are left unnoticed. Having NULLs in the columns for comparison can lead the results to be NULL.

Let’s try the same query by just adding IS NOT NULL for table2 in the WHERE clause.

select ID from Table1

where ID not in (select ID from table2 where ID is not null)

Here, we can see that the results are coming up for us. There are chances that we may forget or miss that thought that NULLs are present in either of the tables. So, always use the IS NOT NULL statement to fix the confusion of NOT IN not working.