Michael J. Swart

May 5, 2017

Drawing (again) with SQL Server

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 10:41 am

You have my permission to skip this post. This one’s just for me. So I’ve been drawing again with SQL Server’s spatial results tab, the first time I posted something was with Botticelli’s Birth of Venus in More images from the Spatial Results Tab.

Why Michael??

Because it’s a stupid challenge and I wanted to see what I could do with it. The SSMS spatial tab is a lousy crummy medium for images. It really is quite terrible and using SSMS to draw imposes restrictions and rules. It’s fun to see what I can do by staying within that framework. It’s something to push against just because it’s challenging. Others do crosswords, This week, I did this.

Why Now?

I realized a couple things lately.

The Colors Seem Dull … But Don’t Have to Be
I used to think the spatial results tab uses lousy colors, pastel and dull. I realized that they’re not dull, they’re just transparent. I can overlap polygons inside a geometry collection to get more solid colors. Here are the top 100 colors without transparency.


The Colors Seem Arbitrary … But Don’t Have to Be
The palette that SSMS uses is terrible. It’s almost as if the nth color is chosen using something like Color.FromArgb(new Random(n).Next()); Notice that color 6 and 7 (the beige colors on the left side of the grid) are almost indistinguishable from each-other. But I can use that. I can overlap different colors to get the color I need. And I can write a program to pick the best combination of overlaps. Here’s a nice red and blue:


But black remains difficult.

Curves Are Supported Now
I can use arc segments called CIRCULARSTRING. SVG files mostly use Bézier curves which cannot be translated easily to arc segments.
Here’s a logo that I rebuilt using arcs instead of Bézier curves:

For some reason, if you begin to use CIRCULARSTRING, then the transparent colors won’t blend with itself (just other colors).
Also arc segments are rendered as several small line segments anyway, so for my purposes, it’s not a super feature.


One last picture/query of a scarlet macaw. Click on it or any other picture in this post to get the query that generated it.

June 13, 2016

It’s 1966!

Filed under: Tongue In Cheek — Michael J. Swart @ 10:56 pm

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

April 20, 2016

Field and Record vs. Column and Table

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 8:50 am

Erik Darling wrote me last week with this idea:

There’s a sort of recurring company chat joke about people who flip out over rows and columns vs. fields and records, etc. And of course, there’s this outdoorsman magazine called Field & Stream.

And he suggested a parody of Field & Stream called Field & Record. That led directly to this:


Thanks to the team at Brent Ozar Unlimited for the suggestion and the article ideas. You guys are hilarious!

My Two Cents on the Debate

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

I dare someone to tell me I used the word ironically wrong.

April 1, 2015

Some Tweets I Drew

Filed under: SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 8:00 am

Last week I asked for people to give me illustration ideas on twitter using the hashtag #SwartDrawsTweets. Thanks for everyone who gave me suggestions. Here are a few of them.

The Stig

Brent Ozar tweeted:

(Sorry Richie. Use the hashtag!)
What will he do next. The world wants to know

The Drive-thru

Then Aaron Bertrand tweeted:

@SQLBrit and other left-side drivers are probably wondering about that sign.

M. C. Escher

Ken Fisher had this idea

He probably had a different idea in mind, but I’ve always loved Escher’s Bond of Union.
Maybe not impossible, but linked servers are rarely part of a good solution

Bonus Regional Illustration

And of course I saved the best for last which all Canadians will get:
Regional filtering is a bizarre youtube feature. It's even more bizarre that people use it.

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.

March 18, 2015

Swart Draws Your Tweets

Filed under: Tongue In Cheek — Michael J. Swart @ 11:22 am

Well, it’s happened, I’ve (temporarily) run out of blog post ideas. So I want to do something lighthearted for next week and I want you to get involved.

I’m going to draw your tweets. Here’s what you do.

  • Think of something funny that could be even funnier in comic form.
  • Then tweet it with the hashtag #SwartDrawsTweets
  • Alternatively leave a comment below.

On March 25th, I’ll pick my favorites and draw them and post them the following week.

Tweet all you want, I’ll be picking a selection based on humour, variety, SQL-Server-relatedness, and my whim.

Here are some previous examples:

Robert L. Davis (@SQLSoldier) wanted to warn people about the dangers of shrinking:
more and more tribbles

Brent Ozar (@BrentO) suggested that he liked to think of the Resource Governor as Colonel Sanders:
Colonel Sanders spoof

and Karen Lopez (@DataChick) had an idea for metadata:
It's not a doll, it's an action figure!

September 13, 2013

Without ORDER BY, You Can’t Depend On the Order of Results

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 10:25 am

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.

SQL Blog Impressions:

As Brent Ozar

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.

As Pinal Dave

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!]

As Aaron Bertrand

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.

As Paul White

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.

As Karen Lopez

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?

As Paul Randal

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

July 29, 2013

Lessons From Sherlock Holmes

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 11:41 pm

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.

BBC's Sherlock and John Watson

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.

If you’re new to some environment, you can’t go wrong with Kendra Little’s checklist

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

So how about me? Who do I turn to when I need outside help? It’s #sqlhelp and stackoverflow (or dba.stackexchange) to the rescue. Those are still the best forums for SQL Server issues. And I’ve said it before; here’s my post about three problem solving resources that make you look like a genius.

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

Some anti-quotes

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

Your Turn

Do you have any Sherlock Holmes moments? Give me your best one.


November 22, 2012

Creativity, Birthdays and Were Snarf

Filed under: Miscelleaneous SQL,Tongue In Cheek — Michael J. Swart @ 12:00 pm

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:

Were Snarf

Were Snarf by Brandon Oliver

My Wish

So for my birthday, I don’t want anything. But if you want to, you could do me a favor: Pick one if you’ve got time.

  • Do something creative and then post a link in the comments
  • Or if you don’t have time, think of something cool (and nerdy/database-y as you wanna be). I’ll pick the two or three coolest to draw and post here next week

August 10, 2012

Avoiding Problems

Filed under: SQLServerPedia Syndication,Technical Articles,Tongue In Cheek — Michael J. Swart @ 8:00 am

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.

Taking Responsibility

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…

May 14, 2012

Lessons From Geordi La Forge

Filed under: Data Cartoons,Miscelleaneous SQL,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 11:56 am

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?

Picture of Geordi Laforge captioned W W G L F D

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:

Can you fix the neutrino flux generator? ==> either "I can fix that captain. Estimated time two minutes." or "Sorry captain, The flux couplers aren't in phase."

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)?

Older Posts »

Powered by WordPress