Cursor in SQL Server

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