The Dynamic Tally or Numbers Table

Question

Post reply

The Dynamic Tally or Numbers Table

Viewing 15 posts - 91 through 105 (of 159 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Is there a way on this forum to increase the number of posts shown per page?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

TheSQLGuru (10/2/2009)
Is there a way on this forum to increase the number of posts shown per page?

Yup its in the forum settings section of the forum control panel..

http://www.sqlservercentral.com/Forums/EditForumSettings.aspx

It's in the Topic display options section...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Click on Control Panel. Scroll down to options, click on Forum Settings. Scroll down and you will find where you can change the number of posts per page. The max is 50, which is what I have mine set to.

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

These topic options were described when SSC switched/upgraded the forum software back in the days. 😉

On the other hand, if you want to print or copy the full topic at once, you can do that using the "topic options" (right hand corner) and choose print topic.

It will open a new page (other layout) and launch a print dialog which you can cancel 😎

Copy/Past is still one of your best friends :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

the sqlist SSCrazy Points: 2398 More actions · Answer 5

Paul White (10/1/2009)
go
set statistics time on
declare @bb int;
select @bb = N from [dbo].[ufn_Tally2] (1, 10000000, 1)
set statistics time off
go
set statistics time on
declare @bb int;
select top (10000000)
@bb = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
from master.sys.all_columns C1,
master.sys.all_columns C2,
master.sys.all_columns C3;
set statistics time off
go
The performance difference becomes more marked with higher row counts. Back later.
Paul

What is the purpose of set statistics time on inside the function?

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

RBarryYoung (9/23/2009)
Jeff Moden (9/23/2009)
RBarryYoung (9/23/2009)
Great article, Lynn! I might finally be ready to stop cross-joining master..syscolumns. 🙂
I already quit... heh... I use Master.sys.All_Columns now. 😛
Heh. I try to stick to only either syscolumns or system_columns because their number and content is pretty reliably consistent within a version.

That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on a full install. Also, what is "system_columns"? I get an invalid object error when I try to do a simple count of that from Master.

--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: 1004340 More actions · Answer 7

the sqlist (10/2/2009)
What is the purpose of set statistics time on inside the function?

Displays CPU consumption and Duration.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Jeff Moden (10/2/2009)
RBarryYoung (9/23/2009)
Jeff Moden (9/23/2009)
RBarryYoung (9/23/2009)
Great article, Lynn! I might finally be ready to stop cross-joining master..syscolumns. 🙂
I already quit... heh... I use Master.sys.All_Columns now. 😛
Heh. I try to stick to only either syscolumns or system_columns because their number and content is pretty reliably consistent within a version.
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on a full install. Also, what is "system_columns"? I get an invalid object error when I try to do a simple count of that from Master.

Try it like this:

select * from master.sys.system_columns

Apparently you have to include the ".sys." for the new catalogs.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Paul White (10/1/2009)

Jeff Moden (9/22/2009)
Actually, it doesn't come close. To generate 1 to 10,000,000 numbers, Lynn's method pretty much blows the doors off that type of cross join (ie: more than a pair of tables cross joined) when inserting into a temp table...
Are you sure, Jeff? The unnecessary sort in the OVER clause is the main cost. I only have 2 minutes for a quick post, but do try this:
go
set statistics time on
declare @bb int;
select @bb = N from [dbo].[ufn_Tally2] (1, 10000000, 1)
set statistics time off
go
set statistics time on
declare @bb int;
select top (10000000)
@bb = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
from master.sys.all_columns C1,
master.sys.all_columns C2,
master.sys.all_columns C3;
set statistics time off
go
The performance difference becomes more marked with higher row counts. Back later.
Paul
edit: I hate the way this site only shows one page when you come to it from the email. So - there are 3 pages of discussion 😀
Well never mind, I'll leave this here anyway.

Nope... not sure anymore. The machine at work favored Lynn's method. My machine at home favors the original method you posted above. Unfortunately, I've changed jobs and the machine "at work" is no longer available to me to double check.

Just one thing to be careful of, Paul. I did a Billion row test in the past using the same method as above. It caused the log file to grow to 40 gig. I recommend a single cross join which will give a bit more than 16 million rows quite nicely. For anything that requires more than the square of the rowcount in a given table, I do recommend one of the various permutations of Lynn's code shown in the article and the discussions. I used Itzek's base 2 code and it caused no log growth on the Billion row test.

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 10

Jeff Moden (10/2/2009)
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on a full install. Also, what is "system_columns"? I get an invalid object error when I try to do a simple count of that from Master.

For the same reason tally stuff should be encapsulated within a function whenever possible. Modifying the function to use another source of records is then all that is needed if the existing source becomes unavailable.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

peter-757102 (10/2/2009)
Jeff Moden (10/2/2009)
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on a full install. Also, what is "system_columns"? I get an invalid object error when I try to do a simple count of that from Master.
For the same reason tally stuff should be encapsulated within a function whenever possible. Modifying the function to use another source of records is then all that is needed if the existing source becomes unavailable.

Or a passthrough view. Or, better yet, a real Tally table that you can cross-join if you need something bigger.

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

the sqlist SSCrazy Points: 2398 More actions · Answer 12

Jeff Moden (10/2/2009)
the sqlist (10/2/2009)
What is the purpose of set statistics time on inside the function?
Displays CPU consumption and Duration.

Yes, I am perfectly aware of that. My question was why INSIDE the function.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

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

RBarryYoung (10/2/2009)
Apparently you have to include the ".sys." for the new catalogs.

Agh... of course. Thanks, Barry. On a new installation, sys.system_columns comes in at under 4k so I'll likely stick with sys.All_Columns.

--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: 1004340 More actions · Answer 14

the sqlist (10/2/2009)
Jeff Moden (10/2/2009)
the sqlist (10/2/2009)
What is the purpose of set statistics time on inside the function?
Displays CPU consumption and Duration.
Yes, I am perfectly aware of that. My question was why INSIDE the function.

I didn't see one inside the function on the code that you gave. Pretty sure it wouldn't even work inside a function.

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

peter-757102 SSCertifiable Points: 6877 More actions · Answer 15

Jeff Moden (10/2/2009)
peter-757102 (10/2/2009)
Jeff Moden (10/2/2009)
That's why I shifted to All_Columns for future code. Rumor has it that syscolumns is going away and Master.sys.All_Columns always has at least 4k rows on a full install. Also, what is "system_columns"? I get an invalid object error when I try to do a simple count of that from Master.
For the same reason tally stuff should be encapsulated within a function whenever possible. Modifying the function to use another source of records is then all that is needed if the existing source becomes unavailable.
Or a passthrough view. Or, better yet, a real Tally table that you can cross-join if you need something bigger.

Not sure what you are pointing out here Jeff. My point was that when you wrap whatever record source in an inline function you can do all that as the function shell can be optimised away in most code. Even with a dedicated numbers table I think a function should be used to warp access to that table.