NOCOUNT command returns number of rows affected by T-SQL statements after the command execution.

Syntax for NOCOUNT is :

SET NOCOUNT {ON/OFF}

SET NOCOUNT OFF : By default NOCOUNT option will be set to OFF in sql server. When ever a T-SQL statement is executed in sql server, then it will return the count of number of rows affected after the statement completion.

SET NOCOUNT ON : This command will not return the count of the number of records affected after command execution.

This option will reduce the extra overhead to the network , which will improve the performance of the sql code.

SET NOCOUNT ON option is especially useful in case of stored procedures.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function @@ROWCOUNT still works and can still be used in stored procedures to fetch information about how many rows were affected by the statement.

By deafault NOCOUNT will be set to OFF in sql server. This will return number of rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

TIP: Always SET NOCOUNT ON at the top of stored procedure for better performance.

By deafault NOCOUNT will be set to OFF in sql server. This will return number of rows affected at the end of each T-SQL statement exceution. In a stored procedure which comprises of many T-SQL statements , its not wise to return the number of rows at the end of each statement execution.

For printing the message with number of rows affected each time, sql server uses some part of system resources; Which is unneccesarily wasted for displaying unuseful informatio.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

For a stored procedures containing many T-SQL statements that do not return much actual data, SET NO COUNT ON can provide a significant performance boost because network traffic is greatly reduced.

TRUNCATE command consumes less time compared to DELETE. Also, Truncate operation will not consume space in transaction log.

Despite of these advantages, TRUNCATE will have its own disadvantages.

TRUNCATE will reset the seed of an identity column to its initial value. If we don't want seed of identity column to reset after data deletion from table, then truncate is not an apt choice.

Statistics on table will not be updated automatically after TRUNCATE operation unless AUTO UPDATE STATISTICS is ON . So, After every TRUNCATE Statement it's required to update the statistics on table manually. If this operation is not done, then wrong statistics will up used for sometime, which may hit the performance.If you wait long enough, and if you have Auto Update Statistics turned on for the database, then the statistics will eventually catch up with themselves. But this may not happen quickly, resulting in slowly performing queries because the Query Optimiser is using out-of-date statistics.

Running Screen saver in a machine consumes significant amount of CPU time.
If we are running the screen saver in the same machine as that of SQL Server running, then some part of CPU time is unnecessarily wasted for running screen saver.

So, When ever a query is running in sql server, always go for a blank screen saver instead of flashy and animated screen savers.

If we can create permanent (persistent) tables in database, why to go for temporary tables. Many people use temporary tables in database without knowing what the purpose and how it will add to performance tuning of queries.

This article will brief you about various types of temporary tables and will show you the examples for each type of temporary table.

"Divide'n'Conquer" a large "problem".

You can get absolutely mind boggling blinding speed out of some queries. This is the rule based on which temporary table concept came into picture. Many people think that "good set based code" has to be a single query to accomplish a task and nothing could be further from the truth. I've used Temp Tables to divide up queries that have couple of joins and that previous took anywhere from 30 minutes to 1 hour to run and have gotten them to run in seconds.

Temporary can be created at runtime and can do the all kinds of operations that one normal table can do. Scope of the temporary tables is limited to the session or instance. These tables are created in tempdb.

Temporary tables are used in the following scenarios:

When we are doing large number of row manipulation in stored procedures. In complex joins, instead of joining the main table directly we can use temporary tables. This will reduce the data load on the table which in turn reduces the execution time of the query.

This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.

Some important information about temp tables:

Temporary table can't have foreign key constraints on it.

The best way to use a temporary table is to create it and then fill it with data. i.e., instead of using Select into temp table, create temp table 1st and populate with data later

Different Types of Temporary Tables

In SQL SERVER we can create the following types of temporary tables.

Local Temp Table

Global Temp Table

Table Variable

Local Temp Table:

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign

Global Temporarytables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Here is an example for global temp table.

scope of Local Temporary table is only bounded with the current connection of current user. Where as, the scope of Global temporary table is the same for the entire user for a particular connection. We need to put "##" with the name of Global temporary tables

We can create a table a global table in one instance of database and we can use it in another instance of the same database until we disconnect from database.

Table Variable

We have a data type called TABLE in sql server. We can make use of this data type to create temporary tables in database. Here is the syntax for temporary table variable.

If you have less than 100 rows generally use a table variable.Otherwise use a temporary table.This is because SQL Server won't create statistics on table variables.