I agree with Seth and would like to point out a couple of other forms of RBAR to avoid...
CTE's are a wonderful tool... until you write a recursive CTE which is slower than a cursor or a while loop. Just because it doesn't have an explicitly defined cursor or while loop, it doesn't necessarily mean that it's "set based".
A good example of "hidden RBAR" comes in the form of what are known as "triangular joins". These insidious monsters of code are actually "RBAR on sterioids" and can be thousands of times worse than any form of cursor or while loop that you can possibly imagine. What makes them insidious is two things...
1. They look like set based code.
2. They seem to not only get the job done with small row counts, but they seem to work very fast with small row counts. The problem is, they get exponentially worse as the row count increases much like a "Cartesian Product" (otherwise known as a CROSS JOIN) does.
For a full explanation on what to look for, please see the following article... generally speaking, inequalities in a JOIN ON or WHERE clause can be (not always) deadly to the performance of a system...
[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]
As you've seen in this very thread, lot's of people believe that writing set based code is difficult and that the code gets a lot longer. Patently, not the case. The key to writing set based code is simply to make a pardigm shift... stop thinking about what you want to do to each row and start thinking about what you want to do to an entire column in a set of rows.
Once you make that shift in thinking, start practicing it... you cannot play the piano well if you never touch the piano. Instead of doing like many do and giving up on a set based solution simply because you can't think of one, struggle a bit and find one. Train your brain to start thinking in terms of how to use a database instead of a GUI.
To put my money where my mouth is on the length and simplicity of set based code, let's address the lowly "split" function... Here's one way of doing it with a While Loop in a function...
--Creates an 'InLine' Table Valued Function (TVF)
CREATE FUNCTION dbo.Split
( @Delimiter varchar(5),
RETURNS @TableOfValues table
( RowID smallint IDENTITY(1,1),
DECLARE @LenString int
WHILE len( @List ) > 0
SELECT @LenString =
(CASE charindex( @Delimiter, @List )
WHEN 0 THEN len( @List )
ELSE ( charindex( @Delimiter, @List ) -1 )
INSERT INTO @TableOfValues
SELECT substring( @List, 1, @LenString )
SELECT @List =
(CASE ( len( @List ) - @LenString )
WHEN 0 THEN ''
ELSE right( @List, len( @List ) - @LenString - 1 )
--demo the split
FROM dbo.Split( '-', '111111-0000-9999' ) AS s
And, they call that "simple"... 😉
Now, consider doing the same thing using a set based method that employs a "helper table" known as the "Tally" or "Numbers" table...
--===== Creates an 'InLine' Table Valued Function (TVF) that will use
-- up to a 5 character delimiter
CREATE FUNCTION dbo.TSplit
RETURNS @TableOfValues TABLE
RowID SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
--===== Now, do the split using the high speed set based method made
-- possible by a Tally table. The single character delimiter of
-- CHAR(1) replaces the original (up to) 5 character delimiter.
INSERT INTO @TableOfValues
FROM dbo.Tally t,
(--=== Replace the original delimiter with single delimiter
SELECT CHAR(1) + REPLACE(@List,@Delimiter,CHAR(1)) + CHAR(1) AS List
WHERE t.N < LEN(L.List)
ORDER BY t.N
--===== Demo the split
FROM dbo.TSplit( '-', '111111-0000-9999' ) AS s
(Side bar: There's a way of using FOR XML to split things in a set based fashion that doesn't use a Tally table that's also nasty fast... it'll be in the final two links I post on this thread).
Now, as you look at that little slice of computational heaven, you'll make the realization that there's another part to writing good, high performance set based code other than just thinking in columns... you actually have to know what SQL Server does. 😉 The very first thing I did when I first started learning SQL (or any language, for that matter) is to study the "meat" of the language and that "meat" is in the form of what the functions do. You don't have to memorize the exact syntax of every function (I have to lookup how STUFF works, every time), but at least know what can be done so that when something comes up, you can figure out how to do things with a function or two instead of having to resort to RBAR. In the databases for the company I'm currently working for, I actually found a user defined function to calculate MOD (Modulo) because the dummies that wrote the code didn't know that there's a MOD operator built into SQL Server.
So... I simply wouldn't spend to much time trying to learn about RBAR... rather, spend the time practicing how to avoid it and spend the time to learn about SQL Server instead of just becoming another "SQL Drone Clone". Spend some time answering questions on this forum and read all the articles that come out... then, try to figure out a better way.
For more information on the "Tally" table I used in the code above, please see the following article on how it's made and how it works.
[font="Arial Black"]The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/font][/url]
For more on how to do "splits" including the FOR XML method, please see the following...
[font="Arial Black"]Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays[/font][/url]
Don't forget to "practice". 😉
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)