Set-Based vs. Procedural

A procedural approach follows the paradigm of traditional languages used for applications. You perform a function then call another function and so on. When this method is used to retrieve data from a data source, it means requesting data from a set of information, then requesting another set and so on. Notice I used the term "set" within my description. Each retrieval task is requesting data from a separate set of data (even if from the same data source), and each request could return no rows or potentially thousands of rows or more.

The most obvious example of a procedural approach is the use of cursors. Let's take a look at the general syntax to see how it works.

DECLARE MyCursor CURSOR
    FOR SELECT ...
OPEN MyCursor

FETCH NEXT FROM MyCursor 
INTO @LocalVariable

WHILE @@FETCH_STATUS = 0
BEGIN
    -- do something

    FETCH NEXT FROM MyCursor 
    INTO @LocalVariable
END
CLOSE MyCursor
DEALLOCATE MyCursor

We know SELECT is the basic data retrieval command in the SQL language. Here we see it used in the definition of the cursor. Each time we execute the statement "FETCH NEXT", we are essentially re-executing that SELECT statement to get the next top result row. Here is a very similar approach without defining a cursor:

DECLARE @var varchar(50); SET @var = 'x';
WHILE @var is not null
BEGIN
    SET @var = 
    (
    	SELECT TOP(1) column1 FROM table1
    	WHERE column1 > @var
    	ORDER BY column1
    )
    IF @var is null
    	BREAK;
    -- do something
END

The idea behind a set-based approach is to attempt to retrieve the same final result but with as few set requests as possible. In fact, the ideal scenario is to design a solution that allows the entire final result to be retrieved from a single dataset.

Let's take a look at a simple example of storing the number of books currently checked out at the library into the local variable @BookCount.

Cursor solution:

DECLARE @BookCount int; SET @BookCount = 0;
DECLARE MyCursor CURSOR
    FOR SELECT book_id FROM books_checked_out
OPEN MyCursor

FETCH NEXT FROM MyCursor
INTO @BookCount

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @BookCount = @BookCount + 1;
    FETCH NEXT FROM MyCursor 
    INTO @BookCount
END
CLOSE MyCursor
DEALLOCATE MyCursor

WHILE loop solution:

DECLARE @BookCount int; SET @BookCount = 0;
DECLARE @BookID int; SET @BookID = 0;
WHILE @BookID is not null
BEGIN
SET @BookID =
(
	SELECT TOP(1) book_id
	FROM books_checked_out
	WHERE book_id > @BookID
);
    IF @BookID is not null
    	SET @BookCount = @BookCount + 1;
END

Set-based solution:

DECLARE @BookCount int;
SET @BookCount = (SELECT COUNT(*) FROM books_checked_out);

As you can see, not only is the set-based solution easier to read, it only issues a single statement to the engine as opposed to one statement per row. Most likely, the library doesn't have thousands of books checked out, so the difference here might not be too bad. But the difference can be very, very large if the data source contains millions of rows of data.

It is important to note that set-based design will not automatically result in faster performance, especially if that particular query includes subqueries in the SELECT clause. For example, a query like this:

SELECT a
    , (SELECT x FROM table2) AS field1
    , (SELECT y FROM table3) AS field2
    , (SELECT z FROM table4) AS field3
FROM table1

looks like a set-based approach but really isn't because each of those subqueries will have to run for each row returned by the main query. For more information on ways that some attempted set-based solutions really aren't truly set-based after all, read Jeff Moden's article here: http://www.sqlservercentral.com/articles/T-SQL/61539/ entitled "Hidden RBAR: Triangular Joins".

Edit:
In response to Matt's request, I decided to use the data from the FIFO challenge. In this simple example, though, I am just determining the current items. For each IN or RETurn transaction, inventory increases by the number of items in the transaction, and it decreases for each OUT transaction. Edit2:
corrected script and provided results

-- cursor
DBCC FREEPROCCACHE;
GO

DECLARE @StartTime datetime; SET @StartTime = GETDATE();
DECLARE @CurrentItems bigint; SET @CurrentItems = 0;
DECLARE @TranCode char(3), @Items int;
DECLARE MyCursor CURSOR
    FOR SELECT TranCode, Items FROM Stock

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @TranCode, @Items
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @TranCode IN ('IN', 'RET')
    	SET @CurrentItems = @CurrentItems + @Items;
    ELSE
    	SET @CurrentItems = @CurrentItems - @Items;
    FETCH NEXT FROM MyCursor INTO @TranCode, @Items
END
CLOSE MyCursor
DEALLOCATE MyCursor

SELECT 'cursor', DATEDIFF(ms, @StartTime, GETDATE()), @CurrentItems

GO

-- WHILE loop
DBCC FREEPROCCACHE;
GO

DECLARE @StartTime datetime; SET @StartTime = GETDATE();
DECLARE @StockID int; SET @StockID = 0;
DECLARE @CurrentItems bigint; SET @CurrentItems = 0;
DECLARE @TranCode char(3), @Items int;

WHILE @StockID is not null
BEGIN
    SET @StockID =
    (
    	SELECT TOP(1) StockID
    	FROM Stock
    	WHERE StockID > @StockID
    	ORDER BY StockID
    )

    IF @StockID is not null
    BEGIN
    	SELECT @TranCode=TranCode, @Items=Items
    	FROM Stock
    	WHERE StockID = @StockID

    	IF @TranCode IN ('IN', 'RET')
    		SET @CurrentItems = @CurrentItems + @Items;
    	ELSE
    		SET @CurrentItems = @CurrentItems - @Items;
    END
END

SELECT 'while', DATEDIFF(ms, @StartTime, GETDATE()), @CurrentItems

GO

-- set-based
DBCC FREEPROCCACHE;
GO

DECLARE @StartTime datetime; SET @StartTime = GETDATE();
DECLARE @CurrentItems bigint; SET @CurrentItems = 0;

SELECT @CurrentItems = @CurrentItems +
    CASE TranCode
    	WHEN 'IN' THEN Items
    	WHEN 'RET' THEN Items
    	WHEN 'OUT' THEN -Items
    END
FROM Stock

SELECT 'set', DATEDIFF(ms, @StartTime, GETDATE()), @CurrentItems

Results:

• Cursor = 48046 ms
• While loop = 30343 ms
• set-based = 843 ms

The set-based solution time was less than 3% of the while-loop time and less than 2% of the cursor time.

The best analogy I have seen comes from Jeff Moden's signature on the main site. And that is that with procedural code you are probably thinking about what you want to do to a row. With set-based code you are usually thinking about what you want to do to a column.

Why should you care?

The efficiencies inherent within a set-based solution can never hope to be matched by a procedural one, as a procedural one can only solve one entity at a time, wheras a set-based one aims to solve all entities in one pass.

My preference for explaining this question comes down to the paradigm that database systems employ.

When you really get into it, a database system runs on a computer, and eventually works things out iteratively. When you look at an execution plan, it shows you what it's doing, and it's incredibly set-based. Sure, there are things such as merge-joins rather than loop-joins which feel set-oriented, but it's still something that is programmed in C at the end of the day.

The power of 'set-based' comes into play because of the Query Optimizer. When you write a query rather than a procedure, there is a translation between that and what actually runs. That translation understands ways of simplifying the work involved, so that it can take advantage of indexes, spools, and so on.

When you use an iterative solution, you effectively tie the Query Optimizer's hands behind its back, forcing it into a particular path. The way you design the solution may not be entirely different to the way the plan works (consider writing an SSIS package - it's very easy to make one that works in the same way as an execution plan), but you're removing options that could cause it to be solved many times faster.

Combining this with the inherent slowness of explicit cursors (which require the data to be queried repeatedly, or storing temporary resultsets to satisfy the logic), and you find that 'set-based' solutions generally (but certainly not always) work faster.

One of the motivations of E.F.Codd when he first proposed the Relational Model of data was to devise a system that didn't require the looping, branching and step-at-a-time processing used in imperative programming languages. His idea was a system that would achieve all its results using only expressions based on a set of operators (relational algebra) returning or assigning relation values in a database.

SQL DBMSs unfortunately are not truly relational systems because SQL doesn't properly implement Codd's model. For example SQL uses tables and query expressions that are not true relations - they may contain duplicate rows and column names for example. For that reason SQL has to support lots of the old-style "procedural" language as well as the "set-based" type of expressions of the relational model.

Although the discussion here has focussed on cursors to a large extent, my pet hate is scalar functions in SELECT statements, where the scalar functions contain (and very effectively hide) SELECT statements of their own.

   SELECT  fn_someting(tbl1.col2) as col2_description
      ,fn_someting(tbl1.col3) as col3_description
      ,fn_someting(tbl1.col4) as col4_description
      ,fn_someting(tbl1.col5) as col5_description
      ,fn_someting(tbl2.col4) as t2_col4_description
      ,fn_someting(tbl2.col5) as t2_col5_description
      ,fn_someting(tbl2.col6) as t2_col6_description
      ,fn_someting(tbl2.col7) as t2_col7_description

I recently saw a stored proc improve from 7 hours to 2 minutes by replacing the functions with table joins. Some of the functions were SELECTing other scalar functions which themselves had SELECT statements. I can understand the rationale for scalar functions in SQL. It reduces the time it takes to write the code and (possibly) improves readability. It isolates the business logic into reusable code. It... just... doesn't... always translate that well into practical efficient SQL. I think the biggest problem is that they don't LOOK like RBAR. People who can develop set-based solutions to avoid loops will still fall for this trick.