Are the posted questions getting worse?

Question

Post reply

Are the posted questions getting worse?

Viewing 15 posts - 20,716 through 20,730 (of 66,815 total)

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

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

On one hand it's good to have that stuff posted. Helps to weed out the people that don't bother to research anything.

WayneS SSC Guru Points: 95461 More actions · Answer 2

Steve Jones - SSC Editor (10/21/2010)
On one hand it's good to have that stuff posted.

Gives those that do know something to laugh at.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Stefan Krzywicki (10/21/2010)
Q: Explain how to determine if a table has a primary key?
Lock the database, if the table can get back in, it has a key.

That's not 100% correct. You have to specify a primary lock. If you used a secondary lock then you may have proven that the table had a secondary key instead of a primary one.

Imagine going to an interview and being asked a question about DBCC TimeWarp. 😀 :w00t:

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Alvin Ramard (10/21/2010)
Stefan Krzywicki (10/21/2010)
Q: Explain how to determine if a table has a primary key?
Lock the database, if the table can get back in, it has a key.
That's not 100% correct. You have to specify a primary lock. If you used a secondary lock then you may have proven that the table had a secondary key instead of a primary one.
Imagine going to an interview and being asked a question about DBCC TimeWarp. 😀 :w00t:

Well, that all depends on if they want the Jan 2020 version, the Mar 3010 version, or that broken piece of crap that came out in May 2095 that they took 15 years trying to correct before realizing they could just DBCC Timewarp the 2020 back into place.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

GSquared (10/21/2010)
Jeff Moden (10/20/2010)
GSquared (10/20/2010)
it's been worse than every other standard hierarchy implementation that I know of
What do you mean, Gus? How has it been worse? Implementation, performance, easy of maintenance, useage, or ????
In my experience, it does slower selects than nested sets while having the update issues they have, and does slower updates than adjacency while not achieving a significant speed increase over them until you hit very deep hierarchies.
Basically, in my experience, it's good at semi-static hierarchies because of update issues, but nested sets are better at exactly that.
Path hierarchies are where it will do well. But most of those aren't deep enough to require better performance than adjacency, which gives better update/insert/delete performance. So it really needs semi-static, deep, path hierarchies, in order to beat the other major hierarchy implementations. I haven't had a chance to implement one of those, and doubt I ever will. I've done proof-of-concept work on it, in my copy of Dev Edition at home, and it does work well for that, but to me it's a theoretical thing with very little, if any, real-world value.
I rank it right down there with SQL's implementation of Pivot. Theoretically useful, but in practice, not so much.
And I have done multi-path adjacency hierarchies using adjacency (a many-to-many self-join) and it was easy to build and manage. Not as easy to query, but there were workarounds for that.
So far as I know, if you wanted to do geneology with the Hierarchy datatype, you'd pretty much end up with one hierarchy path per ancestor for each node. That would be worse than a simple two-parent adjacency model.

I work mostly with "DACs" so I haven't had to work with two-parent models of any type.

Thanks for taking the time to write some great feedback. I'm still working on the hierarchy presentation for my alternate PASS presentation but I was coming to similar conclusions (I just recently loaded up 2K8... I know... lazy bum I am :-D) and wanted to make sure that I wasn't fouling up the works somewhere (which is why I didn't want to taint opinions by asking specific questions). Once I get it done, I'll convert it to an SSC article. It won't be all encompassing (it's a HUGE subject) but it'll take some of the mystic out of hierarchies for beginners and maybe a couple of folks who aren't beginners.

--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.

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

Grant Fritchey (10/20/2010)
Jeff Moden (10/20/2010)
Paul White NZ (10/20/2010)
GSquared (10/20/2010)
I definitely don't. Has all the disadvantages of nested sets and the disadvantages of adjacency, without the advantages of either. It's a "worst of both worlds" scenario.
HierarchyID certainly doesn't solve all problems, but it's hardly the 'worst of both worlds'. For graph problems that benefit from a materialized path representation, hierarchyid provides a robust and extremely compact implementation that reduces the need for custom coding. There is also a decent level of support within the query optimizer for it, which can produce exceptionally efficient plans for problems it is well-suited to. Itzik Ben-Gan covers it quite well in his 2008 T-SQL Querying book (pages 706 - 730). He covers Nested Sets in some detail straight afterward.
edit: The other nice thing about hierarchyid is that it is a CLR type - so .NET code can use it directly, either inside SQL Server or outside. Also, there are likely to be some important improvements to hierarchyid in Denali.
Have you implemented it in anything you've done?
I have a guy in my shop that's put it to work. No negative, or positive, reports so far.

I guess someone would have to make an actual comparison to report on it one way or the other. Do you know if that person has done hierarchies without HierarchyID, Grant?

--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.

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 7

Stefan Krzywicki (10/20/2010)
Nope, all I've done with it so far is research it, work out if it could be more flexible than it seems (I think it can) and teach the .Net programmer about it so he could use it. I don't know if he has or not.

Ok. Thanks for the input, Stefan.

--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.

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 8

ALZDBA (10/20/2010)
Dito.
And it only serves a single purpose. i.e. strict hierarchy.
Ever tried building a family tree ?

Thanks for the input, Johan. Heh... from what I've seen, even nested sets have some difficulty with family trees.

--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.

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 9

Paul White NZ (10/19/2010)
Jeff Moden (10/19/2010)
I prefer your simplified row number checker. It doesn't slow down much and doesn't have problems with unbalanced sets that Hugo's code does.
Thanks. My one remaining concern with it is what to do if an error is detected. I guess it depends on the circumstances, but it seems a shame to have to code a second implementation 'just in case'.
The worst case performance of the set-based iteration method is poor, I agree. From memory, it's worse than a cursor with one 'set', but that's not a common circumstance is it?
As you might guess, my preference remains for a SQLCLR solution in most cases, especially now I can use Adam's QP engine to run multiple threads, and bulk copy the results out.

I agree with something you said on the Running Total discussion... your check seems to "force" the method to work better. If even handled Hugo's "pre-broken" code (violated the ever important Rule 9... it has to work to start with :-D)

So far as what to do when it breaks? I'd say find out who changed an index like Wayne S did. Then fix it.

I never have a "break" because I usually do the calculations where I havee 100% control... in a Temp Table. 🙂

--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.

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

WayneS SSC Guru Points: 95461 More actions · Answer 10

Jeff Moden (10/21/2010)
Paul White NZ (10/19/2010)
Jeff Moden (10/19/2010)
I prefer your simplified row number checker. It doesn't slow down much and doesn't have problems with unbalanced sets that Hugo's code does.
Thanks. My one remaining concern with it is what to do if an error is detected. I guess it depends on the circumstances, but it seems a shame to have to code a second implementation 'just in case'.
The worst case performance of the set-based iteration method is poor, I agree. From memory, it's worse than a cursor with one 'set', but that's not a common circumstance is it?
As you might guess, my preference remains for a SQLCLR solution in most cases, especially now I can use Adam's QP engine to run multiple threads, and bulk copy the results out.
I agree with something you said on the Running Total discussion... your check seems to "force" the method to work better. If even handled Hugo's "pre-broken" code (violated the ever important Rule 9... it has to work to start with :-D)
So far as what to do when it breaks? I'd say find out who changed an index like Wayne S did. Then fix it.
I never have a "break" because I usually do the calculations where I havee 100% control... in a Temp Table. 🙂

Did you ever post about this? Counting you and me, there's only about, well, 2 people that know what you're talking about here.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 11

WayneS (10/21/2010)
Did you ever post about this? Counting you and me, there's only about, well, 2 people that know what you're talking about here.

Nope... that's going to be in the next (and, hopefully, final) rewrite. 🙂

--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.

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

Paul White SSC Guru Points: 150468 More actions · Answer 12

WayneS (10/21/2010)
Did you ever post about this? Counting you and me, there's only about, well, 2 people that know what you're talking about here.

3 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 13

Jeff Moden (10/21/2010)
ALZDBA (10/20/2010)
Dito.
And it only serves a single purpose. i.e. strict hierarchy.
Ever tried building a family tree ?
Thanks for the input, Johan. Heh... from what I've seen, even nested sets have some difficulty with family trees.

and we didn't even mention IVF ( in virto fertilization )

or the alike stuff of reproduction.

Should we sugest a new msconnect topic: the new SQLnext datatype: Genealogy :hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 14

Jeff Moden (10/21/2010)
Grant Fritchey (10/20/2010)
Jeff Moden (10/20/2010)
Paul White NZ (10/20/2010)
GSquared (10/20/2010)
I definitely don't. Has all the disadvantages of nested sets and the disadvantages of adjacency, without the advantages of either. It's a "worst of both worlds" scenario.
HierarchyID certainly doesn't solve all problems, but it's hardly the 'worst of both worlds'. For graph problems that benefit from a materialized path representation, hierarchyid provides a robust and extremely compact implementation that reduces the need for custom coding. There is also a decent level of support within the query optimizer for it, which can produce exceptionally efficient plans for problems it is well-suited to. Itzik Ben-Gan covers it quite well in his 2008 T-SQL Querying book (pages 706 - 730). He covers Nested Sets in some detail straight afterward.
edit: The other nice thing about hierarchyid is that it is a CLR type - so .NET code can use it directly, either inside SQL Server or outside. Also, there are likely to be some important improvements to hierarchyid in Denali.
Have you implemented it in anything you've done?
I have a guy in my shop that's put it to work. No negative, or positive, reports so far.
I guess someone would have to make an actual comparison to report on it one way or the other. Do you know if that person has done hierarchies without HierarchyID, Grant?

Not to my knowledge they haven't, no. I'll have to check with them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

GSquared SSC Guru Points: 260824 More actions · Answer 15

Jeff Moden (10/21/2010)
ALZDBA (10/20/2010)
Dito.
And it only serves a single purpose. i.e. strict hierarchy.
Ever tried building a family tree ?
Thanks for the input, Johan. Heh... from what I've seen, even nested sets have some difficulty with family trees.

I'm not sure how you could do a valid nested sets hierarchy for a real geneology. Pedigree collapse alone would make it at least prohibitively difficult, if not impossible. Nested sets only work well for inverse-pyramid hierarchies. You could do it by having each person exist in two hierarchy ranges (paternal, maternal), but charting it out into human-useful data becomes difficult, and you'd have to increase the range of valid values for each tier as each new generation is added, which makes planning the ranges difficult. (That's usually the biggest difficulty with nested sets hierarchies.) Then, of course, if you find out that an ancestor a few generations back actually had a different father than you originally thought, you run into the other major difficulty with nested sets, which is moving nodes around is a pain.

Family trees are actually one of the few places where I think having two hierarchy ID entries per person might actually work. It's a relatively (pun intended) static, deep, path-style hierarchy. Updates are going to be rare, inserts will usually be at the end of the path, and so on, which avoids the performance issues associated with these things. You'll run into some issues with mid-path corrections, but I don't know if they'll be as bad as nested sets on that or not. Easy enough to do a proof on that.

You might run into problems with the maximum data size on hierarchy IDs if you're doing a REALLY large family tree, but real-world limitations on data availability probably make this a moot point.

I'll have to generate some test data on this and see how it goes. Haven't tried it before. Based on prior experience, adjacency will probably be easier to work with and about as fast, but that's surmise, not proof.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon