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

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

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.

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)

• I have never used views. I know this is very hand holding but how would I use the 1st example you've shown as a view as that to me still looks like a SQL function that'd I'd call in the same way as I already am. Cheers Apr 2 '14 at 20:50
• @TheAngrySaxon - You are going to owe me some unicoins - is MATRIXAGENCYPROPERTY.PropertyID or PRIVATESELLERS.PropertyID a superset of the other or are they mutually exclusive Apr 2 '14 at 20:53
• I think unicoins was an April fools from stackexchange, was it not? They are mutually exclusive Apr 3 '14 at 11:14
• @TheAngrySaxon - An April fools joke, or no, they are still the only "official" virtual currency of the stack exchange network. I take what I can get. :) I'll post an example after I put out the fire here. Apr 3 '14 at 14:41