Importance of SET NOCOUNT in Stored Procedures

                    Today , I am going to share one of important concept .It sounds very small but it is very important.Whenever we write any procedure and execute it a message appears in message window that shows number of rows affected with the statement written in the procedure.

e.g

CREATE PROCEDURE Test_no_count

AS

    SET NOCOUNT OFF

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count

GO

Go
    Default value is SET NOCOUNT OFF.We don't need to specify OFF.For demo purpose I have specified explicitly.

If it is off then it will return affected row message as marked circle sometime affected row count will be multiple  of 100s so this message will creates an extra overhead on the network. 

By using SET NOCOUNT we can remove this extra overhead from the network, that can actually improve the performance of our database and our application.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

GO

CREATE PROCEDURE Test_no_count

AS

    SET nocount On

  BEGIN

      SELECT 1

      SELECT 2

      SELECT 3

      SELECT 4

  END 

GO

Exec Test_no_count 

GO

     Important thing is @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

           SET NOCOUNT ON statement can be useful in store procedures. SET NOCOUNT ON statement into store procedures can reduce network traffic, because client will not receive the message indicating the number of rows affected by T-SQL statement. Setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Disable and Enable the Foreign Key Constraint on the table in SQL Server

     Its  interesting concept but need to know while working on huge database.Recently my friend attended one of the interview .He was rejected there.Interviewer asked him one question which he found difficult while attending that interview.So he asked me to help him to understand the solution.
   He said I  never heard of disabling and enabling constraints but I heard about triggers.Sometime we work on test environment while development. Whenever we deliver any feature to QA team .They test lot of positive and negative scenario for regression.
Let us assume there are some negative cases which we have to test  .Let say we have two table
1.Table having primary key .
2. Table having Foreign key.
e,.g

 CREATE TABLE TEST_PRIMARY
  (
     ID INT PRIMARY KEY
  )

GO

CREATE TABLE TEST_FOREIGN
  (
     ID     INT IDENTITY (1, 1),
     PVALUE INT,
     FOREIGN KEY (PVALUE) REFERENCES TEST_PRIMARY(ID)
  )

GO

INSERT TEST_PRIMARY
       (ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

GO

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (1),
            (2),
            (3)

GO
  Now QA want to test  some negative case for example they are interested to know how system will behave if  constraints are not working properly means there are no constraints on table.

e.g

Syntax :
EXEC Sp_msforeachtable   "ALTER TABLE <table Name> NOCHECK CONSTRAINT all"
Query :
EXEC Sp_msforeachtable   "ALTER TABLE  TEST_FOREIGN NOCHECK CONSTRAINT all" 

Now if  you will try to  insert value into TEST_FOREIGN other than reference value it will allow.

e.g.

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (4),

  Normally it should not allow.

Now  we can enable constraints back again
e.g
EXEC sp_msforeachtable "ALTER TABLE <table name> WITH CHECK CHECK CONSTRAINT all"

EXEC sp_msforeachtable "ALTER TABLE Test_foreign WITH CHECK CHECK CONSTRAINT all"

Note : before enabling constraint we need to delete data which are conflicting  primary key relation from TEST_FOREIGN table.