Alteration of CURSOR

As we all know that the cursor is a bad performer. We always try to avoid cursor.

So the question is how we avoided the cursor? This article give us the demonstration, how to avoid the cursor. 

In this example i am trying to make a stored procedure for Calculate the total quantity and sales of the product in product wise. It will calculate for all the products  in Product Master table.

  

First example is using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

    SET NOCOUNT ON

    DECLARE my_cursor CURSOR

            FOR SELECT pID

                FROM   m_Products

           

    DECLARE @pID    INT

    DECLARE @pName  VARCHAR(100)

    DECLARE @topQty INT

    DECLARE @tot    MONEY

  

    DECLARE @prod_Sale TABLE

                  (

                        srlId     INT IDENTITY(1,1),

                      iProdID   INT,

                      vProdName VARCHAR(100),

                      iTotQty   INT,

                      iGrandTot MONEY

                  )

    OPEN my_cursor

 

    FETCH NEXT FROM my_cursor INTO @pID

  

    WHILE @@FETCH_STATUS = 0

            BEGIN

               SELECT @pName = prod_name

               FROM   m_Products

               WHERE  pID = @pID

              

               SELECT @topQty = SUM(qty),

                      @tot    = SUM(unit_Price*qty)

               FROM   tran_Orderdetail  

               WHERE  pID = @pID

              

               INSERT INTO  @prod_Sale

                           (iProdID,vProdName,iTotQty,iGrandTot)

               VALUES(@pID,@pName,@topQty,@tot)

           

               FETCH NEXT FROM my_cursor INTO @pID

            END

           

     CLOSE my_cursor

     DEALLOCATE my_cursor

 

     SELECT * FROM @prod_Sale

END

GO

EXEC Pr_ProdSalesRpt

Now I am trying to convert it without cursor.

It is quite simple and just needed some programmatic technique to control loop… etc.

Example to convert the stored procedure WITHOUT using CURSOR

IF OBJECT_ID('Pr_ProdSalesRpt','p') IS NOT NULL

BEGIN

   DROP PROCEDURE Pr_ProdSalesRpt

END

GO

CREATE PROCEDURE Pr_ProdSalesRpt

AS

BEGIN

      SET NOCOUNT ON

    SET XACT_ABORT ON

    DECLARE @pID       INT

    DECLARE @pName     VARCHAR(100)

    DECLARE @topQty    INT

    DECLARE @tot       MONEY

    DECLARE @Index     INT

    DECLARE @RecordCnt INT

  

    DECLARE @ProdID TABLE

            (

              srlId   INT IDENTITY(1,1),

              iProdID INT

            )

    DECLARE @prod_Sale TABLE

                  (

                        srlId      INT IDENTITY(1,1),

                      iProdID    INT,

                      vProdName  VARCHAR(100),

                      iTotQty    INT,

                      iGrandTot  MONEY

                  )

  

    SELECT @Index = 1

    INSERT INTO @ProdID

               (iProdID)

    SELECT   pID

    FROM     m_Products

    ORDER BY pID ASC

  

    SELECT @RecordCnt = COUNT(srlId)

    FROM   @ProdID

  

    WHILE (@Index <= @RecordCnt)

            BEGIN

                     SELECT @pID = iProdID

                     FROM   @ProdID

                     WHERE  srlId = @Index

                    

                     SELECT @pName = prod_name

                     FROM   m_Products

                     WHERE  pID = @pID

                    

                     SELECT @topQty = SUM(Quantity),

                            @tot    = SUM(UnitPrice*Quantity)

                     FROM   tran_Orderdetail

                     WHERE  pID = @pID

                    

                     INSERT INTO  @prod_Sale

                       (iProdID,vProdName,iTotQty,iGrandTot)

                     VALUES(@pID, @pName, @topQty, @tot)

                    

                     SELECT @Index = @Index + 1

            END

           

     SELECT * FROM @prod_Sale

END

GO

I think the construction of without CURASOR is quite easy and it definitely improve the performance.

Hope you like it.

Posted by: MR. JOYDEEP DAS

CURSOR Type

Sometimes it is necessary to use the cursor, as we know that the cursor is a big performance killer and we always avoid it. But we can save some performance degradation if we know the type of cursor very well.

This article illustrates the cursor type used in SQL Server. Proper use of cursor type is necessary for increase the performance and for operational use.

The cursor type is declared when we declare the cursor. There are 5 types of cursor mentioned bellow.

STATIC

Static cursor is a fixed snapshot of a set of rows. This fixed snapshot is stored is a temporary database (tempdb). As the cursor is used private snapshot any changes to the set of rows external will not visible in the cursor while browsing through it.

Syntax:

DECLARE <cursor_name> CURSOR STATIC

        FOR   SELECT *

              FROM   <Table_name>

              WHERE  <Conditions>

KEYSET

In KEYSET the key value of the rows are saved in tempdb. For instance let’s says the cursor has faced the following bellow data. So only the “supplierid” will be stored in tha databse. Any new instance happening is not reflected in the cursor. But any updated in the keyset values are reflected in the cursor. Because the cursor is identified by key values we can also absolutely fatches them using “FETCH ABSOLUTE 12 FROM mycursor”

DYNAMIC

In dynamic cursor you can see any kind of changes happening. That means inserting new records or changing the existing records and even record deletions. That’s why the dynamic cursor are slow and have least performance.

FORWARD_ONLY

As the name suggest they only move forward and only a one time featch is done. In every fetch the cursor is evaluated. That means any changes to the data are known, until we have specified “STATIC” or “KEYSET”

FAST_FORWARD

This type of cursor is forward only and read-only in every fetch they are not re-evaluted again. This make them a good choice to increase performance.

GLOBAL and LOCAL cursors

GLOBAL are global for a connection. By default cursor are global. That means you can declare a cursor in one stored procedure and access it outside also.

Locate cursor are accessable only inside the object(SP or Trigger or Function).

We can declare a cursor as LOCAL or GLOBAL in the DECLARE cursor sysntax.

Syntax:

DECLARE <cursor_name> CURSOR [ LOCAL | GLOBAL ]

[ STATIC | KEYSET | DYNAMIC | FORWARD_ONLY  | FAST_FORWARD ]

        FOR   SELECT *

              FROM   <Table_name>

              WHERE  <Conditions>

Hope you like it.

Posted by: MR. JOYDEEP DAS

STUFF Function

  

In this article I am trying to illustrate a vary useful function of SQL Server called STUFF.

STUFF Function

Stuff is a T-SQL Function used to delete a specified length of characters within a string and replace with another set of characters.

  

Syntax:

STUFF(Character_expression1, start, length, Character_expression2)

Character_expression1:

Represents the string in which the stuff function is to be applied.

Start:

It indicates the starting position of the character in the string of Character_expression1.

Length:

The length of the characters, which need to be replaced.

Charcter_expression2:

 It is the string that will be replaced to the start position.

Example:

SELECT STUFF('joydeep', 2, 3, 'xxxxx')

---------------

jxxxxxeep

Here "o" is the starting position 2, so from position 2 it takes 3 character "oyd" and replace it by "xxxxx".

Posted by: MR. JOYDEEP DAS

Interview Question and Answer

[ New Question Added on Date: 08-MAY-2012 ]

All of my readers are invited to SQL server Interview Question and Answer. Please look at the new section and make your comments on it. 

You can find the link from my web "SQL Knowledge Bank" [Upper right Side]

Last Update Date:  08-MAY-2012 @ NEW 5 Question is Added

Posted by: MR. JOYDEEP DAS

Ranking Function

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic and return Big Integer values.  

This article is related to RANKING Function.

ROW_NUMBER function

The ROW_NUMBER function returns the row number over a named or unnamed window specification.

The ROW_NUMBER function does not take any arguments, and for each row over the window it returns an ever increasing BIGINT. It is normally used to limit the number of rows returned for a query. The LIMIT keyword used in other databases is not defined in the SQL standard, and is not supported.

• Derby does not currently allow the named or unnamed window specification to be specified in the OVER() clause, but requires an empty parenthesis. This means the function is evaluated over the entire result set.
• The ROW_NUMBER function cannot currently be used in a WHERE clause.
• Derby does not currently support ORDER BY in sub queries, so there is currently no way to guarantee the order of rows in the SELECT sub query. An optimizer override can be used to force the optimizer to use an index ordered on the desired column(s) if ordering is a firm requirement.

