Monday 30 April 2012

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


Sunday 29 April 2012

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

Thursday 26 April 2012

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

Wednesday 25 April 2012

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