Usage of CTE - Trick with Dates

  • I agree, I just used a table variables so you can run this code anywhere without even needing the right to create objects in a database.

    Also, I did not read up on the old comments before posting, got ahead of myself and just put the code together as a proof of concept right after reading the article 🙂

  • Recursion can be dangerous but I agree, sometimes very helpful.

  • Iwas Bornready (5/18/2015)


    Recursion can be dangerous but I agree, sometimes very helpful.

    Do you have any examples of where it was helpful?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • "Again, if we have a million rows in the source table dbo.abc with each row having a value of 10 for the column, Month_Count then there would be a total of ten million individual one-row INSERTs into the destination table, dbo.def. But in the same scenario with recursive CTE, there would still be exactly one INSERT into the table dbo.def although this would insert 10 million rows in a set-based fashion. In an OLTP system, this means that the number of times locks would be held on the destination table dbo.def would be way less than in the CURSOR-based approach. So the recursive CTE based approach can reduce blocking compared to the CURSOR-based approach in an OLTP scenario."

    I think that some plusses such as shorter code are not really pluses.

    Also, remember that performance is not only a function of elapsed time a process (stored procedure) is taking but in a lot of cases the way a process interacts with the other processes in a multi-user environment, produce load to the server. Especially if we are talking about batch processes, data load processes and reports. They can run for a long time with multiple processes in parallel. And different technique methods can be used to minimize time and improve the way processes affect users and/or other processes. E.g. it is not always wise to insert 10 or 100 millions of rows in one transaction. In this case, mixture of solutions may be necessary. Split process based on decade or other factors to minimize number of inserts (not insert for one row) but at the same time minimize amount of rows for each transaction.

  • Requirements often don't directly address transactions and locking. Non-functional requirements normally don't specify or limit T-SQL methods. A table that describes the preverbal "it depends" by relating requirements to T-SQL methods would be valuable to business analysts and developers.

    An incremental approach can be valid where the process must allow existing processes to continue unaffected. Set based integration using NOLOCK should not be the first and only solution. But it might be appropriate, depending....

    I currently don't often see cursors used where they are not needed. Like scalar UDFs, cursors have gotten bad name. Several years ago I attempted to follow the logic of a procedure that uses nested cursors and nested procedures. It was mind bending. I did not see the requirements, so the methods used might be appropriate. For example, the primary requirement might have been "The logic must stump and stupefy any DBA attempting analysis of the procedure."

    Randy
    Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • nycdotnet (6/18/2013)


    Interesting article. Regarding the paradox of semicolons on CTEs - the rule is "the statement before the CTE has to end with a semicolon".

    I believe the semicolon is the formal terminal punctuation for SQL. MS is only now including it as part of the required syntax preceding certain statements/commands but is usable in all instances and will likely be required in a future version. Start using it now & get a jump on refactoring your DDL/DML.

    https://msdn.microsoft.com/en-us/library/ms177563.aspx

    http://www.dbdelta.com/always-use-semicolon-statement-terminators/

  • HLogic (6/4/2015)


    nycdotnet (6/18/2013)


    Interesting article. Regarding the paradox of semicolons on CTEs - the rule is "the statement before the CTE has to end with a semicolon".

    I believe the semicolon is the formal terminal punctuation for SQL. MS is only now including it as part of the required syntax preceding certain statements/commands but is usable in all instances and will likely be required in a future version. Start using it now & get a jump on refactoring your DDL/DML.

    https://msdn.microsoft.com/en-us/library/ms177563.aspx

    http://www.dbdelta.com/always-use-semicolon-statement-terminators/

    Terminating your SQL statements with a semicolon is noted as a best practice in a lot of books including Itzik Ben-Gan. Azure SQL (which I am seeing used more and more) requires it. I do it as a matter of habit.

    "I 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

  • HLogic (6/4/2015)


    nycdotnet (6/18/2013)


    Interesting article. Regarding the paradox of semicolons on CTEs - the rule is "the statement before the CTE has to end with a semicolon".

    I believe the semicolon is the formal terminal punctuation for SQL. MS is only now including it as part of the required syntax preceding certain statements/commands but is usable in all instances and will likely be required in a future version. Start using it now & get a jump on refactoring your DDL/DML.

    https://msdn.microsoft.com/en-us/library/ms177563.aspx

    http://www.dbdelta.com/always-use-semicolon-statement-terminators/

    The semicolon is a terminator, not a begininator. It does NOT belong at the beginning of ANY SQL statement and it's use there is a crutch to ensure that the previous statement is terminated by a semicolon where required, such as when using the WITH to define a CTE.

  • The reason a semicolon is required before the WITH in a CTE as that WITH has two uses, it can be used for hints e.g.

    SELECT * FROM MyTable WITH (NOLOCK)

    and used in a CTE:

    WITH CTE AS(SELECT * FROM...

    With a single pass compiler you need to be able to tell if the WITH is part of the SELECT statement or the start of a CTE or a syntax error.

  • Jonathan AC Roberts (6/4/2015)


    The reason a semicolon is required before the WITH in a CTE as that WITH has two uses, it can be used for hints e.g.

    SELECT * FROM MyTable WITH (NOLOCK)

    and used in a CTE:

    WITH CTE AS(SELECT * FROM...

    With a single pass compiler you need to be able to tell if the WITH is part of the SELECT statement or the start of a CTE or a syntax error.

    When using WITH to define a CTE the PRECEDING statement MUST be terminated with a semicolon. The semicolon does NOT belong at the beginning of the CTE declaration.

    Preceding the WITH with a semicolon is nothing more than a crutch to ensure that the preceding statement is terminated with a semicolon.

  • Lynn Pettis (6/4/2015)


    HLogic (6/4/2015)


    nycdotnet (6/18/2013)


    Interesting article. Regarding the paradox of semicolons on CTEs - the rule is "the statement before the CTE has to end with a semicolon".

    I believe the semicolon is the formal terminal punctuation for SQL. MS is only now including it as part of the required syntax preceding certain statements/commands but is usable in all instances and will likely be required in a future version. Start using it now & get a jump on refactoring your DDL/DML.

    https://msdn.microsoft.com/en-us/library/ms177563.aspx

    http://www.dbdelta.com/always-use-semicolon-statement-terminators/

    The semicolon is a terminator, not a begininator. It does NOT belong at the beginning of ANY SQL statement and it's use there is a crutch to ensure that the previous statement is terminated by a semicolon where required, such as when using the WITH to define a CTE.

    +1000 for "begininator"

    Don Simpson



    I'm not sure about Heisenberg.

  • Lynn Pettis (6/4/2015)


    Jonathan AC Roberts (6/4/2015)


    The reason a semicolon is required before the WITH in a CTE as that WITH has two uses, it can be used for hints e.g.

    SELECT * FROM MyTable WITH (NOLOCK)

    and used in a CTE:

    WITH CTE AS(SELECT * FROM...

    With a single pass compiler you need to be able to tell if the WITH is part of the SELECT statement or the start of a CTE or a syntax error.

    When using WITH to define a CTE the PRECEDING statement MUST be terminated with a semicolon. The semicolon does NOT belong at the beginning of the CTE declaration.

    Preceding the WITH with a semicolon is nothing more than a crutch to ensure that the preceding statement is terminated with a semicolon.

    Yes, of course you are correct, the official term for a semicolon is a "statement terminator". You can use a CTE if it is the first line in a block without a semicolon, e.g.

    GO

    WITH CTE AS (SELECT * FROM myTable...

    but whether you word it as "a semicolon before the CTE" or "a semicolon after the preceding statement" is just tautology. The semicolon is a statement separator just as a GO is a batch block separator or batch block terminator.

    But the reason Microsoft make you terminate the preceding statement with a semicolon is because of the double usage of WITH and compilation. Personally, I dislike computer languages that use punctuation.

  • I figured I'd give it a go before I looked at the answers and here's what I came up with. :laugh: It's probably terribly inefficient, I'm new at this and didn't know about tally tables or CTE's 🙂

    DECLARE @TotalRowcount INT = (SELECT SUM(Month_Count) FROM abc)
    DECLARE @abc_row INT = 1
    DECLARE @Payment DECIMAL(10,2) = (SELECT Payment FROM abc WHERE SeqNo = @abc_row)
    DECLARE @MonthsToAdd INT = 0

    WHILE (SELECT COUNT(*) FROM def) < @TotalRowcount
      BEGIN
     IF @MonthsToAdd = (SELECT Month_Count FROM abc WHERE  SeqNo = @abc_row)
       BEGIN
      SET @abc_row += 1;
      SET @MonthsToAdd = 0;
      SET @Payment = (SELECT Payment FROM abc WHERE SeqNo = @abc_row);
       END;
      
     INSERT  INTO def (SeqNo,Date_Field,Payment)
     SELECT @abc_row SeqNo
         ,DATEADD(M, @MonthsToAdd, abc.Date_Field) Date_Field
         ,@Payment Payment
     FROM abc
     WHERE SeqNo = @abc_row;
      
     SET @MonthsToAdd += 1;
      END;

  • Dan Kelley - Friday, March 3, 2017 2:16 PM

    I figured I'd give it a go before I looked at the answers and here's what I came up with. :laugh: It's probably terribly inefficient, I'm new at this and didn't know about tally tables or CTE's 🙂

    DECLARE @TotalRowcount INT = (SELECT SUM(Month_Count) FROM abc)
    DECLARE @abc_row INT = 1
    DECLARE @Payment DECIMAL(10,2) = (SELECT Payment FROM abc WHERE SeqNo = @abc_row)
    DECLARE @MonthsToAdd INT = 0

    WHILE (SELECT COUNT(*) FROM def) < @TotalRowcount
      BEGIN
     IF @MonthsToAdd = (SELECT Month_Count FROM abc WHERE  SeqNo = @abc_row)
       BEGIN
      SET @abc_row += 1;
      SET @MonthsToAdd = 0;
      SET @Payment = (SELECT Payment FROM abc WHERE SeqNo = @abc_row);
       END;
      
     INSERT  INTO def (SeqNo,Date_Field,Payment)
     SELECT @abc_row SeqNo
         ,DATEADD(M, @MonthsToAdd, abc.Date_Field) Date_Field
         ,@Payment Payment
     FROM abc
     WHERE SeqNo = @abc_row;
      
     SET @MonthsToAdd += 1;
      END;

    Hi Dan and welcome to this (awesome) forum!  Good stab, but that still is operating as a cursor, or more affectionately known as "RBAR" (row by agonizing row).  I'll save you the time looking through the responses on this post (I see there are many pages and it is rather old), but here is a solution that implements a "tally table".  These things are magical.  I can highly recommend reading up on anything written by author extraordinaire on this forum, Jeff Moden.  His article on the tally table will blow your mind and can be found here:  http://www.sqlservercentral.com/articles/T-SQL/62867/

    I took the example and created temp tables to do the work, but here is an example of a tally table solution for this:


    IF OBJECT_ID('tempdb..#abc', 'U') IS NOT NULL
    DROP TABLE #abc
    IF OBJECT_ID('tempdb..#def', 'U') IS NOT NULL
    DROP TABLE #def
    IF OBJECT_ID('tempdb..#tally', 'U') IS NOT NULL
    DROP TABLE #tally

    --Create the source table, dbo.abc.
    CREATE TABLE #abc
    (SeqNo SMALLINT,
    Date_Field SMALLDATETIME,
    Month_Count TINYINT,
    Payment DECIMAL(10,2))

    --Populate the source table, dbo.abc
    INSERT INTO #abc (SeqNo, Date_Field, Month_Count, Payment)
    VALUES (1, '20090101', 10, 100),
       (2, '20100101', 7, 200),
       (3, '20110101', 5, 300)

    --Create the destination table, dbo.def
    CREATE TABLE #def
    (SeqNo SMALLINT,
    Date_Field SMALLDATETIME,
    Payment DECIMAL(10,2))

    -- Create a temp table version of a small tally table
    SELECT TOP 1000 IDENTITY(INTEGER, 1, 1) AS N
    INTO #Tally
    FROM sys.[objects]
    CROSS JOIN sys.columns

    INSERT INTO #def (SeqNo, Date_Field, Payment)
    SELECT a.SeqNo, DATEADD(M, t.N - 1, a.Date_Field), a.Payment
    FROM #abc a
    CROSS JOIN #Tally t
    WHERE t.N <= a.Month_Count

    SELECT * FROM #def
    ORDER BY SeqNo, Date_Field

    Happy coding!

    Lisa

  • SoCal_DBD - Friday, March 3, 2017 3:21 PM

    Dan Kelley - Friday, March 3, 2017 2:16 PM

    I figured I'd give it a go before I looked at the answers and here's what I came up with. :laugh: It's probably terribly inefficient, I'm new at this and didn't know about tally tables or CTE's 🙂

    DECLARE @TotalRowcount INT = (SELECT SUM(Month_Count) FROM abc)
    DECLARE @abc_row INT = 1
    DECLARE @Payment DECIMAL(10,2) = (SELECT Payment FROM abc WHERE SeqNo = @abc_row)
    DECLARE @MonthsToAdd INT = 0

    WHILE (SELECT COUNT(*) FROM def) < @TotalRowcount
      BEGIN
     IF @MonthsToAdd = (SELECT Month_Count FROM abc WHERE  SeqNo = @abc_row)
       BEGIN
      SET @abc_row += 1;
      SET @MonthsToAdd = 0;
      SET @Payment = (SELECT Payment FROM abc WHERE SeqNo = @abc_row);
       END;
      
     INSERT  INTO def (SeqNo,Date_Field,Payment)
     SELECT @abc_row SeqNo
         ,DATEADD(M, @MonthsToAdd, abc.Date_Field) Date_Field
         ,@Payment Payment
     FROM abc
     WHERE SeqNo = @abc_row;
      
     SET @MonthsToAdd += 1;
      END;

    Hi Dan and welcome to this (awesome) forum!  Good stab, but that still is operating as a cursor, or more affectionately known as "RBAR" (row by agonizing row).  I'll save you the time looking through the responses on this post (I see there are many pages and it is rather old), but here is a solution that implements a "tally table".  These things are magical.  I can highly recommend reading up on anything written by author extraordinaire on this forum, Jeff Moden.  His article on the tally table will blow your mind and can be found here:  http://www.sqlservercentral.com/articles/T-SQL/62867/

    I took the example and created temp tables to do the work, but here is an example of a tally table solution for this:


    IF OBJECT_ID('tempdb..#tally', 'U') IS NOT NULL
      DROP TABLE #tally

    -- Create a temp table version of a small tally table
    SELECT TOP 1000 IDENTITY(INTEGER, 1, 1) AS N
    INTO #Tally
    FROM sys.[objects]
    CROSS JOIN sys.columns

    INSERT INTO #def (SeqNo, Date_Field, Payment)
    SELECT SeqNo, Date_Field, Payment
    FROM #abc
    UNION ALL
    SELECT a.SeqNo, DATEADD(M, t.N, a.Date_Field), a.Payment
    FROM #abc a
    CROSS JOIN #Tally t
    WHERE t.N <= a.Month_Count

    SELECT * FROM #def
    ORDER BY SeqNo, Date_Field

    Happy coding!

    Lisa

    There's no need for the first SELECT of the UNION ALL nor the UNION ALL, Lisa.  Give it a shot.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 46 through 60 (of 69 total)

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