|
Absolutely! 
|
|
|
|
|
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.
|
|
|
|
|
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:
- 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?
- as silly as it sounds, explain what "recursive" actually means.
- 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?
- How does this stuff work with an ORM, like EF? Is EF smart enough to use
UNION ALL or 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:
- people that are supervisors but do not have supervisors.
- people that are not supervisors but are themselves supervised.
- immediate people that are being supervised by supervisors that do not have supervisors.
- all people in the hierarchy that are being supervised by supervisors that do or do not have supervisors.
- traverse the hierarchy upwards: given a person, who are all their supervisors, recursively.
[/edit]
Thinking of queries like that delve more into architectural considerations and make for potentially really messy queries.
Hope that helps.
Marc
|
|
|
|
|
Thank 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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Low quality SQL object loses nothing to be easy to move! (8)
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
The lowest quality object of SQL is CURSOR, but I need two more letters here...
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Portable
"This new learning amazes me, Sir Bedivere. Explain to me again how sheep's bladders may be employed to prevent earthquakes"
|
|
|
|
|
You are up tomorrow - care to explain for everyone else?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
SQL analog of an object is a Table. Bad Quality = Poor. Loses nothing; loses an o so Poor become Por.
"This new learning amazes me, Sir Bedivere. Explain to me again how sheep's bladders may be employed to prevent earthquakes"
|
|
|
|
|
Spot on - you are still up tomorrow! 
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
|
|
|
|
|
Movie Quote Of The Day
Quote: Man gotta be the man, fish gotta be the fish.
Which movie?
|
|
|
|
|
A Fish Called Walter? 
Either that or
Tarzan vs. Jaws 
Anything that is unrelated to elephants is irrelephant
Anonymous
-
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-
I'd just like a chance to prove that money can't make me happy.
Me, all the time
|
|
|
|
|
Womanizer
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
Are you thinking of this one[^]?
Anything that is unrelated to elephants is irrelephant
Anonymous
-
The problem with quotes on the internet is that you can never tell if they're genuine
Winston Churchill, 1944
-
I'd just like a chance to prove that money can't make me happy.
Me, all the time
|
|
|
|
|
More about a candidate with fluffy hair
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
A Fish Called Wanda
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
|
Ok, once again time for.....
SHARKNADO
|
|
|
|
|
"Fish And Chips"... holy... this even is a real movie 
(yes|no|maybe)*
"Fortunately, we don't need details - because we can't solve it for you." - OriginalGriff
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
