September 30, 2007 at 4:31 pm
Ninja's_RGR'us (9/30/2007)
You should send those in for the QOTD.Can't wait to see the stats on those questions.
I stopped participating in QOTD when is became completely "out of BOL" quiz.
_____________
Code for TallyGenerator
September 30, 2007 at 5:39 pm
You can always submit your own questions... your contribution would certainly be very appreciated.
September 30, 2007 at 6:30 pm
You know, it's not the company I want to be surrounded with.
:hehe:
_____________
Code for TallyGenerator
October 1, 2007 at 10:12 pm
GilaMonster (9/30/2007)
Jeff Moden (9/29/2007)
I can't tell you the number of folks I've interviewed who have Masters or PHDs in mathematics that couldn't tell me what bit will be set by 24 or what the value of 1416 is.
16 and E, if I'm not completely loosing it.
I've got a query that does some job time calculations off MSDB and it's full of bitwise calcs (about 4 of them) There's not one other person on the IT floor at my company who understands that query. I hate to think what'll happen if they need to modify that and I'm not around....
Nope...
Didn't ask what the decimal value of 24 was... ask which bit would be set. Hint, first bit is called "bit 1" or "bit 0" depending on which "standard" you want to follow... so, there are two answers.
Didn't ask what 14 converted to hex was, asked what the value of 1416 was. To be more clear, what is the decimal value of 1416? Hint, the subscript identifies the base.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2007 at 10:27 pm
Heh... those are fun but I like the real-life questions that teach why or why not to do something... for example...
1. How many internal rows will be generated by the following query (assuming the original 830 rows provided by Microsoft)? (Easy to do... run it with Execution Plan turned on and add the number of rows on each arrow on the two originating symbols furthest to the right).
USE NorthWind
GO
SELECT X.ORDERID, FREIGHT, (SELECT SUM(FREIGHT) FROM ORDERS Y WHERE Y.ORDERID <= X.ORDERID) AS RUNNING_TOTAL
FROM ORDERS X
ORDER BY X.ORDERID
2. How many internal rows does the Estimated Execution plan say it will use?
3. If the NorthWind.dbo.Orders table above had 10,000 rows, how many internal rows would be generated? (you WILL be surprised)
4. What type of query is the Sub-query in the SELECT list?
5. What type of join does the Sub-query use?
6. Is this a good way to make a running total? Why?
7. What will you say to the next developer that tell's you his Estimated Execution plan for his/her code is better than that of your code?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 12:24 am
Jeff Moden (10/1/2007)
Didn't ask what the decimal value of 24 was... ask which bit would be set. Hint, first bit is called "bit 1" or "bit 0" depending on which "standard" you want to follow... so, there are two answers.
Ah. Misread that. Bit 5, I believe (counting from the right, and I don't remember if that's the corect way. Think so. Electonics course was long, long, long ago)
Didn't ask what 14 converted to hex was, asked what the value of 1416 was. To be more clear, what is the decimal value of 1416? Hint, the subscript identifies the base.
Again, a misread. I read it the wrong way round. 14 expressed in base 16 is commonly expressed as E (hex just the common representation for base 16). 1416 expressed in base 10 is 20.
Seems my english skills are what's lacking right now.... 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2007 at 6:39 am
Heh... correct on both problems...
English skills on the part of the people I interviewed was not the problem, though. When you draw 1416 on a white board and say "the 16 represents the base of this number. What is it's value in decimal?", they have a PHD in Mathematics (which I verified in the background check), and they still can't tell you, something is really wrong. Same thing goes for the bit masking problem... the guy had a Masters in Electronics and DBA certs/experience that went around the block!
What's really, really bad is what folks put on their resume... During one interview I asked "Your resume says "I'm a 9 out of 10 on both SQL Server and Oracle." What function do you use to get the system date and time for each RDBMS you mentioned"... and I got the proverbial "deer in headlights" look. The guy supposedly had several years experience in both. How do you go a month without knowing that?
You just gotta wonder where the heck these folks come from.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 6:44 am
Oh, by the way, Gail... we had emailed back an forth a couple of times on examples when the Estimated Execution plans lies like a rug... the Northwind example I posted above is a great one... Estimated Plan looks like it'll be fast as all get out... Actual Execution plans is identical in every way except for the size of the arrows. Hovering over the arrows tells the real story on the Actual plan... Feel free to use that particular example in your "project" if you'd like.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 7:00 am
Jeff Moden (10/1/2007)
Heh... those are fun but I like the real-life questions that teach why or why not to do something... for example...1. How many internal rows will be generated by the following query (assuming the original 830 rows provided by Microsoft)? (Easy to do... run it with Execution Plan turned on and add the number of rows on each arrow on the two originating symbols furthest to the right).
USE NorthWind
GO
SELECT X.ORDERID, FREIGHT, (SELECT SUM(FREIGHT) FROM ORDERS Y WHERE Y.ORDERID <= X.ORDERID) AS RUNNING_TOTAL
FROM ORDERS X
ORDER BY X.ORDERID
2. How many internal rows does the Estimated Execution plan say it will use?
3. If the NorthWind.dbo.Orders table above had 10,000 rows, how many internal rows would be generated? (you WILL be surprised)
4. What type of query is the Sub-query in the SELECT list?
5. What type of join does the Sub-query use?
6. Is this a good way to make a running total? Why?
7. What will you say to the next developer that tell's you his Estimated Execution plan for his/her code is better than that of your code?
OK I'll bite and expose all my weaknesses.... what are the answers?
here's my guesses:
1 & 2:i see the plan has more than 3 times as many rows as the final query, so that was self explanitory.(if you had not rubbed our nose in "look at execution plan" i'd have missed this)
3: i'd assume that it would be similar, 3x + a bit more than the final query, so 30,0000 + rows? am i a moron? did i miss that it'd switch to a table scan and be less or something?
4: OK maybe I'm not up to speed on the naming convetion, but at least I know how to do the query.
5: inner join I'm pretty sure;
6: well it works, but I'd think a group by would do the same thing a little prettier;
it's probably the same execution plan:
SELECT X.ORDERID, x.FREIGHT, SUM(Y.FREIGHT) AS RUNNING_TOTAL
FROM ORDERS Y INNER JOIN ORDERS X ON Y.ORDERID <= X.ORDERID
GROUP BY x.ORDERID,x.FREIGHT
ORDER BY X.ORDERID
7: i'd just want to see the execution plan...if it's better, fine. Not afraid to be corrected and augmented.
Lowell
October 2, 2007 at 7:00 am
Sounds like the guy we had applying for a senior SQL Server DBA position. When we tested him, he couldn't write a select statement that returned the name and surname for all rows in the person table.
He also couldn't name the 3 backup types in sql server, couldn't say what the difference between snapshot and transactional replication was and couldn't say why one would use indexes.
For some strange reason, we never called him for an interview.... 😛
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2007 at 7:45 am
. How many internal rows will be generated by the following query (assuming the original 830 rows provided by Microsoft)? (Easy to do... run it with Execution Plan turned on and add the number of rows on each arrow on the two originating symbols furthest to the right).
USE NorthWind
GO
SELECT X.ORDERID, FREIGHT, (SELECT SUM(FREIGHT) FROM ORDERS Y WHERE Y.ORDERID <= X.ORDERID) AS RUNNING_TOTAL
FROM ORDERS X
ORDER BY X.ORDERID
Answer: 830 + 249 = 1079... but the very skinny "1" arrows on the bottom leg should be a giveaway that something horrible is about to go wrong...
2. How many internal rows does the Actual Execution plan say it will use?
Answer: my appologies... late night... Original question said Estimated Execution plan... I meant ACTUAL Execution plan... using same method you get 830+344865= 345695... not a misprint... check it out for yourself!
3. If the NorthWind.dbo.Orders table above had 10,000 rows, how many internal rows would be generated? (you WILL be surprised)
Answer: The formula for this particlar query would be n + (n2+n)/2 or 830+50005000 = 50005830. Lemme put some commas on that so you can really see it... Doing this type of running total on a lousy 10,000 rows will produce 50,005,830 internal rows.
4. What type of query is the Sub-query in the SELECT list?
Answer: Correlated subquery... "Correlated" because it makes reference outside of itself. With exceptions, can also be described as "Death by SQL" 😛
5. What type of join does the Sub-query use?
It certainly has all the ear markings of a simple Inner Join... that's why this type of join is so insidious... it's really a bit more than half of a full cross-join. These types of joins are known as "Triangular Joins". More on that in a minute...
6. Is this a good way to make a running total? Why?
Obviously not... it forms a half of a Cartesian join which can be thousands of times worse than a cursor (but you still shouldn't use a cursor for this... use the proprietary UPDATE for SQL Server... UPDATE table SET variable = columnname = formula).
7. What will you say to the next developer that tell's you his Estimated Execution plan for his/her code is better than that of your code?
Answer: Show me the Actual Plan and the duration of execution, please. Some I/O stats would be helpful as well.
For more information on "Triangular" Joins, please see my explanation in the following thread...
http://www.sqlservercentral.com/Forums/Topic359124-338-1.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2007 at 7:49 am
p.s. If you think a Group By would be better, all I can say is "try it"... I think you'll be surprised at how bad it is...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply