Post reply

Please vote for the "built in Tally Table" function.

  • November 26, 2014 at 11:25 am

    #1761156

    Quick suggestion while we are at it, let's keep it as simple as possible:

    😎

    Function GenerateUserPreferedNumberSequence(

    {Optional StartValue] Default 1.000000 Real number

    [Optional EndValue] Default MAX_INT Real number, single parameter passed is interpreted as StartValue = 1, EndValue = Parameter Value

    [Optional Interval] Default 1.000000 Real number | PRIME | f(x) | MODULO([Real Number]) | PI | Alpha

    [Optional Grouping] Default (NONE) Real number, sorts the output by MODULO of the Grouping value

    [Optional DataType] Default INT Type name literals | String literals | system data type id | R (if there is an R in the month then Random)

    [Optional OutputMode] Default NUMERIC Output Domain, Numeric | f(x) | Alpha | Lotto (Lucky Dip) | Phone (lonely)

    )

  • November 26, 2014 at 7:09 pm

    #1761188

    Jeff Moden (11/25/2014)

    Thanks Koen and Eirikur!

    How about it folks? There a 1.7 million of you out there. Can we get a few more votes on this important subject? Thanks. 🙂

    As most users don't enter to the forums unless they have a question to ask, would anyone consider writing an editorial/article about this? Or include a comment to vote in the Tally table article?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 26, 2014 at 8:08 pm

    #1761190

    Luis Cazares (11/26/2014)

    Jeff Moden (11/25/2014)

    Thanks Koen and Eirikur!

    How about it folks? There a 1.7 million of you out there. Can we get a few more votes on this important subject? Thanks. 🙂

    As most users don't enter to the forums unless they have a question to ask, would anyone consider writing an editorial/article about this? Or include a comment to vote in the Tally table article?

    I was always told that when given a choice, take both. I don't believe that Steve will be around this weekend to take an article for publishing in the near future but I can try to have one ready for when he gets back.

    Great idea, Luis. Thanks.

    --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)
    Intro to Tally Tables and Functions

  • November 26, 2014 at 8:21 pm

    #1761191

    Erland Sommerskog noticed the hits on the CONNECT item and posted on the MVP email "forum". Let's hope MS notices, as well.

    Until I can get Steve to publish a quick article on the subject, tell everyone! Thanks for the help folks!

    --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)
    Intro to Tally Tables and Functions

  • November 27, 2014 at 12:42 am

    #1761206

    Luis Cazares (11/26/2014)

    Jeff Moden (11/25/2014)

    Thanks Koen and Eirikur!

    How about it folks? There a 1.7 million of you out there. Can we get a few more votes on this important subject? Thanks. 🙂

    As most users don't enter to the forums unless they have a question to ask, would anyone consider writing an editorial/article about this? Or include a comment to vote in the Tally table article?

    As far as I can tell, most of them are busy watching live streams of some sport or other 😀

  • November 27, 2014 at 12:47 am

    #1761207

    Luis Cazares (11/26/2014)

    Jeff Moden (11/26/2014)

    Jeff Moden (11/26/2014)

    SQLRNNR (11/26/2014)

    I decided to down vote this one. Everybody should just go to Oracle where they already have the functionality. (devil)

    😛

    BWAAAA-HAAAAA-HAAAA!!!! I thought you were kidding... Note about the vote but about Oracle... you recommended a "DUAL" table??? :-D:-P:hehe: I sure do hope they don't take that part of the suggestion.

    I'm sure there's a table valued function in a RDBMS, but I can't remember which one is it or how is it called. It was mentioned once in a forum post. It wasn't Oracle and certainly wasn't the dual table.

    generate_series in Postgres I think: http://www.postgresql.org/docs/9.1/static/functions-srf.html

    Does int,bigint, and datetime.

  • November 27, 2014 at 5:39 am

    #1761290

    Voted.... in favour of TheTotalTally(tm).

    Dutch Anti-RBAR League

  • November 28, 2014 at 1:41 am

    #1761383

    Voted... a couple of years ago 🙂

    -- Gianluca Sartori

  • November 28, 2014 at 4:51 am

    #1761413

    voted.

    It's now 167. Not growing fast enough.

    Tom

  • November 28, 2014 at 11:30 am

    #1761477

    Voted even if I faced this problem only one time since SQL Server 2008.

  • November 28, 2014 at 11:57 am

    #1761480

    TomThomson (11/28/2014)

    voted.

    It's now 167. Not growing fast enough.

    Agreed. I'll write up an "editorial" and see if we can get it published next week.

    Keep up the great work folks. Let every know about this item and ask them to vote.

    Thanks.

    --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)
    Intro to Tally Tables and Functions

  • November 28, 2014 at 12:21 pm

    #1761481

    +1 voted

  • November 28, 2014 at 5:25 pm

    #1761488

    CELKO (11/28/2014)

    I have mixed feeling on this. It sounds like a schema level table constant with contiguous exact numeric values. Nobody can change a constant. Why would it start at one and not zero? (I have an answer for that, based on sets, but I digress).

    But would that be a CREATE SEQUENCE with extra options? What about a CALENDAR constant table? How do you get INCITS to agree?

    Right now,I just load these two tables ever time I build a new schema.

    Good questions, Joe.

    A part of the problem is that if you have a fixed size table, it could be either too big or too small. Being that disk space is cheap, you might think that "too big" would be the way to go and if disk were all that you needed to worry about, then "too big" would fit the bill and it has for you, me, and thousands of DBAs and Developers for decades.

    In order for such a table to be used, it actually has to live in memory. But, considering the size of the table, even that wouldn't be a problem because (for example) a million row table of integers would only require 4 million bytes plus a little for the B-Tree of the Clustered Index. That's almost nothing for today's machines.

    Then, there's the problem of logical READs. A physical TALLY table can cause a huge number of reads especially if there are a lot of functions created that use the Tally table for every row via a CROSS APPLY or similar. These particular reads (although each READ is 8,192 bytes) aren't bad READs but they do tend to obfuscate bigger problems that are frequently detected by finding the queries with the largest number of reads over time. Even then, you could use Itzik's wonderful cascading CTE method which returns the desired numbers in a totally read-less fashion.

    So why the request for a built-in function to do this?

    One reason is, of course, simple convenience. I'm sure that you've worked in shops where it almost takes an "Act of Congress" to get permission to add such "helper" tables and functions to a database or, worse yet, a shop that insists that such "helper" tables and functions live in every database where they are needed so as to not make the database dependent on a "utility" database. Rebuilding/copying Itzik's wonder cascading CTE into every nook and cranny that needs it, if nothing else, is a pain.

    But the big reason (if they do it right) is one of my favorites... blinding speed. And not just for sequences that start at 0 or 1. If they set it up to take a start and end value, you would no longer have to add offsets to the sequence to get the values that you actually need. That even lends itself well to on-the-fly creation of date sequences.

    In an intangible area of performance, a new function in SQL Server would get a whole lot more attention than what the "Tally" or "Numbers" table currently has even though it's been around since the '60s because everyone wants to use the next new shinny object in SQL Server. It might even stop people from recommending the use of recursive CTEs that count and {gasp} splitters based on WHILE loops.

    If Microsoft does it right (doesn't manage to kill performance like they have with so many things) and if they document it correctly, a lot of people who don't even know how to spell "Tally Table" will start to use it.

    Of course, this would be the tip of the proverbial ice-berg. MS really should build a decent splitter function so people can stop killing themselves with trying to normalize junk inputs and a decent date/time sequence generator.

    --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)
    Intro to Tally Tables and Functions

  • November 28, 2014 at 5:32 pm

    #1761489

    P.S. Personally, I don't care if ANSI, ISO, INCITS, or any other organization agrees or not. Sometimes you have to first build the tool before you try to convince such organizations. If they don't agree, would the new function be portable? Probably not for at least 6 years because the people who make RDBMSs aren't necessarily in tune with those organizations and it might take that long for the organizations to decide to adopt. That's why, except for basic crud, true portability is a myth right now. 🙂

    --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)
    Intro to Tally Tables and Functions

  • December 1, 2014 at 6:04 am

    #1761677

    Jeff Moden (11/28/2014)

    One reason is, of course, simple convenience. I'm sure that you've worked in shops where it almost takes an "Act of Congress" to get permission to add such "helper" tables and functions to a database or, worse yet, a shop that insists that such "helper" tables and functions live in every database where they are needed so as to not make the database dependent on a "utility" database. Rebuilding/copying Itzik's wonder cascading CTE into every nook and cranny that needs it, if nothing else, is a pain.

    Personally, I see no harm at all in having a utility database. I would argue that it helps to centralize common code and make it available to every login. Maintenance and performance improvements are done in one location instead of hundreds or thousands. I have one on every instance I manage and all logins have permission to use and view definitions on everything. Since there's not much actual data in it and mostly functions, there's no security problem. In fact, I hope people do look at the source code. Any time someone learns from something done well, it only helps to make them better.

    Jeff Moden (11/28/2014)

    Of course, this would be the tip of the proverbial ice-berg. MS really should build a decent splitter function so people can stop killing themselves with trying to normalize junk inputs and a decent date/time sequence generator.

    Oh my...let the performance testing begin.

    BTW Jeff, we already have a decent date/time sequence generator. It may not do everything, but what it does, it does well. 😉

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

Viewing 15 posts - 16 through 30 (of 38 total)

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