February 1, 2014 at 6:43 pm
Heh... correct. :blush: I really don't have 2K12 installed anywhere to mess with. I have a copy of the Developer's Edition sitting on my desk and I haven't taken the time to install it for some reason.
Shifting gears, I seem to remember that thread, now that you mention some of the participants. I don't remember how long ago it was, though. Seems like yesterday and, at the same time, seems like a million years ago. My, how time flies.
As for a book (thank you for the nice compliment :blush:), I've started one several times and then something happens and I don't get back to it for a while. When I do finally get back to it, I end up changing it and end up no further than I was before. Guess I need to stop "pre-optimizing" such a thing.
--Jeff Moden
February 1, 2014 at 7:07 pm
Jeff Moden (2/1/2014)
Heh... correct. :blush: I really don't have 2K12 installed anywhere to mess with. I have a copy of the Developer's Edition sitting on my desk and I haven't taken the time to install it for some reason.Shifting gears, I seem to remember that thread, now that you mention some of the participants. I don't remember how long ago it was, though. Seems like yesterday and, at the same time, seems like a million years ago. My, how time flies.
As for a book (thank you for the nice compliment :blush:), I've started one several times and then something happens and I don't get back to it for a while. When I do finally get back to it, I end up changing it and end up no further than I was before. Guess I need to stop "pre-optimizing" such a thing.
For crying out loud man, you could paste the things you write here into a book, and I'd pay for it. There is more wisdom in you asking for DDL and sample data than there is in some of the other books I've read ("You want to learn SQL? Its's HARD! There are TABLES! Can you type? If you can't, practice typing and come back to this book...").
People like me, and I'm sure many others, owe a serious intellectual and professional debt to you and the other regular posters here that are so constantly helpful. There's no way I would have gone from being an accidental SQL developer to landing my first DBA job within three years without this community (and a few others like it). You guys (and Gail!) are amazing.
February 1, 2014 at 8:10 pm
Heh... my Dad said something similar. "More than 30 articles? Sounds like a 30 chapter book to me".
There's no way I would have gone from being an accidental SQL developer to landing my first DBA job within three years without this community...
Congratulations on that! Well done!
I feel the same way. Ya just gotta love this community! I learn something new here every day.
--Jeff Moden
February 1, 2014 at 9:20 pm
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.
February 2, 2014 at 3:34 am
Jeff Moden (2/1/2014)
There is a method that'll blow the doors off of even LAG but a lot of people are afraid of it.
Thus are you referrring to CROSS APPLY here?
Regarding CROSS APPLY, I have a similare feeling for it as does sqldriver. It doesn't jump at me and cuddle me when I try to solve problems and it scares me a bit that it gets executed for each row in the main select.
What is needed is some really good experiences with it where you remember the wonderful things CROSS APPLY did to some really cool problems. Your solution may become part of this chapter of experiences in my mind - also because it's less than 200 characters and if you take out the spaces and line feeds it can be twittered.:-D
February 2, 2014 at 4:24 am
Michael Meierruth (2/2/2014)
Jeff Moden (2/1/2014)
There is a method that'll blow the doors off of even LAG but a lot of people are afraid of it.
Thus are you referrring to CROSS APPLY here?
Regarding CROSS APPLY, I have a similare feeling for it as does sqldriver. It doesn't jump at me and cuddle me when I try to solve problems and it scares me a bit that it gets executed for each row in the main select[/i].
What is needed is some really good experiences with it where you remember the wonderful things CROSS APPLY did to some really cool problems. Your solution may become part of this chapter of experiences in my mind - also because it's less than 200 characters and if you take out the spaces and line feeds it can be twittered.:-D
A nested-loops inner join, Michael. Same as most of the ordinary inner joins in most of your queries. Only a merge join is quicker (it depends, of course). CROSS APPLY with a table reference in it somewhere is like a parameterised inner join with a built-in toolkit for doing stuff, making it incredibly flexible. CROSS APPLY without a table reference in it simply means "calculate".
I think Jeff is referring to the quirky update - the super-fast running totals method.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 2, 2014 at 8:46 am
Michael Meierruth (2/2/2014)
Jeff Moden (2/1/2014)
There is a method that'll blow the doors off of even LAG but a lot of people are afraid of it.
Thus are you referrring to CROSS APPLY here?
Regarding CROSS APPLY, I have a similare feeling for it as does sqldriver. It doesn't jump at me and cuddle me when I try to solve problems and it scares me a bit that it gets executed for each row in the main select.
What is needed is some really good experiences with it where you remember the wonderful things CROSS APPLY did to some really cool problems. Your solution may become part of this chapter of experiences in my mind - also because it's less than 200 characters and if you take out the spaces and line feeds it can be twittered.:-D
No. Not referring to a CROSS APPLY. Chris is correct. I was referring to the Quirky Update method.
There's a method even faster than that, though, and a lot of people have almost as much fear about it as the Quirky Update method. If there's a guarantee that none of the rows will ever be updated (which can be enforced by an INSTEAD OF trigger) for amount or date or other thing to affect the expected order (such as a checkbook register created by the bank, for example), then a trigger to calculate and store the running balance in the table itself produces absolutely the best query speeds.
As for CROSS APPLY, Chris is also correct on that. Perhaps it would help if there were a simpler explanation, though. CROSS APPLY is really nothing more than a correlated sub-query like any that you might find in a SELECT list. If it's an "Equi-Join", then it's treated more like an Inner Join rather than RBAR. The two big differences between a CROSS APPLY and a correlated sub-query in the SELECT list are that CROSS APPLY was designed to work a bit more effeciently and it can return more than one value whereas a correlated sub-query will return the infamous "sub-query returned more than one row" error. Think of it as the penultimate "for each row" tool that works like setbased code when done correctly. Of course, like anything else, it can be made to be horrible when you use things like Triangular Joins, etc, within the CROSS APPLY just like correlated sub-queries can.
Paul White has two excellent articles on APPLY and may help in understanding.
http://qa.sqlservercentral.com/articles/APPLY/69953/
http://qa.sqlservercentral.com/articles/APPLY/69954/
--Jeff Moden
February 2, 2014 at 8:52 am
sqldriver (2/1/2014)
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.
BWAAA-HAAAA!!!!! I know that guy! He makes some pretty good stuff. The computers are so fast nowadays that it has cut down on the number of sips I can take between runs which means he can only get his kilt dry cleaned once a month instead of every other day. 😛
--Jeff Moden
February 2, 2014 at 10:12 am
Jeff Moden (2/2/2014)
sqldriver (2/1/2014)
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.BWAAA-HAAAA!!!!! I know that guy! He makes some pretty good stuff. The computers are so fast nowadays that it has cut down on the number of sips I can take between runs which means he can only get his kilt dry cleaned once a month instead of every other day. 😛
That makes me think there's a need for a SQL server sipping game. But then again, a dirty kilt probably serves as a good early warning system for the sheep. :Whistling:
February 28, 2014 at 2:24 pm
Jeff Moden (2/2/2014)
sqldriver (2/1/2014)
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.BWAAA-HAAAA!!!!! I know that guy! He makes some pretty good stuff. The computers are so fast nowadays that it has cut down on the number of sips I can take between runs which means he can only get his kilt dry cleaned once a month instead of every other day. 😛
I hope you didn't mean this gentleman. :w00t:
February 28, 2014 at 8:48 pm
sqldriver (2/28/2014)
Jeff Moden (2/2/2014)
sqldriver (2/1/2014)
Yes, Jeff, now give me something to spend all this DBA money on that wasn't bottled and corked by a guy in a kilt.BWAAA-HAAAA!!!!! I know that guy! He makes some pretty good stuff. The computers are so fast nowadays that it has cut down on the number of sips I can take between runs which means he can only get his kilt dry cleaned once a month instead of every other day. 😛
I hope you didn't mean this gentleman. :w00t:
BWAAA-HAAAA!!!!! Most certainly not!
--Jeff Moden
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply