May 15, 2015 at 1:33 pm
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 🙂
May 18, 2015 at 2:03 pm
Recursion can be dangerous but I agree, sometimes very helpful.
May 18, 2015 at 4:46 pm
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
Change is inevitable... Change for the better is not.
May 20, 2015 at 7:25 am
"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.
May 20, 2015 at 11:27 am
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."
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
June 4, 2015 at 8:56 am
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/
June 4, 2015 at 9:44 am
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.
-- Itzik Ben-Gan 2001
June 4, 2015 at 9:54 am
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.
June 4, 2015 at 10:27 am
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.
June 4, 2015 at 10:30 am
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.
June 4, 2015 at 1:31 pm
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
June 4, 2015 at 2:45 pm
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.
March 3, 2017 at 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;
March 3, 2017 at 3:21 pm
Dan Kelley - Friday, March 3, 2017 2:16 PMI 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 = 0WHILE (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
March 3, 2017 at 6:07 pm
SoCal_DBD - Friday, March 3, 2017 3:21 PMDan Kelley - Friday, March 3, 2017 2:16 PMI 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 = 0WHILE (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.columnsINSERT 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_CountSELECT * FROM #def
ORDER BY SeqNo, Date_FieldHappy 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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 60 (of 68 total)
You must be logged in to reply to this topic. Login to reply