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

  • Alan Burstein

    SSC Guru

    Points: 61006

    []

    Luis Cazares - Thursday, March 7, 2019 6:32 PM

    Jeff Moden - Thursday, March 7, 2019 4:12 PM

    Heh... not quite right.  The Tally Table is still faster than the table constructors.  It's not by much and it's less noticeable for most folks due to the speed of today's machines but, when you're willing to trade reads for speed, a physical Tally Table is difficult to beat.

    And, no... I have no bad words for Itzik's method (which I refer to as cCTEs or Cascading CTEs).  Done correctly, it's blazing fast and produces no reads.

    As with all else in SQL Server, "It Depends". 😀

    I believe that Drew meant that you shouldn't read physical tables to create a tally table, not that you shouldn't use a physical tally table. I'm not sure if someone has actually tested the performance of an In-Memory table. I haven't had the opportunity to work with those on real systems.

    I have. No faster than a CTE tally in my experience.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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: 993402

    []

    Alan.B - Thursday, March 7, 2019 7:50 PM

    Luis Cazares - Thursday, March 7, 2019 6:32 PM

    Jeff Moden - Thursday, March 7, 2019 4:12 PM

    Heh... not quite right.  The Tally Table is still faster than the table constructors.  It's not by much and it's less noticeable for most folks due to the speed of today's machines but, when you're willing to trade reads for speed, a physical Tally Table is difficult to beat.

    And, no... I have no bad words for Itzik's method (which I refer to as cCTEs or Cascading CTEs).  Done correctly, it's blazing fast and produces no reads.

    As with all else in SQL Server, "It Depends". 😀

    I believe that Drew meant that you shouldn't read physical tables to create a tally table, not that you shouldn't use a physical tally table. I'm not sure if someone has actually tested the performance of an In-Memory table. I haven't had the opportunity to work with those on real systems.

    I have. No faster than a CTE tally in my experience.

    Like I said, it's not by much but, as always, "It Depends".

    --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.
    "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. 😉

    Helpful Links:
    How to post code problems

  • Alan Burstein

    SSC Guru

    Points: 61006

    []

    Jeff Moden - Friday, March 8, 2019 9:33 AM

    Alan.B - Thursday, March 7, 2019 7:50 PM

    Luis Cazares - Thursday, March 7, 2019 6:32 PM

    Jeff Moden - Thursday, March 7, 2019 4:12 PM

    Heh... not quite right.  The Tally Table is still faster than the table constructors.  It's not by much and it's less noticeable for most folks due to the speed of today's machines but, when you're willing to trade reads for speed, a physical Tally Table is difficult to beat.

    And, no... I have no bad words for Itzik's method (which I refer to as cCTEs or Cascading CTEs).  Done correctly, it's blazing fast and produces no reads.

    As with all else in SQL Server, "It Depends". 😀

    I believe that Drew meant that you shouldn't read physical tables to create a tally table, not that you shouldn't use a physical tally table. I'm not sure if someone has actually tested the performance of an In-Memory table. I haven't had the opportunity to work with those on real systems.

    I have. No faster than a CTE tally in my experience.

    Like I said, it's not by much but, as always, "It Depends".

    I could be mistaken but I thought Luis was talking the performance of an in-memory tally table vs a traditional tally table.
    I was looking for this yesterday and just found it: https://www.sqlservercentral.com/Forums/1101315/Tally-OH-An-Improved-SQL-8K-CSV-Splitter-Function?PageIndex=36 It would appear that, in this case, the memory optimized table was faster but, for me, I have not had the same level of success. In my personal experience I have never seen a performance improvement switching from a CTE tally table to a memory optimized tally table.

    That said, I have never had a primary key on mine; here's the DDL for the one I use:
    CREATE TABLE dbo.eTally
    (
      N INT NOT NULL,
      UNIQUE NONCLUSTERED (N ASC)
    )
    WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY);

    The one Magoo used in his testing had a PK (nonclustered). 

    On a separate note - here's a great example of "there being no spoon or default ORDER BY in SQL Server":

    SELECT TOP (10) t.N
    FROM  dbo.eTally AS t

    Returns: 998753, 998754.....998762

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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: 993402

    Ah… sorry, Alan.  I flat out missed (even though bolded and underlined :blush:) that folks were talking about “memory optimized” tables.  I can’t speak to that because I don’t use them… at least not yet.  I can say that it’s my understanding that “memory optimized” tables work the best for non-unique data and would speculate that there’d be little difference made by using them for a Tally Table.  Again, I don’t actually know because I’ve not tested it and probably won’t in the near future.

    --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.
    "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. 😉

    Helpful Links:
    How to post code problems

Viewing 4 posts - 466 through 469 (of 469 total)

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