Fifty years ago this week, Percy Sledge had the number one single with “When A Man Loves A Woman”. I’m going to break my SQL-only rule and write just this once about something besides SQL or data. Call it my NoSQL post.
I’m going to break down the song into parts and grade it. Here’s the song:
Percy Sledge sang his heart out and he’s never sung better since. Which is kind of unfortunate because this was his first hit. Still if you want to leave a legacy, you could do a lot worse than “When A Man Loves A Woman”. He also benefits from retroactive comparison to Michael Bolton’s version. I was never a fan of Michael Bolton. He starts every song 100% full out and he has nowhere to go. Good work Percy Sledge! A+
The organ has a big job in this song, it carries the chord progression (the classic Pachelbel’s Canon one). There’s something really funky about the organ. Somehow it got replaced by the synthesizer in the seventies. Then the synthesizer got cheesy in the eighties (think Van Halen’s Jump) so the sound disappeared after that. Personally, I would welcome an organ comeback. A
Drums, Guitar, Backing Vocals, Bass
Competent, they do their job. But most importantly, they don’t stand out at all. This isn’t their show.B
The horns in this song are the WORST! They don’t really come in until the last twenty seconds but when they do, they’re way too loud. I have a theory that the guy who did the horns was the cousin of the sound mixer in the studio. Wikipedia tells me that the horns are also out of tune. They were re-recorded but the old horns somehow got released on the track anyway. Ugh. So now I can’t not notice them. They make a great soul song just a little less great. F
I’m definitely a descriptivist. Language is always changing and if a word or phrase gets adopted widely enough, it is no longer “wrong” (whatever that means).
So when I hear “Field” and “Record” they’re acceptable to me. But if I’m explaining something, I don’t want to distract from the thing I’m saying. And from that point of view, I try to use “Row” and “Column” because I don’t know anyone who blinks at those terms. In other words
When speaking, I use “row” and “column”
When listening, I do not correct “field” and “record”.
This also means I never use the word “whom” which is a word that has the strange quality of being distracting and correct.
I can think of a couple exceptions
Kendra Little made a list of confusing words or phrases. When terms are confusing and it’s important to be precise. In that case, a correction is necessary.
He probably had a different idea in mind, but I’ve always loved Escher’s Bond of Union.
Bonus Regional Illustration
And of course I saved the best for last which all Canadians will get:
Using These Images
By the way, I really had fun doing these images, so share away. I waive any copyright I have on these three images. Copy them, modify them without attribution wherever you like. Profit if you can. Go nuts.
The title says it all. In a SQL Query, you can’t depend on the order that rows are returned without using the ORDER BY clause.
Years ago, this was one of the first lessons I learned about SQL. Without using ORDER BY I was assuming a particular order for returned rows and things went fine for a while. Then it simply “broke”: the results came back in an unexpected order. We quickly fixed the problem, but the client wanted to know what happened. They asked “What changed?” and the best answer that I could come up with is that our luck ran out. We were never entitled to assume an order to that particular set of results.
That’s all I wanted to say. Hmm… That makes for a short blog post eh?
It bears repeating and so why not. I’m going to re-explain myself. But this time I’ll do it as other bloggers you may or may not follow.
The other day when I got back from walking my dog, I opened my Surface (my Surface Pro, not my Surface RT) and surfed the forums. Someone was wondering about the order of query results that didn’t use an ORDER BY clause. BWAAAAH! You can’t do that. After I cleaned the coffee off my monitor, I replied tactfully that you can’t depend on the order here. If you need to order your results, you need to use the ORDER BY clause. Better yet, sort the records in the app. App server CPU is way cheaper than SQL Server CPU. Sign up here for next Tuesday’s webcast about this very topic.
Kind sir or madam, allow me to welcome you to my humble website where I blog about my SQL Server journey and share with you what I’ve learned. You searched the web for SQL Server answers and clicked on the first link. That’s what brought you here and it’s my sincerest wish that I can help you out with your problem today.
Today I’m exploring the ordering of results when the ORDER BY clause is not used. Let us see what happens:
<One succinct explanation and example later…>
In conclusion, it is a mistake to believe that there is an order to results when the ORDER BY clause is not used.
[Ed: Pinal Dave is so prolific, I shouldn’t have been surprised to find out that he has in fact blogged before on this very topic!]
The ANSI-SQL standard specifies ORDER BY as the only way to sort rows in a query. Without the ORDER BY clause, the rows may be returned in any way that SQL Server sees fit. It’s such an important fact, that I believe Microsoft should dedicate at least 50 per cent of the SQL Server Management Studio splash screen for the purpose of warning you. I’ve created a connect item for it so please go and vote.
Excuse me… I have to go, Microsoft just released a new cumulative update for SQL Server.
Examine a query without an ORDER BY clause; the order of the resulting rows is non-deterministic even though it may seem otherwise. We can tell because the input tree for a query with an ORDER BY clause contains the logical operators LogOp_OrderByCOL or LogOp_OrderByQCOL. The optimizer will then take steps to ensure the correct sort order. It will take advantage of existing indexes. Alternatively, the optimizer can choose to use a sort operator if needed.
Without the ORDER BY statement, the optimizer won’t enforce the order and so the order of returned rows can not be determined. It’s unwise to look at the execution plan and make a guess, your query plan could change at any time. And on top of that, the query optimizer algorithms can change at any version, SP or CU.
Yesterday afternoon I was tweeting about data, Barbies and astronauts, and data about Barbie astronauts. It occurred to me that I really should be focusing on the conference session I was at. Especially since I was only half way through presenting it.
I was at my favourite conference centre and an attendee had just asked whether queries without ORDER BY clauses came back ordered by the Primary Key or the Clustered Key? It was an odd question to get during a talk about data modelling. I explained that the answer is neither: Without an ORDER BY clause, no order is guaranteed. Love your data people! And the order it comes in! Hey, if you’re in Toronto next month
could you feed my cats?
Last week I asked you whether you could depend on the results of a query without an ORDER BY clause.
I’m encouraged that the “No” answer was most prevalent because that is the correct answer. It’s not data corruption, it’s the way that SQL Server works and it has since at least SQL Server 2005 when I worked on the team. So remember the ORDER BY clause if you need to depend on the order (and don’t forget to run DBCC CHECKDB afterwards).
Last year, I wrote the article Lessons From Geordi Laforge and I had a ton of fun doing it. So I’m writing a bit of a follow-up article for another fictional hero of mine, Sherlock Holmes. It’s a fun post for the summer.
When I looked at Laforge’s character, he was a model for a competent engineer. Now when I look at Sherlock Holmes, it isn’t about engineering. It’s about troubleshooting. So the lessons below aren’t strictly about SQL Server, but about troubleshooting in general.
Sherlock Holmes, the famous detective can tell how good a Chinese restaurant is by its door handle. I’m certainly not that good. But I notice that there are a lot of parallels between what he does and what I do when I’m troubleshooting. I may never be a consulting detective, but a consulting troubleshooter? Maybe.
Read on! I quote some favorite lines from Sir Arthur Conan Doyle. Then I talk about how each line applies to me, or at least me when I’m wearing my troubleshooting hat.
Here we go! Enjoy the quotes:
“Eliminate all other factors, and the one which remains must be the truth”
Eliminating causes! Great! I think of this as narrowing down the possible causes.
It’s super helpful to be able to say, “The problem we’re dealing with lies somewhere in this sproc”, or “The problem lies with contention in this area.”
Most people put this into practice subconsciously. Try to think about what you do. When you’re alerted to a database performance issue, do you check sp_whoisactive first? Or do you go straight to sys.dm_os_wait_stats? Both will give quick information that lets you eliminate a whole host of causes. But which do you check first? There’s no right answer. Experience with your environment may give you a good idea about what’s more likely to be useful.
“An outside eye, a second opinion. It’s very useful to me. Really!”
Okay, this one isn’t from Sir Arthur, it’s a line straight from BBC’s Sherlock so it counts.
I agree with the message here. It is useful to get a second set of eyes on a problem, especially if you’ve gotten stuck. Experience has taught me that you should ask for help earlier than later. And sometimes the simple act of explaining a problem brings new understanding!
And that’s your bonus Sherlock Holmes quote “Nothing clears up a case so much as stating it to another person.”
“It is the unofficial force, the Baker Street Irregulars”
Sherlock Holmes has a team of street kids who have really good access to information in London. He pays them for clues and they can often canvass London a lot better than the police can.
“It was easier to know it than to explain why I know it.”
I know what it’s like to understand something completely but not be able to explain it easily. So I kind of know what Sherlock Holmes is getting at here. For me, I think it’s true for two reasons actually.
The first reason is that I’m not that eloquent, but I’m getting better. I think of my own thought processes as visual rather than verbal. So I sometimes think of my writing as a translation of my thoughts and ideas into English.
The second reason is that with practice, the brain takes shortcuts to understanding. Good chess players can look at a game in progress and just see good moves. And so some other SQL Server experts can look at query plans and assess the important information quickly. I’d like to think I’m getting there.
“It is a capital mistake to theorize before you have all the evidence. It biases the judgment.”
I couldn’t have said it better. This is one of the few Sherlock Holmes quotes that gives advice I agree with and explains exactly why that advice is good. We actually see other inspectors in Sherlock mysteries that develop their own (often incorrect) pet theories. These inspectors are often reluctant to give their theories up even when they are shown to be wrong.
At a place I used to work I’ve been burned by making this mistake. Someone asked for a root cause analysis of a problem that wasn’t understood yet. And I said “Well, I really don’t know yet. The reason might be higher use of [some new feature], but I have to check”. The next day I’m misquoted in someone’s email as “Michael says it’s [that new feature]”.
“Mediocrity knows nothing higher than itself, but talent instantly recognizes genius.”
Woo hoo! I guess I’ve got talent, because to be honest, I know tons of people higher than myself. The people I hold in high esteem matches Tom Larock’s rankings pretty closely.
“Data! Data! Data! I can’t make bricks without clay.”
Who talks like that? Even a hundred years ago, and even in England, it’s hard to imagine. I like the sentiment, but it doesn’t sound right to me. The prose is off for some reason I can’t explain.
“You know a conjurer gets no credit when once he has explained his trick.”
The only reason Sherlock Holmes delays explaining himself is because otherwise the story would be over too soon. The plot’s tension would be resolved too early. Me, I’ll explain my “tricks” to everyone and anyone who will listen. I guess someone might avoid transparency for appearances sake. I don’t play that game. Or I don’t think I do, at least not consciously.
Do you have any Sherlock Holmes moments? Give me your best one.
It’s American thanksgiving, and coincidentally my birthday today. I’m here at work (like a sucker) while those of you south of the border are travelling, eating, talking and thanking.
But working is a pretty good consolation prize. The alternative – not working – would definitely be worse and I’m thankful that I’m employed. I work with a great bunch of people. They’re super-creative and I do my best to fit in. What I mean, is that I’m not the best artist where I work. We have professional graphic designers on staff and I stare at what they do with envy.
But it goes beyond that too. I’m not even the best artist in my group. I work with a QA Analyst Brandon Oliver who draws for twxxd. He recently learned that it was my birthday coming up and whether he could draw something for me. I came up with the coolest thing that I could think of on such short notice: I asked “Draw a character that would be to Snarf as Chewbacca is to an Ewok”. (If you understood any of those pop-culture references in that sentence, then congratulations! You’re my kind of people) And Brandon delivered:
Takeaway: None really, I just wanted to tell a few stories and draw a bit
A long time ago, I had an interview and I was asked a question that (I’m guessing) was designed to evaluate how I approached problem solving. It went something like this:
Interviewer: “Say you’re a farmer who has a cow in a fenced field. But the fence is broken and you need a nail to fix it. The nail you need is inside the barn and if you retrieve it you leave the cow free to escape. What do you do?” Me: “I think I would just go fetch the nail. I don’t think the cow will get out” Interviewer: “But you can’t leave the cow alone” Me: “I don’t know. I’ve been around cows and they’re pretty stupid. There’s a good chance they don’t know the fence has a hole in it. It will probably take me under two minutes to get the nail.” Interviewer: “Ohh… You’re a problem-avoider.” Me:!!!
“Problem Avoider”? That label took me by surprise. And even though it wasn’t meant as a criticism, maybe I did feel a little slighted by it. The implication is maybe that I don’t face problems that ought to be faced.
Since then, when I face a technical problem I often ask myself: “Am I a problem avoider?” “Am I refusing to deal with a particular issue, hoping it will go away?” I’d like to believe that the answer to those questions is yes and no respectively: I am a problem-avoider but I do not have my head buried in the sand.
Unapologetic Problem Avoider
Sometimes taking a step back to try another approach makes the problem go away. I was asked something like this recently:
Friend: For this set of data, how do I get the min, max, median values. As well as the value at the 25th percentile and the value at the 75th percentile. Me: Oooh, I know this one…
I googled “NTILE” and was soon looking at the books online topic wondering if this was appropriate. After some thinking I decided to maybe avoid the problem:
Me: How many rows are we talking about? Friend: Umm a couple hundred… maybe up to a thousand max. Me: You can do this in C# right? Just load the entire dataset, sort it and then do index look ups after doing some arithmetic. Friend: I was thinking of that, but I wanted to know your opinion. Me: I think that’s the way to go. SQL Server already has to do the reads. The only thing we’re not saving is the network bandwidth. And that’s not much any way.
Problem avoided. But I think that the important part here is that the problem is still being faced; it’s just being faced in the right place and by the right person.
But there are times when you can’t avoid a problem and you have to take responsibility. I’m not going to preach against procrastination. Mostly because I think Nike already has the best advice.
But I want to talk about procrastination’s second cousin: Doing-fun-work-first. Man, I do that a lot. Probably too much. I still struggle, but lately, my approach has been
to prioritize things and then be as objectively honest about the priorities as possible (no kidding).
Or deal with crummy tasks first in order to “get them out of the way”.
If you got any tips of your own, send them this way…
I’ve been rewatching Star Trek again, I find I relate most to Geordi La Forge. He’s a technical guy. He wears the yellow shirt of engineering instead of the red shirt of command.
So I was inspired to write this light hearted post. I usually find non-technical posts a little wishy-washy (No offense to you Seth-Godin-types). But I liked the way this drawing turned out and I had to use it somehow right?
You may have already seen articles or websites elsewhere that detail a comparison between Star Trek operations and company operations. Well here’s another one. For the moment we’ll sidestep the lessons learned from Picard (on how to be a inspiring leader) and go straight to Geordi. Here are the lessons:
Make Everyone Believe You Can Do Anything …
Here’s how Geordi’s typically responds to requests from the captain:
Did you see what he did there? It’s not a choice between “I can” or “I can’t”. It’s also not a choice between “it’s possible” or “it’s impossible”. If Geordi can accomplish a task, he essentially takes the credit with “I can do this”. If he can’t accomplish the task, then his words imply “It can’t be done.” He never implies that his skills are lacking. So are Geordi’s skills ever lacking? No:
… And Then Make Sure They’re Right …
Even when the pressure’s on and someone calls your bluff. There’s a persistence that can pay off: RIKER: Gentlemen, we’re giving you an assignment. The one thing we don’t want to hear is that it’s impossible. PICARD: I need the transporters to function, despite the hyperonic radiation. LA FORGE: Yeah, but that’s imp… Yes, sir.
and then later: LA FORGE: Captain – we can do it. We can modify the transporters. It’ll take fifteen years and a research team of a hundred…
Most impossible things are actually just really really difficult. For example, recently I asked a question on stackexchange about avoiding downtime during a migration. The first feedback I got was the comment “I don’t think [the downtime] can be avoided” which got a couple up-votes. But it turns out there was a way; it just took a lot of effort (I blogged about that effort in this series). It’s the yellow shirt’s job to explain the “how” and “what” so that the red shirts can make informed choices.
Later on in another episode: LA FORGE: Ferengi codes are damn near impossible to break. PICARD: Gentlemen, I have the utmost confidence in your ability to perform… the impossible.
See! It paid off. But it’s easy for me to give advice that boils down to “be able to do anything” it’s another thing to make it so. How do you get great at what you do?
… By Being Awesome
Easier said than done right?
So Geordi’s got skills, and he uses those skills to build a great career for himself. But where did he get those skills? I figure you can become great at something in a number of ways:
Be talented at it (you can’t change you, but you can change fields)
Hard work. There’s no getting by without hard work. Imagine you’re on vacation sitting on a beach. You’re looking at the hut next to you and see some guy in a chair reading SQL Server Pro on his iPad. You just can’t compete with that guy. I’m not suggesting you read trade magazines on holiday, but are you in a field where you might want to?
It boils down to finding your passion. And I hope you’ve found yours. Geordi is a guy whose passionate about the latest thing in his field. It almost seems like he cares about it more than job security:
PICARD: Warp without warp drive. RIKER: They’re gonna put you out of a job, Geordi. LA FORGE: I hope so, Commander.
Antilessons From Geordi
But I like my role models the same way I like a buffet. I pick and choose the good lessons and leave the rest behind. Here’s a Geordi “lesson” that didn’t make the cut:
LA FORGE: I don’t know, Data, my gut tells me we ought to be listening to what this guy’s trying to tell us. DATA: Your gut? LA FORGE: It’s just a… a feeling, you know, an instinct. Intuition. DATA: But those qualities would interfere with rational judgment, would they not? LA FORGE: You’re right, sometimes they do. DATA: Then… why not rely strictly on the facts? LA FORGE: Because you just can’t rely on the plain and simple facts. Sometimes they lie.
I don’t buy it. I get what he’s trying to say and the sentiment is correct, but the point of view is wrong. Facts don’t lie by definition. When Geordi says that facts lie sometimes, I would say instead that the facts are incomplete, or an assumption has been made incorrectly.
Once I was asked by a developer why his code was throwing the error message:
Table ‘noidentity’ does not have the identity property. Cannot perform SET operation.
even though the table in question was showing that it did have an identity column.
I said show me. And he did. And was it the same table? It was. And was it the same database? Yes. Are you sure? Of course. Can you show me the connection string? Hang on a second…. ohhhh. (Omitted in this mini-dialogue is about 15 minutes of head-scratching on both our parts).
Yes, sometimes the facts lie… but only when they’re not facts.
More Geordi Lessons in The Comments
I’ve put a few more of my favorites in the comment section of this post. Do you have any favorite Geordi lessons (or Star Trek lessons in general)?
So I recently looked through my web statistics and I wanted to share some of my favourite search terms. These are words or phrases that people have searched for and for good or bad, they've wound up here on my site. Google Analytics helps me browse these search terms and looking through them I learned that
Michael is apparently hard to spell
Some people still include question marks in their search queries. It's quaint. I always assume they're asking "Jeeves"
Any way here are my favourites, in no particular order.
Sorry, you've come to the wrong place, random googler.
cartoon cow tossing dog
(strangely enough) You've come to the right place, random googler.
how to avoid swart
HAHAHA! If you find out, let me know.
my software never has bugs. it just develops random features
and pivot tables are like good wine you need to learn how to appreciate them
2 things: (1) How did my site come up for these searches and (2) Can we be best friends?
By the way, the phrase "You've come to the right place" reminds me of Engywook, the toothless scientist from the movie The Neverending Story. He's an expert on the Southern Oracle (it's his speciality). So I include him here in the hopes that I get at least one ironic google hit for "Oracle expert":