SET NOCOUNT ON; - Improve performance of stored procedure

Benefit: - By Setting NOCOUNT as ON we improve the performance of the stored procedure

Problem: -

One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible.  As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle.  This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution: -

Sometimes even the simplest things can make a difference.  One of these simple items that should be part of every stored procedure is SET NOCOUNT ON.  This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed.  This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

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 your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

Source: - http://www.mssqltips.com/tip.asp?tip=1226

192 unique visitors

All Databases and All Tables

• Select All Databases from a Server (this is a system stored procedure)
  

sp_databases

• Select All Tables from a Particular Database
  

select name from sysobjects WHERE type = 'U'

106 unique visitors

What is DDL, DML, DCL, TCL in SQL ?

The Data Definition Language (DDL) includes: -
CREATE TABLE - creates new database table
ALTER TABLE - alters or changes the database table
DROP TABLE - deletes the database table
CREATE INDEX - creates an index or used as a search key
DROP INDEX - deletes an index

The Data Manipulation Language (DML) includes: -
SELECT - extracts data from the database
UPDATE - updates data in the database
DELETE - deletes data from the database
INSERT INTO - inserts new data into the database

The Data Control Language (DCL) includes: -
GRANT – gives access privileges to users for database
REVOKE – withdraws access privileges to users for database

The Transaction Control Language (TCL) includes: -
COMMIT – saves the work done
ROLLBACK - restore the database to original since the last COMMIT

70 unique visitors

A Database Trigger

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

Difference between a trigger and a stored procedure

1- when you create a trigger you have to identify event and action of your trigger but when you create s.p you don't identify event and action

2-trigger is run automatically if the event is occured but s.p don't run automatically but you have to run it manually

3- within a trigger you can call specific s.p but within a s.p you cann;t call atrigger

68 unique visitors

Pass a String (List) to a Stored Procedure and Update

ALTER PROCEDURE [dbo].[sprocUpdateStatusInProductMaster]
@List nvarchar(4000) -- value assigned to @List from DataLayer
AS
BEGIN
declare @ProdId2 int;
declare @Status2 nvarchar(100);
declare @CountRows int;
mailto:declare@val int;
declare @i int;
set @i = 1;
-- format of @List is @List = {1,True;2,False;3,True;4,False;5,False;}
-- execute sprocUpdateStatusInProductMaster ';1,True;2,False;3,True;4,False;5,False;'
set @List = ltrim(rtrim(Substring(@List,CharIndex(';',@List)+1,Len(@List)))); -- to eliminate the first semicolon ;
while(len(@List)>0)
BEGIN
set @ProdId2 = ltrim(rtrim(Substring(@List,1,CharIndex(',',@List)-1)));
set @List = ltrim(rtrim(Substring(@List,len(@ProdId2)+2,len(@List))));
set @Status2 = ltrim(rtrim(Substring(@List,1,CharIndex(';',@List)-1)));
set @List = ltrim(rtrim(Substring(@List,len(@Status2)+2,len(@List))));

update tblProductMaster set IsActive = @Status2
where ProductID = @ProdId2;
END
END