RANK()  function

If two or more rows tie for a rank, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.

DENSE_RANK()function

If two or more rows tie for a rank in the same partition, each tied rows receives the same rank.
For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number two. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

NTILE()  function

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each. If on the other hand the total number of rows is divisible by the number of groups, the rows will be evenly distributed among the groups. For example, if the total number of rows is 50, and there are five groups, each bucket will contain 10 rows.
Example are mentioned bellow

CREATE TABLE #EXMAPLE_TABLE

       ( CODE    INT IDENTITY(1,1) NOT NULL,

         SNAME   VARCHAR(50)       NOT NULL,

         GRADE   CHAR(1)           NOT NULL)

GO

INSERT INTO #EXMAPLE_TABLE(SNAME, GRADE)

VALUES

('Raja', 'A'),   

('Prja', 'B'),

('Saja', 'C'),

('Khaja', 'A'),

('Baja', 'B'),

('Balaram', 'C'),

('Majhi', 'B'),

('Sajal', 'B'),

('Sarita', 'C'),

('Sarika', 'C')  

SELECT    ROW_NUMBER() OVER(ORDER BY GRADE) [ROW_NUMBER],

              RANK() OVER(ORDER BY GRADE) [RANK],

              DENSE_RANK() OVER(ORDER BY GRADE) [DENSE_RANK],

              NTILE(4) OVER(ORDER BY GRADE) [NTILE],

          SNAME, GRADE

FROM      #EXMAPLE_TABLE  

ROW_NUMBER  RANK  DENSE_RANK  NTILE       SNAME GRADE

1           1     1     1     Raja        A

2           1     1     1     Khaja       A

3           3     2     1     Baja        B

4           3     2     2     Prja        B

5           3     2     2     Majhi       B

6           3     2     2     Sajal       B

7           7     3     3     Sarita      C

8           7     3     3     Sarika      C

9           7     3     4     Saja        C

10          7     3     4     Balaram     C

SELECT    ROW_NUMBER() OVER(PARTITION BY GRADE ORDER BY GRADE) [ROW_NUMBER],

          SNAME, GRADE

FROM      #EXMAPLE_TABLE

ROW_NUMBER        SNAME       GRADE

1                 Raja        A

2                 Khaja       A

1                 Baja        B

2                 Prja        B

3                 Majhi       B

4                 Sajal       B

1                 Sarita      C

2                 Sarika      C

3                 Saja        C

4                 Balaram     C

Hope you like it.

Posted by: MR. JOYDEEP DAS

Dropping Constraint

Sometimes it is necessary to drop all the primary key, foreign key or unique constraint from a specified table. Here is this article I am preparing a T-SQL stored procedure to clean the constraint according to your needs.

It takes Database name, table name, constraint name and Type of constraint. If we not provide the Table name it means all the table are affected in specified DB and if we not provide the constraint name it means the entire constraint name are affected Or we can specified the Type of the constraint without providing constraint name.

To know about all the constraint in a specified Table uses these SQL statements.

SELECT *

FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE  TABLE_NAME='my_table'

As the foreign key dependency is there u must technically map the table objects.

It is a proto type only and u must develop it according to your needs.

IF EXISTS (SELECT *

           FROM   sysobjects

           WHERE  type = 'P'

                  AND name = 'up_RUN_CONSTRAINTCLEANER')

      BEGIN

            DROP  PROCEDURE  up_RUN_CONSTRAINTCLEANER

      END

GO

CREATE Procedure [dbo].[up_RUN_CONSTRAINTCLEANER]

      (

        @P_DBName   VARCHAR(MAX) = NULL,

        @P_TBlName  VARCHAR(MAX) = NULL,

        @P_ConsName VARCHAR(MAX) = NULL,

        @P_ConsType VARCHAR(MAX) = NULL   -- P, F, U

      )

AS

    DECLARE @SQL   NVARCHAR(MAX)

    DECLARE @CTYPE NVARCHAR(MAX)

