The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Question

The "Numbers" or "Tally" Table: What it is and how it replaces a loop

Viewing 15 posts - 106 through 120 (of 511 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Wayne West (5/8/2008)
The ISBN is 0-201-55710-X and you can get it on Half.com for $0.75! (http://product.half.ebay.com/_W0QQcpidZ2437565QQprZ1144253)
It was an excellent book for its time, needless to say it's rather dated. Yet I still have a copy on my bookshelf at work.

Perfect! Thanks, Wayne!

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Gary Noter (5/8/2008)
Great article. I already implemented it into an existing solution that was doing 250000+ loops. My boss asked me if I had notice some reports were a bit faster. "No, not recently. I'll keep a tally on it, though.":cool:;):D

Heh... Now, THAT's funny! I laughed right out loud! Thanks for the great feedback, Gary! 😀

--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)

creeds-494584 Newbie Points: 5 More actions · Answer 3

You name it and we're running it. We've got about 60 servers. Running various versions of MSSQL.

Thanks,

Creeds

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Hugo Kornelis (5/8/2008)
.

Correct me if I'm wrong but IMO if result changes because of a changed index, your query symantics do not reflect what you want 100%!

In the example you've provided, that would mean you'd have to detirmine the actual "T2.col" value you need.

"Pick one" always bites you in the back !

In the example you provided that would mean you'd have to decide to determine if you want to use the min or max Col.

UPDATE T1

SET Col = T2m.Col

FROM T1

INNER JOIN (Select T2.ID1, max(Col) as Col

from T2

group by T2.ID1

) T2m

ON T2m.ID1 = T1.ID1;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

ALZDBA (5/9/2008)
Hugo Kornelis (5/8/2008)
.
Correct me if I'm wrong but IMO if result changes because of a changed index, your query symantics do not reflect what you want 100%!

Hi ALZDBA,

Exactly. And that's exactly the point I was trying to make.

