Looping and scalar value

Question

Post reply

Looping and scalar value

kuopaz

Default port

Points: 1449
More actions
March 19, 2016 at 11:42 am

#305835

Hi
No doubt this is standard behaviour I should already know about, but I'm a little unclear about it. The example below is contrived, and I got here by thinking how I could rewrite a string handling scalar function that uses a while loop, to one that uses a tally table to do the looping.
declare @Num int
--#1
set @Num = 0
select
@Num = sum(N)
from
Tally
where
N <= 10
select @Num as Result
--#2
set @Num = 0
select
@Num = N + @Num
from
Tally
where
N <= 10
select @Num as Result
Both give the answer 55. #2 is doing the SUM in #1 'automatically', so it's looping row-by-row and accumulating. But it's not obvious to me that it would do this just by looking at the SQL statement.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

I would start by reading this article: The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]

kuopaz Default port Points: 1449 More actions · Answer 2

Thanks, I've used that great article in the past. Most of the examples I've seen for using a tally table return a rowset, such as a CSV splitter. What I'd not fully realised is that you can use it to return a scalar value. That you can write @Num = @Num + N and it will cycle through the tally table as a counter. So analogous to looping in a programming language.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

The main difference between those two queries is that the first (#1) uses a Stream Aggregation Operator to sum the values while the second (#2) uses an Arithmetic Addition and a Comput Scalar Operators, otherwise the queries are identical, that is range scanning the Tally from the start to the predicate value.

There is no looping as such in the execution plans.

😎

kuopaz Default port Points: 1449 More actions · Answer 4

I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

kuopaz (3/21/2016)
I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.

That's correct that the correct order of N can and often is very important, if it is then one tries to force the order.

😎

drew.allen SSC Guru Points: 76996 More actions · Answer 6

Eirikur Eiriksson (3/21/2016)
kuopaz (3/21/2016)
I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.
That's correct that the correct order of N can and often is very important, if it is then one tries to force the order.
😎

I think that's more of an illusion than reality. I think that the correct order of N is often irrelevant. For instance, the string splitter appears to require the correct order of N to work, when, in actuality, the string can be split in any order and each part is independent of the other parts.

I think that the only time that the correct order is required is for windowed functions with a mandatory order clause.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 7

drew.allen (3/21/2016)
Eirikur Eiriksson (3/21/2016)
kuopaz (3/21/2016)
I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.
That's correct that the correct order of N can and often is very important, if it is then one tries to force the order.
😎
I think that's more of an illusion than reality. I think that the correct order of N is often irrelevant. For instance, the string splitter appears to require the correct order of N to work, when, in actuality, the string can be split in any order and each part is independent of the other parts.
I think that the only time that the correct order is required is for windowed functions with a mandatory order clause.
Drew

Interested how one would tackle Gaps and Islands problem with a random order of N appearance:-D

Or even properly parse RFC-4180 compliant CSV

😎

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

drew.allen (3/21/2016)
Eirikur Eiriksson (3/21/2016)
kuopaz (3/21/2016)
I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.
That's correct that the correct order of N can and often is very important, if it is then one tries to force the order.
😎
I think that's more of an illusion than reality. I think that the correct order of N is often irrelevant. For instance, the string splitter appears to require the correct order of N to work, when, in actuality, the string can be split in any order and each part is independent of the other parts.
I think that the only time that the correct order is required is for windowed functions with a mandatory order clause.
Drew

I think that with respect to relational theory order is not important but in reality there are many examples where correct order of N is important. E.g. when filling in gaps in a sequence then finding the "first gap".

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

drew.allen (3/21/2016)
Eirikur Eiriksson (3/21/2016)
kuopaz (3/21/2016)
I see, thanks. As it's scanning the tally table row-by-row from start to the predicate value, the order of N may be important in some cases (not this example), such as when using it for string handling.
That's correct that the correct order of N can and often is very important, if it is then one tries to force the order.
😎
I think that's more of an illusion than reality. I think that the correct order of N is often irrelevant. For instance, the string splitter appears to require the correct order of N to work, when, in actuality, the string can be split in any order and each part is independent of the other parts.
I think that the only time that the correct order is required is for windowed functions with a mandatory order clause.
Drew

There are, indeed, a great many places where knowing the order isn't only NOT necessary, but would actually slow things down. But, there are a great many places where the order of things is critical and not just because of the required ORDER BY clause in the OVER clause of some functions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76996 More actions · Answer 10

I agree that there are cases where the order is important, but my point was that the number of cases is far smaller than most people think. Running aggregates (usually totals) are certainly one case (and gaps and islands fit into running aggregates under a very broad definition of running aggregates), but I can't think of any other case where they are important. I think it's much more instructive to talk about specific cases where the order is thought to be important rather than talk in vague generalizations. That is why I specifically mentioned the string splitter where the order is not important despite looking like it is on the surface.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kuopaz Default port Points: 1449 More actions · Answer 11

Ended up with this, that is closer to my actual requirement. Previously used a WHILE loop and counter.

declare @List varchar(max) = '123456789'

declare @ChunkSize int = 3

declare @SepChar char(1) = ','

declare @Result varchar(max) = ''

select

@Result = @Result + substring(@List, N, @ChunkSize) + @SepChar

from

Tally

where

N >= 1 and N <= len(@List)

and (N - 1) % @ChunkSize = 0

order by

N

select left(@Result, len(@Result) - 1) as Result

Sergiy SSC Guru Points: 110208 More actions · Answer 12

kuopaz (3/22/2016)
Ended up with this, that is closer to my actual requirement. Previously used a WHILE loop and counter.
declare @List varchar(max) = '123456789'
declare @ChunkSize int = 3
declare @SepChar char(1) = ','
declare @Result varchar(max) = ''
select
@Result = @Result + substring(@List, N, @ChunkSize) + @SepChar
from
Tally
where
N >= 1 and N <= len(@List)
and (N - 1) % @ChunkSize = 0
order by
N
select left(@Result, len(@Result) - 1) as Result

This would be shorter by 2 operations:

declare @List varchar(max) = '123456789'

declare @ChunkSize int = 3

declare @SepChar char(1) = ','

declare @Result varchar(max) -- no assignment here

select

@Result = ISNULL(@Result+ @SepChar, '') + substring(@List, N, @ChunkSize)

from

Tally

where

N >= 1 and N <= len(@List)

and (N - 1) % @ChunkSize = 0

order by

N

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Or - eliminate the variable @Result completely:

declare @List varchar(max) = '123456789'

declare @ChunkSize int = 3

declare @SepChar char(1) = ','

SELECT STUFF((

SELECT @SepChar + SUBSTRING(@List, N, @ChunkSize)

FROM dbo.Tally t

where

N >= 1 and N <= len(@List)

and (N - 1) % @ChunkSize = 0

order by

N

FOR XML PATH ('')

), 1,1,'')

_____________
Code for TallyGenerator

kuopaz Default port Points: 1449 More actions · Answer 14

Thanks. Will use the ISNULL to eliminate the final operation. Don't really see the need to go any further as it will probably be wrapped up in a scalar-valued function.