The use of database "sets" rather than "functions"

Question

I've currently got a MSSQL stored procedure that uses multiple SQL functions to achieve a number of returns. Now I've been told about using sets rather than functions as it creates " bad code smell of a DBA who is not yet thinking in sets" Original Link

I'd like to better my code and love to know what sets are. So below are two of my functions from a long list of functions. Thanks to any that attempt the answer.

Function that returns a long list of values

CREATE FUNCTION dbo.funcGetPropertyEnviListFR
(
    @PropertyId AS INT
)
RETURNS VARCHAR(200)
AS
BEGIN
    DECLARE @ENVIFR VARCHAR(200)
    SET @ENVIFR = ''


        SELECT
            @ENVIFR = COALESCE(@ENVIFR + ', ', '') + ENVIRONMENT.FR
        FROM
            ENVIRONMENT
        INNER JOIN
            MATRIXPROPENVIRONMENT
        ON
            MATRIXPROPENVIRONMENT.EnvironmentId = ENVIRONMENT.id
        WHERE
            MATRIXPROPENVIRONMENT.PropertyId = @PropertyId
        ORDER BY
            Weight

        SET @ENVIFR = SUBSTRING(@ENVIFR, 3, 200)
        RETURN @ENVIFR
END

Result may look like - Coastal, Village, Habitat

Function that returns a seller's ID

CREATE FUNCTION dbo.funcSellIdByPropId
(
@PropertyId AS INT
)
RETURNS INT
AS
    BEGIN
        DECLARE @ID AS INT
        SET @ID = 0
            SELECT
        @ID = MATRIXAGENCYPROPERTY.AgencyId
    FROM
        MATRIXAGENCYPROPERTY
    WHERE
        MATRIXAGENCYPROPERTY.PropertyId = @PropertyId

    IF @ID = 0
        BEGIN
            SELECT
                @ID = PRIVATESELLERS.id
            FROM
                PRIVATESELLERS
            WHERE
                PRIVATESELLERS.PropertyId = @PropertyId
END

RETURN @ID
END

Result may look like - 43

Function that returns an image value from database

CREATE FUNCTION dbo.funcDefaultImage
(
    @PropertyId INT,
    @Position INT
)

RETURNS VARCHAR(50)

AS
BEGIN
    DECLARE @IMAGE VARCHAR(50)
    
    SELECT @IMAGE = T.image
    FROM (
        SELECT
            ROW_NUMBER() OVER (ORDER BY weight DESC) AS [rownum],
            PROPERTYGALLERY.image
        FROM
            PROPERTYGALLERY
        WHERE
            PROPERTYGALLERY.PropertyId = @PropertyId
    ) T
    WHERE
        rownum = @Position

    RETURN @IMAGE
END

Result may look like - property_0001.png

Answer 1

First goal in thinking in SQL (or thinking in sets) is to get rid of IF statements and Loops (or cursors). So for your 2nd function this is much better:

CREATE FUNCTION dbo.funcSellIdByPropId
(
@PropertyId AS INT
)
RETURNS INT
AS
BEGIN
  DECLARE @ID AS INT

  SELECT @ID = COALESCE(MATRIXAGENCYPROPERTY.AgencyId, PRIVATESELLERS.id, 0)
  FROM  MATRIXAGENCYPROPERTY, PRIVATESELLERS
  WHERE MATRIXAGENCYPROPERTY.PropertyId = @PropertyId AND 
        PRIVATESELLERS.PropertyId = @PropertyId

  RETURN @ID
END

This select can now be implemented as a view and then joined to any other select statement instead of calling a function.

---

For the 3rd function you could implement a view like this:

SELECT
  PropertyId 
  ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY weight DESC) AS [imgnum],
  image
FROM PROPERTYGALLERY

Now you can join on this view by PropertyId and imgnum instead of calling a function

---

For your first function take a look at this answer I posted on SO. Using the XML generator like this is considered the fastest way to create a comma separated list in MS SQL. Like the others examples I've given here, this technique could be used in a view to create a list of comma separated values partitioned by PropertyId

---

The key to both of these solutions is thinking in sets. Instead of solving one problem (as the function does) they solve all the problems. Then when you join to these queries you limit to just the data you need.

SQL Engines love this -- their optimizers are designed to make this kind of process work really fast.

---

Detailed Example (see comments)

First create the view:

-- MATRIXAGENCYPROPERTY.AgencyId and PRIVATESELLERS.id are mutually exclusive.  
-- Grab all of them with their PropertyID
CREATE VIEW SellIDByPropertyID(ID, PropertyID) AS
(
  SELECT AgencyId, PropertyID  
  FROM MATRIXAGENCYPROPERTY
  UNION ALL
  SELECT ID, PropertyId
  FROM PRIVATESELLERS
)

Now we can integrate it into your original query:

SELECT 
   ...
   dbo.funcSellIdByPropId(T0.id) as SellerId,  
   ...
FROM T0 
   ...

To

SELECT 
   ...
   S2P.ID,  
   ...
FROM T0 
JOIN SellIDByPropertyID S2P ON T0.id = S2P.PropertyId
   ...

Why this works

Once again the key to understanding is to think in sets. If you just think about a single record a reaction might be the original SP would be faster because if it is a agency property it will only do one select and the IF statement will stop the 2nd select from being run giving a speed advantage.

Side note: often on SQL Engines a select statement will be slower than an if statement so even the above assumption is wrong, but I'll ignore that for now.

However, this goes out the window as soon as we look at the set. If the original query is returning more than one record -- say N records then with the original function call we have the overhead of N function calls + N Selects + N/2 Selects (if half are not agencies). With the view we have just 2 Selects. Clearly O(3.5N) > O(2)