How to explain recursive CTE in as simple a manner as possible
-
I actually found it helpful in creating a stored procedure for a report. Unfortunately I couldn't use a CTE due to some other constraints, but I did learn something. Thanx!
Great that you found it useful, Besinger! :) If you would like to talk about why you cannot use CTE in your SP, I would be glad to help.
-
Hi CodeProject members, Greetings! After being a passive reader of CodeProject articles for so long a time, it feels great to be actively contributing something to this thriving and extremely helpful community. Recently, I decided to undertake the task of explaining some relatively complex concepts of T-SQL, SQL Server and MSBI, in an effort to give back something to the community I have been learning from for so long. My goal is to simplify the explanation and elucidation of the concepts as much as possible, at the expense of being annoyingly verbose if need be. ;P To this effect, I started with explanation of a recursive Common Table Expression in t-SQL. My article can be found at[^] http://www.codeproject.com/Articles/1139746/Recursive-CTE. Now this is my first attempt at explaining something in writing. I need feedback-, how have I fared overall, suggestions for improvement, possible topics for later etc. Thus, I am sincerely hoping, active community members would help me out in this regard. I have tried explaining the recursive CTE concept to the best of my ability here. But of course, often one's best is still lacking in some respects. Please come forth with your comments and feedback. Could I have used a better example to explain the concept? Could I have used better formatting for the code snippets? Or the result-sets? How could I have ensured that this article is as helpful as possible to a person who is finding it difficult to grasp the recursive CTE concept? Looking forward to hearing from you.
After reading the comments here (and Michael's was spot on, IMO), here's my thoughts: Nice example, though please fix the clickey. At this point, it seems to point to my last post! Explain: The ‘UNION ALL’ is a syntactic handcuff. What do you mean by "handcuff?" You might want to reference other explanations, like SQL Anywhere: Example: RECURSIVE UNION[^]
Rahul_Biswas wrote:
Could I have used better formatting for the code snippets?
IMO, yes -- get rid of the extra lines and use them only to separate top level "blocks" like "select" and "union"
Rahul_Biswas wrote:
Or the result-sets?
Yes - get rid of extra lines or use screenshots.
Rahul_Biswas wrote:
How could I have ensured that this article is as helpful as possible to a person who is finding it difficult to grasp the recursive CTE concept?
That's a difficult question. Some suggestions: 1. a lot of people want to know about performance. Dig into the SQL plan so people get an idea if SQL Server is doing a full table scan, and if it is, are there ways to prevent that? 2. as silly as it sounds, explain what "recursive" actually means. 3. What are the alternatives? Can you remove the self-reference (requiring a nullable column) by splitting the table into employees and supervisors? (Why yes, you can.) What are the pros and cons of that architecture and how does it affect the query and performance? 4. How does this stuff work with an ORM, like EF? Is EF smart enough to use
UNION ALLor does it do separate queries or does it bail and leave it up to the coder? [edit] You might want to illustrate (with #3 in mind) how you do other queries as well, like: 1. people that are supervisors but do not have supervisors. 2. people that are not supervisors but are themselves supervised. 3. immediate people that are being supervised by supervisors that do not have supervisors. 4. all people in the hierarchy that are being supervised by supervisors that do or do not have supervisors. 5. traverse the hierarchy upwards: given a person, who are all their supervisors, recursively. [/edit] Thinking of queries like that delve more -
After reading the comments here (and Michael's was spot on, IMO), here's my thoughts: Nice example, though please fix the clickey. At this point, it seems to point to my last post! Explain: The ‘UNION ALL’ is a syntactic handcuff. What do you mean by "handcuff?" You might want to reference other explanations, like SQL Anywhere: Example: RECURSIVE UNION[^]
Rahul_Biswas wrote:
Could I have used better formatting for the code snippets?
IMO, yes -- get rid of the extra lines and use them only to separate top level "blocks" like "select" and "union"
Rahul_Biswas wrote:
Or the result-sets?
Yes - get rid of extra lines or use screenshots.
Rahul_Biswas wrote:
How could I have ensured that this article is as helpful as possible to a person who is finding it difficult to grasp the recursive CTE concept?
That's a difficult question. Some suggestions: 1. a lot of people want to know about performance. Dig into the SQL plan so people get an idea if SQL Server is doing a full table scan, and if it is, are there ways to prevent that? 2. as silly as it sounds, explain what "recursive" actually means. 3. What are the alternatives? Can you remove the self-reference (requiring a nullable column) by splitting the table into employees and supervisors? (Why yes, you can.) What are the pros and cons of that architecture and how does it affect the query and performance? 4. How does this stuff work with an ORM, like EF? Is EF smart enough to use
UNION ALLor does it do separate queries or does it bail and leave it up to the coder? [edit] You might want to illustrate (with #3 in mind) how you do other queries as well, like: 1. people that are supervisors but do not have supervisors. 2. people that are not supervisors but are themselves supervised. 3. immediate people that are being supervised by supervisors that do not have supervisors. 4. all people in the hierarchy that are being supervised by supervisors that do or do not have supervisors. 5. traverse the hierarchy upwards: given a person, who are all their supervisors, recursively. [/edit] Thinking of queries like that delve moreThank you so very much. These are very helpful comments and suggestions. I will try to modify the article going forward with these. Once again, thanks so much.
-
Now I am not sure about whether one can be accused of plagiarizing from oneself. Self-plagiarizing, perhaps? :rolleyes: It is me who wrote that answer. But of course, a better platform for that kind of answer is CodeProject. Don't you think? Now please check the link. I am going to delete the thread from Quora again. I don't want same article posted multiple times in multiple places.
Rahul_Biswas wrote:
Now I am not sure about whether one can be accused of plagiarizing from oneself.
I was, once, by a Dutch moron living in Kowloon and conning kids out of money for mostly copied-and-pasted English lessons (example here[^]) The stuff he added himself in the discussion boards he spammed was always completely wrong, so I was continually pulling him up on it (as well as posting the sources of his stolen text). On one occasion, I quoted my own text from one of my own sites, and he immediately flared up, demanding to know how I dared to call him a plagiarist and thief, when I had "stolen" the words from a web-site. Ranted about it for several hundred words. So I added a comment at the top of the page I'd copied my work out of, telling him in no uncertain terms precisely what kind of idiot he is. Dick W.J. Oosterveld, the guy's name. Pretends to be the CO of a non-existent company named HKEOL (started as Hong Kong Exporters On-line, a poorly made web-shop that never sold a bean, then changed to Hong Kong English On Line, when he realised he could bilk Chinese kids out of money for copied and pasted English lessons). If you ever run across him, give him a smack in the mouth from me.
I wanna be a eunuchs developer! Pass me a bread knife!
-
Thanks Martin! :) I owe you.
How much beer can you afford?
I wanna be a eunuchs developer! Pass me a bread knife!
-
Rahul_Biswas wrote:
Now I am not sure about whether one can be accused of plagiarizing from oneself.
I was, once, by a Dutch moron living in Kowloon and conning kids out of money for mostly copied-and-pasted English lessons (example here[^]) The stuff he added himself in the discussion boards he spammed was always completely wrong, so I was continually pulling him up on it (as well as posting the sources of his stolen text). On one occasion, I quoted my own text from one of my own sites, and he immediately flared up, demanding to know how I dared to call him a plagiarist and thief, when I had "stolen" the words from a web-site. Ranted about it for several hundred words. So I added a comment at the top of the page I'd copied my work out of, telling him in no uncertain terms precisely what kind of idiot he is. Dick W.J. Oosterveld, the guy's name. Pretends to be the CO of a non-existent company named HKEOL (started as Hong Kong Exporters On-line, a poorly made web-shop that never sold a bean, then changed to Hong Kong English On Line, when he realised he could bilk Chinese kids out of money for copied and pasted English lessons). If you ever run across him, give him a smack in the mouth from me.
I wanna be a eunuchs developer! Pass me a bread knife!
:-D
-
Sorry I can't help as I know nothing on T-SQL or SQL Server in the last 15 years. But to everyone else, just fuck off and look at his article and judge him on that. The posting in 3 forums has been addressed in his initial or second reply. Get a life you miserable cunts and whinge like bitches else where.
Michael Martin Australia "I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So I had to leave the place as soon as possible." - Mr.Prakash One Fine Saturday. 24/04/2004
Absolutely! :thumbsup:
-
Hi CodeProject members, Greetings! After being a passive reader of CodeProject articles for so long a time, it feels great to be actively contributing something to this thriving and extremely helpful community. Recently, I decided to undertake the task of explaining some relatively complex concepts of T-SQL, SQL Server and MSBI, in an effort to give back something to the community I have been learning from for so long. My goal is to simplify the explanation and elucidation of the concepts as much as possible, at the expense of being annoyingly verbose if need be. ;P To this effect, I started with explanation of a recursive Common Table Expression in t-SQL. My article can be found at[^] http://www.codeproject.com/Articles/1139746/Recursive-CTE. Now this is my first attempt at explaining something in writing. I need feedback-, how have I fared overall, suggestions for improvement, possible topics for later etc. Thus, I am sincerely hoping, active community members would help me out in this regard. I have tried explaining the recursive CTE concept to the best of my ability here. But of course, often one's best is still lacking in some respects. Please come forth with your comments and feedback. Could I have used a better example to explain the concept? Could I have used better formatting for the code snippets? Or the result-sets? How could I have ensured that this article is as helpful as possible to a person who is finding it difficult to grasp the recursive CTE concept? Looking forward to hearing from you.
Simple rule for training or instruction is - Tell them what you are going to tell them Tell them Tell them what you told them in other words - give an overview in simple point form here you should start with a paragraph that allows the reader to decide whether the content is relevant to them. After reading that paragraph they should be able to decide whether to read on or not. deliver the details Keep the paragraphs short. No more than a few sentences. This allows the reader to take bite size information. Nothing turns me off more than long paragraphs that I need to reread 3 times to get it. then recap with simple reminder, in point form, of what you told them. When I write I start with a list of things I want to talk about, rearrange the list so it gradually builds on the previous knowledge. This reinforces the previous while providing small steps. it's also good to use examples that the target audience can relate to. In this case it's developers so an example that uses Dilbert and his pointy headed boss would be appropriate. Hope this helps.
-
Simple rule for training or instruction is - Tell them what you are going to tell them Tell them Tell them what you told them in other words - give an overview in simple point form here you should start with a paragraph that allows the reader to decide whether the content is relevant to them. After reading that paragraph they should be able to decide whether to read on or not. deliver the details Keep the paragraphs short. No more than a few sentences. This allows the reader to take bite size information. Nothing turns me off more than long paragraphs that I need to reread 3 times to get it. then recap with simple reminder, in point form, of what you told them. When I write I start with a list of things I want to talk about, rearrange the list so it gradually builds on the previous knowledge. This reinforces the previous while providing small steps. it's also good to use examples that the target audience can relate to. In this case it's developers so an example that uses Dilbert and his pointy headed boss would be appropriate. Hope this helps.
Thanks for the great suggestions. :) Can you elaborate a bit more on 'When I write I start with a list of things I want to talk about, rearrange the list so it gradually builds on the previous knowledge. This reinforces the previous while providing small steps.'? Thanks again.
-
Thanks for the great suggestions. :) Can you elaborate a bit more on 'When I write I start with a list of things I want to talk about, rearrange the list so it gradually builds on the previous knowledge. This reinforces the previous while providing small steps.'? Thanks again.
The important thing to remember is that people learn in small steps that each build on the last, so getting the order right and keeping each step small is important. The reader needs to be able to say "I got that" before moving on to the next lesson. When writing I use Word's headings. I start with Heading 1 style and then gradually drill down to greater detail using Heading 2, 3, 4 etc then get to the fine detail Then when I review my logical sequence and see that, well you need to know this before you can comprehend that, I may rearrange the order using Word's outline view that allows me to collapse a section to say Heading 1 and easily move the entire section. Once I've said all I need to say then I take the list of Headings and distil them to create the overview and summary. The overview items fall into perspective if you start each one with "By the end of this article you will be able to..." With experience you can get more creative but to start use that "by the end of ..." The summary is the same but with different wording where you briefly recap each section to remind the reader of what they just learned and maybe crystallise it in their minds. Like this - So to recap - Tell them what you are going to tell them in the overview give them the detail keep your sentences bite size Build each new section on the knowledge gained from the previous and then tell them what you told them in the summary
-
The important thing to remember is that people learn in small steps that each build on the last, so getting the order right and keeping each step small is important. The reader needs to be able to say "I got that" before moving on to the next lesson. When writing I use Word's headings. I start with Heading 1 style and then gradually drill down to greater detail using Heading 2, 3, 4 etc then get to the fine detail Then when I review my logical sequence and see that, well you need to know this before you can comprehend that, I may rearrange the order using Word's outline view that allows me to collapse a section to say Heading 1 and easily move the entire section. Once I've said all I need to say then I take the list of Headings and distil them to create the overview and summary. The overview items fall into perspective if you start each one with "By the end of this article you will be able to..." With experience you can get more creative but to start use that "by the end of ..." The summary is the same but with different wording where you briefly recap each section to remind the reader of what they just learned and maybe crystallise it in their minds. Like this - So to recap - Tell them what you are going to tell them in the overview give them the detail keep your sentences bite size Build each new section on the knowledge gained from the previous and then tell them what you told them in the summary
Great suggestions. Thanks a lot. :)
-
Great suggestions. Thanks a lot. :)
Good that you have started to write and post. It's a great way to learn as you get feedback, whether it's phrased in a kindly manner or not, there are often little gems that you had not have thought of. I often find that I get half way through a post and realise that I'm totally wrong and discard it. But simply writing the post allowed me to clarify my idea to myself and see why it was wrong.