BEGIN

       IF ISNULL(@P_DBName,'')=''

          BEGIN

              PRINT 'Database name can not be blank'

          END

       ELSE

           BEGIN

                         IF  ISNULL(@P_TBlName,'')=''

                              WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME

                                                            FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                                          INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                                            WHERE  a.CONSTRAINT_CATALOG = @P_DBName)

                                           BEGIN

                                                      SELECT    @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME

                                                       FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                                           INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                                      WHERE     a.CONSTRAINT_CATALOG = @P_DBName

                                                  EXEC    SP_EXECUTESQL @SQL

                                                 END

                         END                                       

         

       IF ISNULL(@P_TBlName,'')<>''

          BEGIN

              WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME

                                                  FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                             INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                   WHERE  a.CONSTRAINT_CATALOG = @P_DBName

                                                   AND B.NAME=@P_TBlName)

                                        BEGIN

                                                SELECT    @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME

                                                FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                                 INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                      WHERE     a.CONSTRAINT_CATALOG = @P_DBName

                                         EXEC    SP_EXECUTESQL @SQL

                                        END

          END

       ELSE

          BEGIN

             IF ISNULL(@P_ConsName,'')<>''

                BEGIN

                   WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME, a.CONSTRAINT_NAME

                                                      FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                                           INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                                      WHERE  a.CONSTRAINT_CATALOG = @P_DBName

                                                                         AND b.NAME=@P_TBlName

                                                                         AND a.CONSTRAINT_NAME=@P_ConsName)

                         BEGIN

                             SELECT    @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME

                            FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                  INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                            WHERE     a.CONSTRAINT_CATALOG = @P_DBName

                                               AND a.CONSTRAINT_NAME=@P_ConsName

                             EXEC    SP_EXECUTESQL @SQL

                         END

                END

             ELSE

                BEGIN

                   IF ISNULL(@P_ConsType,'')<>''

                      BEGIN

                        IF @P_ConsType='P'

                           BEGIN

                                SET @CTYPE='PRIMARY KEY'

                           END

                        IF @P_ConsType='F'

                           BEGIN

                                SET @CTYPE='FOREIGN KEY'

                           END

                        IF @P_ConsType='U'

                           BEGIN

                                SET @CTYPE='UNIQUE'

                           END

                                

                 WHILE EXISTS(SELECT B.NAME TBL_NAME, a.CONSTRAINT_NAME, a.CONSTRAINT_TYPE

                                                     FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                             INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                                     WHERE  a.CONSTRAINT_CATALOG = @P_DBName

                                                                      AND b.NAME=@P_TBlName

                                                                  AND a.CONSTRAINT_NAME=@P_ConsName)

                         BEGIN

                                SELECT    @SQL = 'ALTER TABLE ' + B.NAME + ' DROP CONSTRAINT ' + CONSTRAINT_NAME

                                FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS a

                                                   INNER JOIN sys.tables b ON a.TABLE_NAME=b.name

                                WHERE     a.CONSTRAINT_CATALOG = @P_DBName

                                                   AND a.CONSTRAINT_TYPE =@CTYPE

                                EXEC    SP_EXECUTESQL @SQL

                         END

               END

           END  

       END

 -- PRINT 'SUCCES -- Constraint Removed'       

END

/*

-- Settings-1

EXEC up_RUN_CONSTRAINTCLEANER]

     @P_DBName   = 'my_db',

     @P_TBlName  = NULL,

     @P_ConsName = NULL,

     @P_ConsType = NULL   -- P, F, U  

-- Settings-2

EXEC up_RUN_CONSTRAINTCLEANER

     @P_DBName   = 'my_db',

     @P_TBlName  = 'my_table',

     @P_ConsName = NULL,

     @P_ConsType = NULL   -- P, F, U   

    

-- Settings-3

EXEC up_RUN_CONSTRAINTCLEANER]

     @P_DBName   = 'my_db',

     @P_TBlName  = 'my_tab',

     @P_ConsName = 'FK_my_table',

     @P_ConsType = NULL   -- P, F, U 

    

-- Settings-4

EXEC up_RUN_CONSTRAINTCLEANER]

     @P_DBName   = 'my_db',

     @P_TBlName  = 'my_tab',

     @P_ConsName = '',

     @P_ConsType = 'F'   -- P, F, U            

*/

Hope you like it.

Posted by: MR. JOYDEEP DAS