Tag: TSQL

Cursor in SQL Server

sql2008

SQL Server Cursor is a row base operator but it does not have good performance

because it create different execution plan per each rows, personality

I do not like use this operator so always I look for standards operators same as JOIN,SELECT,GROUP,…

and Loop operators instead of cursor.

By the way few times the cursor is useful;

please pay attention to below example:

Simple Sample:

USE Arm_DB 
GO

DECLARE @ProductID INT
DECLARE @ProductName NVARCHAR(50)
DECLARE @ProductPrice DECIMAL(10,2)

DECLARE Product_Cursor CURSOR FOR 
                                SELECT ID,NAME,Price 
                                    FROM dbo.TBL_Product

OPEN Product_Cursor FETCH FROM Product_Cursor INTO  @ProductID
                                                   ,@ProductName
                                                   ,@ProductPrice
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @ProductName ProductName
             ,SUM(Quantity)*@ProductPrice AS TotalSales 
            FROM dbo.TBL_Sales
            WHERE ProductID = @ProductID

        FETCH FROM Product_Cursor INTO @ProductID
                                      ,@ProductName
                                      ,@ProductPrice  
    END      
CLOSE Product_Cursor
DEALLOCATE Product_Cursor

Optimal Option: Use JOIN and GROUP BY instead of CURSOR 

SELECT 
      P.NAME AS ProductName 
     ,SUM(S.Quantity) * P.Price AS TotalSales
 FROM dbo.TBL_Sales S
  INNER JOIN dbo.TBL_Product P ON S.ProductID = P.ID
GROUP BY P.NAME,P.Price

Useful sample: Get Backup from all user’s databases in SQL Server instance

USE Master 
Go

DECLARE @Path NVARCHAR(600) = 'C:\Arman\Mine\MyBlog\CodingTips\DBbackup\'
DECLARE @FileDate NVARCHAR(50) = CONVERT(NVARCHAR,GETDATE(),112)
DECLARE @DBname NVARCHAR(50) = ''
DECLARE @FileName NVARCHAR(1000) = ''

DECLARE DB_Cursor CURSOR For 
                            SELECT name 
                            FROM sys.databases  
                            WHERE database_id > 4 --Start From User Database

OPEN  DB_Cursor FETCH NEXT FROM DB_Cursor INTO @DBname
     WHILE @@FETCH_STATUS = 0
      BEGIN

        SET @FileName = @Path + @DBname + '_' + @FileDate + '.Bak'    
        BACKUP DATABASE @DBName TO DISK = @FileName    
        FETCH NEXT FROM DB_Cursor INTO @DBname         
      END 
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
Advertisement

How to create table in SQL Server

sql2008

USE Arm_DB
Go

 IF (OBJECT_ID('TBL_Product','U') IS NOT NULL)
   DROP TABLE TBL_Product

Go

CREATE TABLE TBL_Product
(
ID INT IDENTITY(1,1)
,NAME NVARCHAR(50)
,Price DECIMAL(10,2)
,[Desc] NVARCHAR(MAX)
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,MOdifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TBL_Product PRIMARY KEY (ID)
)

GO 

 IF (OBJECT_ID('TBL_Sales','U')IS NOT NULL )
    DROP TABLE TBL_Sales

CREATE TABLE TBL_Sales
(
ID INT IDENTITY(1,1)
,ProductID INT 
,Quantity INT
,SalesDate DATETIME
,InsertedDate DATETIME DEFAULT SYSDATETIME()
,ModifiedDate DATETIME DEFAULT SYSDATETIME()
CONSTRAINT PK_TB_Sales PRIMARY KEY (ID)
CONSTRAINT FK_TBL_Sales_TBL_Product 
     FOREIGN KEY (ProductID) REFERENCES dbo.TBL_Product(ID)
)

GO

INSERT dbo.TBL_Product   ( NAME ,Price )
SELECT 'Book',32.4
UNION ALL
SELECT 'Pen',5.2
UNION ALL
SELECT 'Notebook',8.7

Go

INSERT INTO dbo.TBL_Sales( ProductID , Quantity ,SalesDate)
SELECT 1,4,'2013-Apr-10'
UNION ALL
SELECT 1,3,'2013-Apr-11'
UNION ALL
SELECT 1,8,'2013-Apr-13'
UNION ALL
SELECT 1,1,'2013-Apr-18'
UNION ALL
SELECT 2,4,'2013-Apr-10'
UNION ALL
SELECT 2,3,'2013-Apr-11'
UNION ALL
SELECT 2,8,'2013-Apr-13'
UNION ALL
SELECT 2,1,'2013-Apr-18'
UNION ALL
SELECT 3,4,'2013-Apr-10'
UNION ALL
SELECT 3,5,'2013-Apr-11'
UNION ALL
SELECT 3,3,'2013-Apr-13'
UNION ALL
SELECT 3,9,'2013-Apr-18'

SELECT * FROM dbo.TBL_Product

SELECT * FROM dbo.TBL_Sales