diff between cursor based approch and set based..?

Think of it this way. If your wife wants you to fold the washing that's come out of the tumble dryer - she might well say 'can you please fold the washing?'. That's a set based approach - it's an operation over a collection of items.

The cursor based approach would be the equivalent of your wife asking you to fold a t-shirt, then when you had come back with that asking you to fold a pair of trousers, then when you had come back with that, asking if you could fold a jumper. No doubt, that would really hack you off. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.

Think of it this way. If you are cutting wood so you have logs for your fire over for winter - You might well say I'm going to cut some wood. You would get in your pick-up with your saw and other tools. Cut down a tree, cut it into logs, load the logs onto the truck and bring it all back top stack on your log pile. That's a set based approach - it's an operation over a collection of items.

The cursor based approach would be the equivalent of going to the forest, cutting down a tree, cutting one log off it and bringing it home to stack on the wood pile, then returning to the forest to get another log, bringing it back to the log pile and so on.

No doubt, that would really hack you off, it would cost a lot of resources - fuel, time, traffic on the road etc. SQL Server doesn't fare much better, and really prefers to be asked to do things the set based way.

[absolute plagiarism of Matt's answer - just wanted a LumberJack version rather than doing the washing!!]

Jeff Moden (SSC contributor and all round SQL genius) has the following text in his signature over on the SSC site

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.

I've always liked that explanation of set-based vs looping

A great way to better try and understand the difference is through one explanation I heard a while back (can't remember who said/wrote it).

When working in T-SQL, try to tell the system what you want to do with the data, not how you want it done.

Most programming is the other way around, you tell the computer what it should do with an eye on the outcome almost being a secondary thought. With T-SQL / Set Based thinking, you will often win by constructing commands that explain what result you want - the query optimiser will then find a good way of arriving at that result.

A cursor is a programming construct that prevents the query optimiser from working with the entire data-set you are wanting to process. You queue up the data and manipulate each row, one at a time. The query optimiser then "thinks" that you are working with one single data row and will decide how to best satisfy your single row request.

An easy way to demonstrate the difference would be to imagine a table with 100 rows and wanting to return the rows to the client:

/* Test data creation */
IF EXISTS ( SELECT  *
            FROM    sys.tables T
            WHERE   name = 'CursorTest'
                    AND type = 'U' ) 
    BEGIN
        DROP TABLE dbo.CursorTest
    END ;
CREATE TABLE dbo.CursorTest
    (Col1 int,
     Col2 varchar(100)) ;
INSERT  INTO dbo.CursorTest
        (Col1,
         Col2)
        SELECT TOP 100
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
                'Row ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT   NULL)) AS varchar(100))
        FROM    sys.columns C
        CROSS JOIN sys.columns

Look at a cursor solution that gets the rows and returns them to the client:

/* Cursor Solution */

DECLARE @Col1 int,
    @Col2 varchar(100)

DECLARE @Results AS TABLE
    (Col1 int,
     Col2 varchar(100))

DECLARE Curs CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT  Col1,
        Col2
FROM    dbo.CursorTest CT

OPEN Curs

FETCH NEXT FROM Curs INTO @Col1, @Col2

WHILE @@FETCH_STATUS = 0 
    BEGIN
        INSERT  INTO @Results
                SELECT  @Col1,
                        @Col2

        FETCH NEXT FROM Curs INTO @Col1, @Col2

    END

CLOSE Curs
DEALLOCATE Curs

SELECT  *
FROM    @Results

Now look at the set based approach:

SELECT Col1,
       Col2
FROM dbo.CursorTest

This is an extremely simplified example, but shows that both will achieve the answer, but the cursor spends most of the time telling the system what to do instead of what results we want.

There are really very few reasons for using cursors nowadays, the programming constructs that are offered from SQL 2005 onwards reduced the need even further.

If you have a cursor based solution and want help removing it, let us know on here and we will gladly help.

On Cursor based approach otherwise procedure approach you are manipulating data set on a row-by-row basis otherwise called looping instead of Set.This will hurts the performance if you are dealing with large number of rows.

On set-based approach you are dealing with one or more set of records and the data manipulation is done using SQL Join Operators like OUTER, INNER JOINS, sub-queries etc. Most of the time Set-based approach outperforms Cursor based or procedural ways because, SQL Server engine can make use of indexes and statistics defined on underlying tables of the sets. Refer this link for more details