(For context, please refer to my blog -link to the actual post is below- This discussion is actually off topic here; it started because Jeff read my blog and then challlenged me on a statement that I had formulated ambiguously. It is not related to Jeff's article on a Numbers (Tally) table. It is related to my blog posts where I explain how using UPDATE FROM introduces the risk of getting hard to detect unexpected results because of minor errors. And that's all I'll say about it in this discussion).

Now, back to your regularly scheduled discussion about Jeff's article... 🙂

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

Jeff Moden (5/8/2008)

Hey! What kind of bike is in your avatar?

It's a 1995 Honda NTV650. (The little brother of the Pan European)

It's current evolution is known as "Deauville".

Quit fault tollerant, easy to manage, lovely to ride, low maintenance and still you can sniff a bit of sports curve techniques if you want to.

With the current speed limits, it is way to hard to have a 500Hp bike.

And overhere we also have many speed trap cameras. They send you a lovely picture and a huge bill. :hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Hugo Kornelis (5/9/2008)
ALZDBA (5/9/2008)
Hugo Kornelis (5/8/2008)
.
Correct me if I'm wrong but IMO if result changes because of a changed index, your query symantics do not reflect what you want 100%!
Hi ALZDBA,
Exactly. And that's exactly the point I was trying to make.
(For context, please refer to my blog ...)

Wil do 🙂

Now, back to your regularly scheduled discussion about Jeff's article... 🙂

Nice indeed.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

srienstr SSCrazy Points: 2435 More actions · Answer 8

I'm trying, but can't figure out how to apply this to cleaning user input. I have a file I get monthly with member information that has some rows which contain excessive spaces. (e.g. 'SMITH JR', '123 MAIN ST', etc.)

I currently use the following code:

while exists(select top 1 '1' from hshah.dbo.currentmembership where [memblname] like '% %'

or [membfname] like '% %'

or [membaddr1] like '% %'

or [membaddr2] like '% %'

or [membcity] like '% %')

update hshah.dbo.currentmembership

set [memblname]=replace([memblname],' ',' ')

, [membfname]=replace([membfname],' ',' ')

, [membaddr1]=replace([membaddr1],' ',' ')

, [membaddr2]=replace([membaddr2],' ',' ')

, [membcity]=replace([membcity],' ',' ')

where [memblname] like '% %'

or [membfname] like '% %'

or [membaddr1] like '% %'

or [membaddr2] like '% %'

or [membcity] like '% %'

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

RyanRandall SSChampion Points: 13623 More actions · Answer 9

srienstr (5/9/2008)
I'm trying, but can't figure out how to apply this to cleaning user input. I have a file I get monthly with member information that has some rows which contain excessive spaces. (e.g. 'SMITH JR', '123 MAIN ST', etc.)
I currently use the following code:
while exists(select top 1 '1' from hshah.dbo.currentmembership where [memblname] like '% %'
or [membfname] like '% %'
or [membaddr1] like '% %'
or [membaddr2] like '% %'
or [membcity] like '% %')
update hshah.dbo.currentmembership
set [memblname]=replace([memblname],' ',' ')
, [membfname]=replace([membfname],' ',' ')
, [membaddr1]=replace([membaddr1],' ',' ')
, [membaddr2]=replace([membaddr2],' ',' ')
, [membcity]=replace([membcity],' ',' ')
where [memblname] like '% %'
or [membfname] like '% %'
or [membaddr1] like '% %'
or [membaddr2] like '% %'
or [membcity] like '% %'

Use this kind of thing for that (no need for loop or tally)...

update hshah.dbo.currentmembership set

[memblname] = replace(replace(replace([memblname], ' ', ' |'), '| ', ''), '|', ''),

[membfname] = replace(replace(replace([membfname], ' ', ' |'), '| ', ''), '|', '')

(where | is a character which won't/can't be entered - use another if you need to)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Jon Russell Hall of Fame Points: 3048 More actions · Answer 10

Great article, Jeff. This is must-read for all database developers. I have inherited a few databases created by application developers who think in terms of loops, rather than sets. These curors are everywhere and now have to go clean all that up. This wil help. Thank you.

srienstr SSCrazy Points: 2435 More actions · Answer 11

RyanRandall (5/9/2008)
Use this kind of thing for that (no need for loop or tally)...
update hshah.dbo.currentmembership set
[memblname] = replace(replace(replace([memblname], ' ', ' |'), '| ', ''), '|', ''),
[membfname] = replace(replace(replace([membfname], ' ', ' |'), '| ', ''), '|', '')
(where | is a character which won't/can't be entered - use another if you need to)

Ah, the thought of replacing with an intermediate character hadn't occurred to me. Thank you.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

ALZDBA (5/9/2008)

Jeff Moden (5/8/2008)
Hey! What kind of bike is in your avatar?
It's a 1995 Honda NTV650. (The little brother of the Pan European)
It's current evolution is known as "Deauville".
Quit fault tollerant, easy to manage, lovely to ride, low maintenance and still you can sniff a bit of sports curve techniques if you want to.
With the current speed limits, it is way to hard to have a 500Hp bike.
And overhere we also have many speed trap cameras. They send you a lovely picture and a huge bill. :hehe:

Mine's a nice little ol' man's bike... Honda VTX 1300 S (retro with spoked wheels) and something I love dearly... shaft drive. Took me a long time to riding in a "chair-like" position and the foot-boards are so low to the ground that even on what I think are semi-gentle turns, I sometime scrape them on the ground... scares the heck out of me everytime.

--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)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Jon Russell (5/9/2008)
Great article, Jeff. This is must-read for all database developers. I have inherited a few databases created by application developers who think in terms of loops, rather than sets. These curors are everywhere and now have to go clean all that up. This wil help. Thank you.

Thanks, Jon. If you get a chance, post some of the "before'n'after"... it would help a lot of other folks do the same thing you're trying to do.

--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)

creeds-494584 Newbie Points: 5 More actions · Answer 14

[/quote]

Thanks for the feedback and the code you posted. What version of SQL Server are you using? I might be able to turn a treat for you...[/quote]

Jeff,

We're running the whole gamit on versions. We've got a bunch of servers and they're running everything from 6.5 to 2008. Any words of wisdom or assistance in any way would be greatly appreciated.

Thanks,

CReeds

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 15

Jeff Moden (5/9/2008)
Mine's a nice little ol' man's bike... Honda VTX 1300 S (retro with spoked wheels) and something I love dearly... shaft drive. Took me a long time to riding in a "chair-like" position and the foot-boards are so low to the ground that even on what I think are semi-gentle turns, I sometime scrape them on the ground... scares the heck out of me everytime.

So that'll be about to only RBAR you like:

Ride Bike And Revive :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me