In case you forgot how to count



  • A coleague just sent me this to me via skype...

    I couldn't believe my eyes, so I checked in case it was a joke. Turns out, that table really is there in the database, it has "Number" as the only row and apparently, the application cannot function without the table...

    And no, this is not an applicaton to implement Peano's axioms and see if you can generate all the natural numbers.


  • kills Dumbledore

    There are a handful of reasons this might make sense, but they're generally a bit :wtf:y. I've had a couple of times when one might have been useful, but I would simulate it with selecting an indexed column from a large table, cross joined with itself if I needed more rows

    Basically, it can be faster than a loop. See http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/07/bad-habits-to-kick-using-a-loop-to-populate-a-table.aspx


  • Notification Spam Recipient

    Google "Tally table". It is a very useful table to avoid loops (as mentioned), as loops in sql is DEAD slow. We use it for a variety of cases, i.e. generate a date table on the fly, splitting strings with a defined separator, etc.



  • @Jaloopa said:

    There are a handful of reasons this might make sense, but they're generally a bit :wtf:y.

    I assumed it was there for some reason (actually that's what I would have done if the application was less broken). There are better ways to doing this, however. TRWTF is not using SQLServer's facilities.

    @Vault_Dweller said:

    Google "Tally table". It is a very useful table to avoid loops (as mentioned), as loops in sql is DEAD slow. We use it for a variety of cases, i.e. generate a date table on the fly, splitting strings with a defined separator, etc.

    I'm not saying it didn't have its uses once. Still, as I supposed the world has come up with better solutions as in ones that save people from having to store - and maintain - an 8 million row table.



  • SELECT
      dt = CONVERT(datetime, FLOOR(CONVERT(float, DATEADD(dd, 8 - Number,  GETDATE())))),
      BackupCount = (SELECT COUNT (*)
                     FROM msdb.dbo.backupset
                     WHERE database_name = 'master'
                       AND backup_start_date
                             BETWEEN
                               CONVERT(datetime, FLOOR(CONVERT(float, DATEADD(dd, 32 - Number,  GETDATE()))))
                                 AND
                               CONVERT(datetime, FLOOR(CONVERT(float, DATEADD(dd, 31 - Number,  GETDATE())))))
    FROM
      master.dbo.Numbers
    WHERE
      Number BETWEEN 1 AND 31
    ORDER BY Number
    

    This give you a quick listing of whether a backup was performed for the master database on each of the past 31 days. This is a pain in the balls to do without a tally table.



  • ...and I just found out this table is missing the number 91. Guess that renders it unusable.

    @Jaime said:

    This is a pain in the balls to do without a tally table.

    Point taken. It isn't strictly necessary to generate it by hand, however.



  • Shit, I thought that with advent od PostgreSQL's generale_series() function other databases have caught up...


  • kills Dumbledore

    @ronin said:

    and I just found out this table is missing the number 91

    And that's where we go from "not necessarily the best way to do it" to "WHAT WERE YOU THINKING!?"


  • area_deu

    I usually have a table with all dates from 1900-01-01 to 2199-12-31 together with their weekdays and calendar weeks. Because SQL Server can't do ISO calendar weeks and datepart(dw) depends on the user language. Which is really stupid if you want to exclude weekends for all users regardless of their language. (Yes, I know, SET DATEFIRST. Until the next developer forgets to paste that in their new function.)

    Until ROW_NUMBER() came along, tally tables where also quite common in our applications.


  • Discourse touched me in a no-no place

    I love working with a language that doesn't have need for such fuckery as tally tables.

    Instead, we get an entirely different set of fuckery, such as "a GUI model that's essentially frozen at Windows 95", "no support for multithreading", and, my most recent favorite, "no support for generic .Net methods." I have a C# library I could completely replace with Progress...except for a single call to a static generic factory method. The library works but its nature offends me: it's got 2-3 support methods, and 23 or so different "Get[table]ById" and "Get[table]ListById" methods which are all basically 10 lines of boilerplate and one or maybe two unique lines. Not enough RoI to be worth rewriting, especially since I need that one damn generic.


  • kills Dumbledore

    Could you wrap it and have a replacement for the generic take an object and do some type checking/casting?


  • Discourse touched me in a no-no place

    @Jaloopa said:

    Could you wrap it and have a replacement for the generic take an object and do some type checking/casting?

    The sensible alternative for the current setup would be to strip the .Net DLL down to a single helper/init function and a single function that does the one generic method call, and replace all the rest of the code with Progress code. Not worth doing, though, compared to everything else on my plate.



  • That's totally lame. My table only has 0 thru 9 in a column named "DIG". If you need more rows:

       select a.dig*1000+ b.dig*100 + c.dig*10 + d.dig as numb
       from nums a, nums b, nums c, nums d
    

    [spoiler]Qualification: only used in ad hoc queries...not in applications.[/spoiler]



  • This one time, I was trying to get a particular report that joins 3 tables to not skip dates when a record was missing from one of the tables. A row with no data in some field => "oh, let me go to that table and enter the information for that date"; a missing row => "hey @anotherusername? can you figure out why this report isn't showing $date?"

    No matter which way I ordered the tables in the JOIN (it was Access, so of course what I needed to do wasn't supported), there had to be one master table with dates to rule them all, and if a row was missing from it, it'd be skipped... even though the other tables might have rows for the date. Basically, I needed a Dates table.

    I didn't create a Dates table. At least, not in the database. What I ended up doing was this:

    SELECT 
      ...
    FROM
      (
        (
          (
            SELECT dt
            FROM (
              SELECT table1.dt FROM table1
              UNION
              SELECT table2.dtmDate AS dt FROM table2
              UNION
              SELECT table3.dtmDate AS dt FROM table3
            )
            GROUP BY dt
          ) AS Dates LEFT JOIN table1 ON Dates.dt = table1.dt
        ) LEFT JOIN table2 ON Dates.dt = table2.dtmDate
      ) LEFT JOIN table3 ON table2.dtmDate = table3.dtmDate
    WHERE
      Dates.dt Between ["Beginning Date"] And ["Ending Date"];
    

    It'll still skip dates, but only if they're missing from all three tables...


  • :belt_onion:

    DELETE FROM [Numbers] WHERE number=1;
    INSERT INTO [Numbers] VALUES (2);
    

    Filed Under: Hehehe


  • :belt_onion:

    Or, better, for a bug you'll never find...

    DELETE FROM [Numbers] WHERE number=54;
    INSERT INTO [Numbers] VALUES (55);
    


  • @CoyneTheDup said:

    ```
    from nums a, nums b, nums c, nums d

    Twitch...twitch...twitch...
    
    Please tell me you don't actually write non-ANSI joins in real life.


  • The T-SQL keyword "all" is awesome.



  • @Jaime said:

    @CoyneTheDup said:
    from nums a, nums b, nums c, nums d

    Twitch...twitch...twitch...

    Please tell me you don't actually write non-ANSI joins in real life.

    Well, first of all, that was the traditional form of join in IBM DB2, until they finally got around to implementing the JOIN keywords (in V4, I think). [Sepulchral voice] That usage predates ANSI my friend. OoooOOOOooooo....

    Second, they didn't implement CROSS JOIN until the most recent version, V10:

    from nums as a cross join nums as b cross join nums as c cross join nums as d
    

    Prior to that, yes, you could do it with inner join but it would be a headache:

    from nums as a join nums as b on 1 = 1
                   join nums as c on 1 = 1
                   join nums as d on 1 = 1
    

    So, the traditional format is still useful (and convenient) in a few cases:

     from nums a, nums b, nums c, nums d
    

    OoooOOOOooooo....



  • Btw, if the number you needs never exceeds 2047, you can use a system table instead.

    SELECT number FROM [master].[dbo].[spt_values] where name is null

    This will give you 0 to 2047. I vaguely remember building store procs that needs something like row_number() in SQL2000 using this.


Log in to reply