what is the difference between using Cursors and aTemp table

_{Tuesday, September 22, 2009 10:23 AM}

How both are useful in SQLSERVER

_{Tuesday, September 22, 2009 2:13 PM ✅Answered | 1 vote}

As suggested before, it all depends on the purpose.
However, I ran into an issue before where I had to move data from one database to another database. Through this process, I had to read each row from one table in one database, compare some values with another table or tables in another database, and then do INSERT or UPDATE.
Using CURSORS was taking forever, but I changed the code to use a temp table instead and noticed much better performance.
Abdallah, PMP, MCTS

_{Tuesday, September 22, 2009 10:31 AM | 2 votes}

Hi,
Both are for different purposes,
 

Cursor is a private SQL area.. its a temporary buffer used to hold the
transactional data on it n used to manipulate multiple rows through SQL block
statements.

Temporary table can hold the records and we can do DML operations
on this..

For a better understanding you have to go through reading Cursors and Temp tables separately.

**Please Vote & "Mark As Answer" if this post is helpful to you.

Cheers
Bikash Dash
MCDBA/MCITP**

_{Tuesday, September 22, 2009 10:31 AM}

Can you show us the situation where you have to decide in between of these two?

_{Tuesday, September 22, 2009 10:32 AM | 1 vote}

The cursors are used to iterate the rows one by one from a rowset.

Eg : Select customerid, Name From Customers. This query will return all the customer RowSets or Record Set. Using cursors we can access the records one by one.

But the temp table is like a ordinary table, but the scope is limited to the current context.. i.e., if you create a temproray table inside the stored procedure the temproary table will be deleted automatically after the execution of the Stored procedure.

finally, the cursors and temp tables both were two different concepts.

refer : http://msdn.microsoft.com/en-us/library/aa172595(SQL.80).aspx, http://www.sqlteam.com/article/temporary-tables

_{Tuesday, September 22, 2009 10:34 AM | 1 vote}

Both have their uses depending on what it you want to do. They're not really comparable without a problem you are trying to solve.

You should aim to use set based solutions if possible and cursors as a last resort. I try to avoid temp tables if possible, but certainly favour a temp table over a cursors.Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)

_{Tuesday, September 22, 2009 10:36 AM}

ALTER PROCEDURE [dbo].[usp_Mas_ProgramScheduleValidation] --'1900-01-01 10:00:00.000','2009-09-10 30:00:00.000','PGM50','CH1','0'
@StartTime datetime,
@ScheduleDate datetime,
@ProgramUId nvarchar(50),
@ChannelUId nvarchar(50),
@TimeReturn int out
AS  
BEGIN  
    
    CREATE TABLE #Temp (ROID int ,SCHEDULETIME DATETIME,ENDTIME DATETIME)
    DECLARE @Query varchar(5000)
    DECLARE @ColumnList varchar(500)
    SET NOCOUNT ON
    SET @ColumnList = 'ROW_NUMBER() OVER (ORDER BY CHANNELUID ASC) AS ROWID,SCHEDULETIME,ENDTIME'
    SET @Query = 'SELECT ' + @columnList + ' FROM tbl_Mas_ProgramSchedule WHERE ChannelUId = ' + '''' +@ChannelUId+'''' +' AND ScheduleDate= CAST(' + ''''+CAST (@ScheduleDate AS VARCHAR)+''' AS DATETIME)'
            
    INSERT INTO #Temp
    EXEC(@Query)

    DECLARE @i INT --This for while loop increment  
    SET @i=1  
    declare @MyCount int --Variable to know the number of records exist in #temp table  
    declare @LStartValue datetime  
    declare @LEndValue datetime  
    Declare @iRVal int  
    set @iRVal=0
    declare @EndTime datetime
    --Here StartTime is a Parameter value,But to know the EndTime Add ProgramDuration
    --to the StartTime given as a parameter
    select @EndTime=dateadd(second,(Hours\*60\*60)+(Minutes\*60)+Seconds,@StartTime) from
        tbl_Mas_Program where ProgramUId=@ProgramUId    
    
    SELECT @MyCount=COUNT(1) FROM #Temp   
        WHILE (@i<=@MyCount) --while start  
        BEGIN
            select @LStartValue=SCHEDULETIME from #Temp where ROID = @i  
            select @LEndValue=ENDTIME from #Temp where ROID = @i      
                IF(@StartTime>@LStartValue and @EndTime\<@LEndValue)        
                -- the above if is to check the given values are in between the existing
                --values record by record
                begin  
                    set @iRVal=1  
                    BREAK;
                end  
            set @i=@i+1  
        END --end of while  
        print('After while condition')
        IF(@iRVal=0)  
        begin
            print('After IF(@iRVal=0) condition ')     
             if((select count(1) from #Temp where SCHEDULETIME>=@StartTime)>0)  
            --To check the weather any records exist with values greater than StartTime
             begin
                  print('After starttime conditon')  
                  if(@EndTime<=(select min(SCHEDULETIME) from #Temp where SCHEDULETIME>=@StartTime))  
                  --To check the weather EndTime croses with any StartTime of existing
                  --records
                  begin
                    print('In the enddate condition')
                    set @TimeReturn=-5
                    --set @iRVal=-5
                  end
                  ELSE
                  BEGIN
                    set @TimeReturn=-6
                    --set @iRVal=-6
                  END
             end
             else
             begin
             set @TimeReturn=-7
             end       
        end
        ELSE
            set @TimeReturn=-8
            --set @iRVal=-8
    if(@@error=0)
    begin
        return @TimeReturn
        --return @iRVal
    end
    else
    begin    
        --set @TimeReturn=-9
        return -9
    end
            
END

Actual the senario is have insert the StartTime and EndTime values ,by checking weather there is any values exist with in that time interval.

MyPrograms are already schedule 10:00   to 11:00
                                                  4:00  to    5:00 ,now i want insert schedule the Program, other than the above two timings.

For this i wrote the above procedure.

Is is possible to write the above procedure with the  Cursor concept.

what are the advantages and disadvantages if we use Cursor when compare with the Temp table

_{Tuesday, September 22, 2009 1:46 PM}

I would suggest using google to look up the following topics "Set based SQL" , "Procedural SQL"

Here is a link to an article that does a half decent job of describing the difference.

http://weblogs.sqlteam.com/jeffs/archive/2007/04/30/60192.aspx

Have a good day!!!