datetimeoffset Uniqueness

Datetimeoffset is an interesting data type, because there are multiple ways to represent the same value. And I don't mean like using a string to store "1" and "1.0", I mean the fact that I might have inserted a value in one time zone, but need to be query it in a different one.

CREATE TABLE dbo.TimesOffsets (TimeAndDateAndOffset datetimeoffset, CONSTRAINT pkTimesOffset PRIMARY KEY(TimeAndDateAndOffset))

INSERT dbo.TimesOffsets(TimeAndDateAndOffset) 
VALUES ('20210101 00:00 +10:30');

SELECT TimeAndDateAndOffset
FROM dbo.TimesOffsets
WHERE TimeAndDateAndOffset = '20210101 00:30 +11:00';
Insert in one time zone, query in another

Notice that I inserted a value in the time zone UTC+10:30, and queried it back in UTC+11, and the value that it gave me in the results is in UTC+10:30. Clearly the system knows that the value in the table was stored in UTC+10:30, and would store something different if I had told it to insert the value in UTC+11 (feel free to try it and see that it'll return the same value that you inserted, whichever time zone your WHERE clause value is in).

Sort order understands this too. If I insert other rows, it knows what order they are actually in, regardless of where in the world those times happen.

As weird as it looks, this is in chronological order

And as I have a unique index on this, it won't let me insert 00:30 in UTC+11, because 00:00 in UTC+10:30 is already there. It tells me "Msg 2627, Level 14, State 1, Line 19. Violation of PRIMARY KEY constraint 'pkTimesOffset'. Cannot insert duplicate key in object 'dbo.TimesOffsets'. The duplicate key value is (2021-01-01 00:30:00.0000000 +11:00)."

Errors if I try to insert the same time but translated to a different time zone

I'm okay with this. They are definitely the same value, even if they are written differently. The same thing happens with strings in different collations, because they sort to the same value. Müller and Mueller will clash in a table with a German PhoneBook collation (and yes, that's what the German collations are called, complete with a capital 'B' in the middle of 'PhoneBook').

CREATE TABLE dbo.Names (LastName varchar(100) COLLATE German_PhoneBook_CI_AI, CONSTRAINT pkGermanNames PRIMARY KEY(LastName));
INSERT dbo.Names (LastName) VALUES ('Müller');
INSERT dbo.Names (LastName) VALUES ('Mueller');

So you see, the time zone thing isn't about whether they are actually identical or not. It's about whether they are considered the same based on the things that the SQL engine uses to consider them equal. When you're dealing with data that is case-insensitive, it will consider that 'FARLEY' and 'Farley' are the same, and complain if I'm trying to have both those values in a unique index.

And for a fun trick, let's consider what happens with GROUP BY or DISTINCT.

For this, I'm going to first recreate those tables with non-unique indexes. Still using indexes, just non-unique ones.

DROP TABLE IF EXISTS dbo.TimesOffsets;
CREATE TABLE dbo.TimesOffsets (TimeAndDateAndOffset datetimeoffset, INDEX cixTDO CLUSTERED (TimeAndDateAndOffset))
INSERT dbo.TimesOffsets(TimeAndDateAndOffset) 
VALUES ('20210101 00:00 +10:30');
INSERT dbo.TimesOffsets(TimeAndDateAndOffset) 
VALUES ('20210101 00:30 +11:00');
FROM dbo.TimesOffsets
ORDER BY TimeAndDateAndOffset ASC
FROM dbo.TimesOffsets
ORDER BY TimeAndDateAndOffset DESC

And the results show different values according to whether we sort the data ascending or descending.

Different order, different results

All those times someone told you the order of the data didn't affect what the results were, just what order they were in… yeah.

And if I run the script again but insert the rows in the opposite order…

Same, but different

You see, the rows are in the table in the order they were inserted. And when we are moving through the index either forwards or backwards, we're coming across values, and the version of the value is the one that gets seen first. If it moves through it forwards, it'll (probably) come across the first row that was inserted first. If it moves through it backwards, it'll (probably) come across the last row first. I say 'probably', because there are times that it won't quite work like this. Parallelism could make this be different. There may have been index rebuilds, or page splits, or the execution plan might decide to do run things differently.

But the idea is there – those two values are the same, even though they're not.


This post fits into T-SQL Tuesday, hosted this month by Brent Ozar (@brento) on the topic of data types.

Beware the width of the covering range

I'm sure you've heard by now that casting a datetime to a date is still SARGable. Like, that if you have an index on a datetime column (such as DateTimeCol in dbo.SomeTable), then you will get a seek even if you use a predicate like "WHERE CAST(DateTimeCol as date) = @DateParam". Sargability is all about whether your predicates can be used for a Seek, and it's something I've been preaching about for a very long time.

The trouble with this is that casting a datetime column to a date isn't actually a Sargable predicate. It feels like one, but it's not. Either way, I thought I'd write about this for T-SQL Tuesday, which is hosted this month by Brent Ozar (@brento). He asks us to write about our favourite data types, and I figure that talking about the pseudo-sargability of datetime-date conversion fits. (It was either that or write about how a unique index on datetimeoffset complains that '20210101 00:00 +10:30' and '20210101 00:30 +11:00' are the same value, even though they are clearly a little different. Maybe I'll do both… Edit: I did.)

Sargability is about the fact that an index is on the values in a column, and that if you're actually looking for something else, then the index doesn't work. So converting a column from one datatype to another doesn't cut it.

It doesn't even work to add zero to a integer value. That's enough to confuse the Query Optimizer into thinking that an index might not be right (and this is still the case if we use OPTION (RECOMPILE) so that it doesn't try to parameterise the query). It's easy for us to see that the order of things in the index isn't changed by adding a constant value, but the Query Optimizer doesn't look for this. I had a Connect item open for years about this.

The operation on the index is a Scan when I add a "plus zero" to the predicate

So what's special about converting from datetime to date? Well, it's about being able to add a helper predicate to the mix. (This is also how LIKE works against strings.) The Query Optimizer knows that all the values must be in a certain range, so that it can seek to that range. It works out this range using an internal function called GetRangeThroughConvert. It does a good job on this for LIKE, but doesn't do such a good job with dates.

Let me show you.

First I'm going to create a table called Times and put 100,000 rows in it, one per minute from the start of the year until roughly now. It's actually in a couple of days' time, mid-morning on March 11th.


CREATE TABLE dbo.Times (TimeAndDate datetime, CONSTRAINT pkTimes PRIMARY KEY (TimeAndDate));

INSERT dbo.Times (TimeAndDate)
SELECT TOP (100000) DATEADD(minute,ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1,'20210101') 
FROM master..spt_values t1, master..spt_values t2;

Now when I query this table to find all the rows from today, I see an Index Seek and can be happy. "Obviously" this is sargable.

Looks like we've got ourselves some sargability!

But it's not really. The properties of that Clustered Index Seek show that our WHERE clause is being used as the Residual Predicate, and that our Seek Predicate is based on the Expressions created in that Compute Scalar operator.

The properties show there's something else going on

Now, you'll notice that the operators used in that Seek predicate are > and <. Greater-than and less-than. Not Greater-than-or-equal and less-than. This is a range which is exclusive at both ends. And this is interesting because if I want to write out a date range myself, I do it using an inclusive start and an exclusive end, like:

SELECT TimeAndDate
FROM dbo.Times
WHERE TimeAndDate >= '20210309'
AND TimeAndDate < '20210310'
<!-- /wp:shortcode -->

<!-- wp:paragraph -->
<p>So I got to thinking about this. I wasn't quite curious enough to pull up the debugger to try to see what the values were, but I did do some investigation.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Notice in that tooltip I showed earlier, that the "Actual Number of Rows for All Executions" property says 1440. That's the number of minutes in a day, so that makes sense. But the "Number of Rows Read" property is 2879. This means that the Seek Predicate is returning 2879 rows, and then the Residual Predicate (shown as just "Predicate") is filtering these down to the 1440 that actually produce the correct value when converted to the date type.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>2879 is one less than the number of minutes in two days. I figure the "one less" bit is because it's an exclusive-exclusive range. But still it's a range which is twice as big. It's not "a little bit bigger", it's twice as big as it needs to be. To compare this with LIKE:</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>When LIKE knows what values are being used, it doesn't use those ComputeScalar &amp; ConstantScan operators, and we see helper predicates that do an inclusive-exclusive range:</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4311,"align":"center"} -->
<div class="wp-block-image"><figure class="aligncenter"><img src="" alt="" class="wp-image-4311"/><figcaption>Inclusive-exclusive range</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>But when we use a parameter, we have the expressions and an exclusive-exclusive range, but the same number of rows read as returned. </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4310} -->
<figure class="wp-block-image"><img src="" alt="" class="wp-image-4310"/><figcaption>Exclusive-exclusive range, but still the right number of rows read</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>The Query Optimizer clearly understands how to do the range for LIKE. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>But with the date conversion, twice as big.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Back to what that time period range is...</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To figure this out, I started deleting rows from my table. I figured I'll be able to see the range by when the Number of Rows Read value starts dropping.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>First I deleted rows from mid-morning March 10th on:</p>
<!-- /wp:paragraph -->

<!-- wp:shortcode -->

FROM dbo.Times
WHERE TimeAndDate &gt;= '20210310 10:00';

And the size of the range didn't change.

I removed all the datetime values from the 10th and the range didn't change. And all the datetime values from before the 8th. Only when I deleted '20210308 00:01' did the range start shrinking.

Without 00:01 on the 8th, my range to find values from the 9th was smaller.

So the range of my helper predicate hits the day before my date as well as the day I actually want. TWO days. Twice as big.

It certainly sounds like I'd be better off running my query using my own range predicate, typing out my inclusive-exclusive range instead of relying on the supposed sargability of casting as date. When I write this out, my plan becomes a lot more simple-looking, because it doesn't have to work out the range, but the plans tell me the cost is greater!

Looks simpler, but apparently the cost is greater?

It's lying though. It thinks the first query is cheaper because it does a bad job of estimating. It thinks there's only one row being produced when there are actually way more.

It estimates badly to make us think it's cheaper.

To try to even things up, let's try using a parameter for the date in both queries.

And whoa! It's even worse. Now when we do the range ourselves, it thinks it's 93% of the batch, and the simplicity of the plan has gone out the window.

Ugliness prevails

And yet this "more expensive" plan is actually better. The Index Seek looks through 1440 rows to find the 1440 rows, while the CAST query looks through 2879 rows. The estimate on this second version says that over 16,000 rows might be produced (which is what causes it to be 93% of the batch). But if I'm going to be passing 1440 rows through a query, I'd rather guess 16,000 than guess just 1.

The conclusion of all this is to point out that although we often refer casually to the idea that a datetime column cast to a date column is sargable, it's actually not quite, and you're better off handling your ranges yourself.


Adapting the tools of my trade

It's been a while since there was a T-SQL Tuesday about the tools we use, so I figured my response to Mikey Bronowski (@MikeyBronowski)'s T-SQL Tuesday invite should be about things that have become more pertinent in the last twelve months.

I should point out that I rarely need to work from home 2020-style. I'm almost always back at client sites now, because our response to the pandemic here in Australia has been so good and we have no cases in my city. Massive thanks to my compatriots here, who have almost all been very compliant in following the rules so that we can now be back in offices.

But still there were adjustments, and SQL Community activity is still different.

These days I carry an external webcam with me in my bag, because I want one that's better than the one built-in to my laptop and I want to be able to adjust it separately to the angle of my laptop screen. It's a Logitech C615 HD Webcam. My headphones for meetings are Plantronics Focus, which were a speaker gift from one of the big conferences years ago. They're pretty good at not picking up background noise, probably because of the mic arm.

I carry them around these days because more of my meetings are still remote and want my camera on. Back in 2019, if a meeting was remote it didn't tend to involve webcams. That's changed now, perhaps forever.

Without travel being an option, I've found myself thinking more about how I present online. Presenting online used to be just an occasional thing, and typically something that I would only do when needed. But now I find myself talking into my webcam really often. I'm not quite at the stage of buying googly eyes for my webcam (the webcam in that picture is also a Logitech C615) to make it seem more like a person, but I do have my webcam output showing on my laptop screen so that I have at least my own face to talk at. I'm sure talking to yourself is a sign of madness, but I'm really just using my face so that I'm not just looking at my webcam.

I grabbed a whiteboard for home, because I find that writing on a whiteboard helps me work through ideas. I didn't have much wallspace, so it's actually a small whiteboard – similar size to a monitor. But it still helps a bit.

For presenting, I found myself really missing having a large whiteboard or flip chart. These things had become a bit of a staple of my presentation style, and I found they didn't really lend themselves to the online world. I know there are virtual whiteboards available in Teams and Zoom and so on, but I didn't like having to shrink my face down to the bottom corner of the screen when using it – it makes me feel like I'm connecting with the audience less. So I recently worked out a way of using OBS Studio to let me draw on my laptop screen like a glassboard. It also works for displaying PowerPoint bullet points that hover in the air like magic so that people can keep just watching my webcam feed (and all my hand waving), but still see my annotations.

And the idea has caught the imagination of a few people, including Brian Hogan and Scott Hanselman (who promptly made a video about it).

So now I'm still not using slides in my presentations, but I am using PowerPoint, because it responds nicely to drawing with my stylus.

Online presentations are still not really my thing. I'm looking forward to the day when I can safely travel to events and present to rooms of people, and interact with people without a webcam. Being able to draw on the screen doesn't replace being able to draw on a massive piece of paper, and I'm still stuck in one place for the whole thing. But changing the tools of my trade is helping to compensate a bit.


Scrolling credits in Teams or Zoom – for free and so quick and easy

I made a video about how to do this too, at

The basic concept is that once you have a greenscreen application background in place, you can easily show text or drawings or whatever on the screen, superimposed over your webcam feed. I show how to do this in this post: Scott Hanselman also made a video about it at, and used the idea to make scrolling credits, which is a really neat idea that everyone loves.

So once you have the greenscreen part set up…

I figure we all know how to make scrolling text in PowerPoint – you just make a textbox containing what you want to display, move to the side (or bottom or wherever), and give it an Exit animation so that it passes through the screen. Make it slow enough to read, and then you just hit the Preview button.

So quick… so easy… and OBS is free (get it from and you might already have PowerPoint somewhere…

EDIT – STOP THE PRESS, it's even easier to do the credits natively inside OBS…

I can add a Text source that I set to invisible…

…with the credits in the properties…

…add a Scroll filter with vertical or horizontal speed…

…and then when I make this layer visible, the credits scroll past!

So that's even easier than using PowerPoint! But using PowerPoint means I can draw with my stylus, which is the winning feature for me, so I'll use a combination. A PowerPoint layer for drawing and bullet points, and then the OBS Layer for credits.


Presentation trickery: Online glassboard (like Lightboard) but using just free software

I don't know if you've ever seen me present. I like to use whiteboards or flip charts, and that doesn't necessarily translate well to online presentations.

It was at least ten years ago when I had an idea about giving online presentations with a whiteboard, but where the whiteboard would be between me and the camera. A glassboard rather than a whiteboard, obviously, so that I would still be visible through the glass but not obscuring the text. And the image would be mirrored so that the things I wrote would be readable to the audience, given that I'd be drawing on the other side of it. Recently I've found out this is a real thing called Lightboard, using ultra-clear glass and lights to make sure what's drawn glows enough. And considering this seems to have only appeared around 2015 (a good five years after I was musing about the concept), I really should've explored things further.

This picture is from a site belonging to the National University of Singapore, but an image search gives plenty of examples

In a world where technical presentations are more online than in-person, I've struggled a bit with how to give my usual style of presentation. I know I could set up something like this, and I've been tempted, but it would be a lot of effort, and it's not exactly portable, and I just haven't (although I know at least one person who has…)

I know what you're thinking – and that's that tools like Teams do have whiteboards in their meetings. That's not what I want though – in those situations the main screen becomes white (or whatever colour the whiteboard background is), and the bit showing me disappears or shrinks to the corner. It's like sharing a screen. And my style isn't just writing on a whiteboard, it's pointing at the whiteboard, it's gesturing, it's all of that stuff that doesn't work if I'm constrained to the corner of the screen. If I had a greenscreen behind me I could do the "weatherperson" trick of moving in front of the screen to point things out, etc, but it's still not quite what I want.

And so I got to thinking about what could be done.

Enter OBS. That free piece of software that many people use now. Plus, from version 26 on, it can act as a Virtual Camera, so that Teams (or Zoom or GoTo or whatever) can show whatever OBS is doing. So if I figured if I could get this to behave the right way, I would be able to use it in live presentations. The button that appeared in version 26 is in the Controls pane in the bottom right of the OBS window.

There is no mode where I can just use my stylus to draw on the screen where my face is. At least I don't believe there is, but I found a workaround.

The basic concept here is that I use my webcam as a source, but overlay a window capture from an application where I can draw with my stylus (PowerPoint will do, and is good for other reasons too). Then I set the application's background to a chroma key colour (like bright green #00FF00) and filter that out like a traditional greenscreen. I stretch that source in OBS bit so that the main drawing area is over my webcam feed, and all the menus and stuff is outside.

To explain with pictures:

I started with a plain PowerPoint presentation and set the background to bright green. I'm going to leave the PowerPoint application in this mode because I find it behaves better. And as I'm about to draw on it with a stylus, I don't want it to do anything weird by switching into 'presenter mode' or 'annotation mode' or anything like that.

Then in OBS, with the webcam as the bottom layer of the Sources, I add a Window Capture and use the PowerPoint screen.

Now select the WindowCapture and resize it to match the slide to the webcam. This slide is going to disappear though, when we add the filter.

Right-clicking on the "Window Capture" source, I can go to Filters and add a Chroma Key filter, using the default Green. You'll notice that the bits that are green in PowerPoint have gone grey, indicating that they'll be invisible.

Now back in OBS, I see myself again..

…but when I write in PowerPoint, it appears in front of me. (Oh, because I want to point at the things I draw, I flip the webcam horizontally. You might have noticed the writing on my shirt is backwards.)

And it's not just text that works. I can have standard bullet-point text.

And I can write in other colours too, just not green.

But that brings me to an interesting set of tricks.

The first one is to grab a screenshot of me on the webcam, and make it very saturated, and green. I can do this using just about any image editing tool. So now I have an image that looks like this.

I set this to my PowerPoint background, and I can easily see where I can draw and where I can't (assuming I don't move around too much).

I can also add gridlines to help my handwriting stay neater, and help me make sure I don't run out of room.

But in many ways the biggest trick is around the flow of my presentation.

Part of why I use whiteboards and flipcharts is because I feel like they help the audience connect with me better. There's something about writing live that means that people feel like I'm doing it on the fly, being more responsive to how the audience is responding, changing tack as I go.

But it's not like that at all. When I present without slides, or even without a computer, I have to know my material really well. I have to know where I'm going. I don't have the crutch of a slide deck of bullet points. I need to know what I'm planning to write. How I'm going to make the various points. Even how the audience is likely to respond to various things. By knowing the narrative of my presentation really well, it gives me the freedom to move around the content if I feel like I need to, but I have to know my anchor points in my head, because I don't have them on the screen.

So… as long as they're still green, and therefore invisible to the audience, I can indeed have them on the screen now. And whether I use consecutive slides with different colours (first green, then white/black/whatever) to make it look like it's building, or whether I trace over the top using my stylus, I can have all the notes I like – even notes to myself that the audience can't see. I can have as many slides as I want and can move around them just like regular folk do.

When it comes to giving demos, I still need to add a separate screen or do traditional screensharing through Teams or GoTo or Zoom or whatever. But with a bit more effort I could do my demo in the background and still draw over the top. Or get a physical greenscreen behind me so that I can have a solid background or demo screen behind me and still have my drawings in the foreground. This can definitely go a lot further.

But for my whiteboard-based presentations, this should work nicely.


PS: I just made a short video at to show drawing on the screen. It would've been better if I had taken a few minutes to sort out my lighting and background, but you can see the rough concept there.

My other job

I don't tend to take many holidays. So when James McGillivray (@JamesMcG_MSBI) prompted this month for a post about taking breaks I felt a little underqualified. I could write about how one of the reasons I enjoy my car is the way that I can separate from everything for a bit, but I think I'm going to write about the week before Christmas when I didn't attend client sites at all, and instead took on a different type of work.

In recent years I've got to know some people that have a company that provides Father Christmases (I feel like that should be "Fathers Christmas", but that sounds odd – maybe just "Santas") for shopping centres, department stores, and so on. Last year at a Christmas party I was at, I mentioned that I had filled in as Father Christmas to give out the "Secret Santa" gifts at a client, and commented that maybe I should look into being a proper Santa. I say "Proper Santa" as if Santa is real, but you know what I mean. They said I should remind them in the middle of the year.

Of course, 2020 was, well, you know. Most places didn't have a Santa, even though Adelaide had no covid cases at all between April and November. I had said I could still be available, but the opportunities were scarce. In mid-November we had some cases hit Adelaide and we were in lockdown for a bit. But then by early December we were clear of the virus again, and I got asked if I could do a 3-hour shift as Santa on the 18th. And then that was followed up with the opportunity to do a 3-hour shift on each day Dec 21-24.

I said yes, and arranged to take a vacation from clients.

I still did some client work that week, but it was definitely a break from normality. And although I got paid for my work as Santa (meaning I could update my LinkedIn profile with "Professional Santa") that all went into a donation to a children's charity.

I wasn't a "sit on a couch and talk to the children that have queued up for a photo" Santa – I was a "walk around and greet people" Santa. Photos were selfies, not staged. At first I was a little disappointed, because it wasn't what I had imagined, but in many ways it was better this way, for quite an unexpected reason.

It wasn't a photo of me in the sign advertising that I'd be there…

I hadn't really noticed this before, but people that pre-book to have photos with Father Christmas are quite privileged. I don't mean that they're lucky to have the honour of sitting with him, I mean that they've been able to pick up the phone and make a booking. Or they've been able to take the time to queue for a long time. That they've been able to spend the money to get a photo.

The people that I was dealing with were just people that were at the shopping centre. People who needed to pick up a few things ahead of Christmas. Or who were stressed about getting some presents for people because they weren't ready yet. On the 24th many of them were dads with kids in tow, muttering "I don't know what we're going to get her".

And so I interacted with people. My elf and I walked around every part of the shopping centre for three hours each day. I waved at everyone I saw. With no cases of the virus in the community anywhere in the country, let alone the state, kids ran up and hugged me. Many of them were totally enchanted, and the wonder in their eyes made it worthwhile.

A picture of my elf and me.

But the one that caught me by surprise was the adults who wanted to interact with me. Not the parents or the shop-owners, who understood the "game", but the ones who were there with their carers because of some disability. The ones who still believed in Father Christmas. Who would never have been able to get a photo with a sit-down Santa, but who were just as excited to see me as any of the children (oh, except one kid who was absolutely beside herself – no one was more excited than her). Some of them I saw every day I was there – I guess an outing to the shops was part of their routine. One gave me a Christmas card on my last day.

The band Wizzard sings "I wish it could be Christmas every day", and part of me would like to recreate that feeling of being Santa more often. The theme from Cheers reminded us that we like to go where everybody knows our name, and when my name was "Santa" everyone really did. Walking through the shopping centre afterwards was strange because people couldn't see me. I would see those same faces, but wasn't able to talk to them. I missed it immediately, and totally understood the character Bob Parr (from The Incredibles) and his feeling of being powerless when not able to help people the extent that he wanted.

The annual PASS conference felt a little like that (as will events like SQLBits when they become in-person again), because I would see a lot of people I knew, but the level of enchantment isn't the same. People are never as excited to see Rob (well, not this Rob anyway) as children are to see Santa. And having lost Gareth Swanepoel to Covid this week, I'm feeling that we all need to see each other in the way that children see Santa. I was on a call with Gareth in the last couple of weeks thinking he had recovered. So we were all able to tell him how good it was that he was still around. We never thought it might be the last time.

So for those people who ask if I'll work as Santa again – yes, I hope so. It's tiring work, and it's so hot in the suit, but the rewards are huge.

I pretended to come down a slide


The other side of the room

In 1910, a little over a year after his presidency ended, Theodore Roosevelt was visiting the Sorbonne in Paris, and he said this:

"It is not the critic who counts; not the man who points out how the strong man stumbles, or where the doer of deeds could have done them better. The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood; who strives valiantly; who errs, who comes short again and again, because there is no effort without error and shortcoming; but who does actually strive to do the deeds; who knows great enthusiasms, the great devotions; who spends himself in a worthy cause; who at the best knows in the end the triumph of high achievement, and who at the worst, if he fails, at least fails while daring greatly, so that his place shall never be with those cold and timid souls who neither know victory nor defeat."

Theodore Roosevelt – 1910

The whole speech that this is part of is remarkable, but this well-quoted paragraph is very significant. I've quoted it for many years as part of my encouragement to those who are considering presenting. I've quoted it in presentations when rallying people to step up to bigger things.

The fact is that there putting yourself into the arena turns you into a different type of person. And for people involved in a community that includes events, that arena includes being a volunteer, organiser, or presenter. It means you're on the other side of the room. No longer just in the audience, but through that "fourth wall" and in view of everyone. You're in the arena.

It puts you into a category where you are no longer just an audience member, happy to watch what's going on but not stepping up. Happy to give feedback on what could be changed, but not putting yourself in the firing line.

And it really is a firing line, because of evals. Critics hurt – especially anonymous ones who feel they can say whatever they like. I've had some really harsh feedback over the years, and I've probably deserved all of it. I've also had excellent feedback, often in the same talks, but the harsh ones are the ones I notice.

And this brings me to the topic of this month's T-SQL Tuesday, hosted by Lisa Bohm (@lisagb_sql). She asks us about what technical things we've learned while presenting.

I want to say that the main things I've learned from presenting are non-technical. That I've learned that Roosevelt's arena is worth being in. That I've learned that critics will be harsh and that it will hurt and that I should learn to pay attention to feedback from the people who are in the arena with me. And that if that's the feedback that I want to look for, that I should look to give that kind of feedback to the people whose presentations I attend. Because I can do it without being anonymous. I can be in the arena with them, fighting with them to help them improve. I can make sure they know I care and that I'm an ally. And that even if the things I say seem harsh, it's because I believe in them.

I want to say all that, but none of it's a technical thing. And that's what Lisa's asking for.

So… from presenting I discovered that there's a shortcut key that creates a new virtual desktop in Windows 10. It's Ctrl+Win+D, and it basically makes everything disappear. When you're in the middle of demoing something and you accidentally do this… well, try it some time. Then use Win+Tab to see how to get back to your real desktop and get rid of the virtual one.

Edit: A friend of mine has pointed out to me that these areas of volunteering, organising, or presenting, are not always accessible to everyone. I understand that, and would prefer that those places be entirely accessible to everyone who wants to be there. If you're not able to step up, please know that it doesn't make you worth any less. But also please ask about ways that you can get involved, because I think you could enjoy it. And if you're excluded somehow, let me know and I'll see if I can help.


Coping with Covid

No, I don't have it. If I did, that would be major news because we haven't had a locally transmitted case in Adelaide for a very long time. But Taiob Ali (@sqlworldwide) asks us to write about it for T-SQL Tuesday this month.

Taiob's post quotes a statistic that 45% of adults feel that their mental health has been impacted negatively by the worry and stress of the pandemic. I definitely see this, even here where there haven't been many cases compared to the rest of the world.

We've had restrictions in Australia. The state of Victoria, where much of my family live, is in the process of removing restrictions, but I don't know when we'll be able to visit there, as the state borders have been closed since it all began. There are family-related things going on that mean that I want to get over for a visit, but the pandemic has prevented that. Let alone the impact on international travel. There's a new family member in the UK, and I have no idea when going there will be an option again.

The impact here has been small compared to the rest of the world. But it's the impact on the rest of the world that I'm feeling. I have friends who have lost family members, and some who have nearly died from the virus itself. I haven't lost anyone directly to the disease, but the impact of it on people who mean a lot to me is huge. I was talking to someone earlier who was saying almost everyone she knows has lost a friend to suicide this year. Given there are rules about the number of people that can attend funerals, the impact is even greater. My family has been impacted by restrictions on hospital visits.

I feel powerless in it all – there isn't exactly much I can do. But this month I can grow a moustache for Movember. The causes being supported this year include prostate cancer, testicular cancer, and mental health / suicide prevention. Obviously cancer is a big deal, but I think mental health is even more important this year.

I already look ridiculous, as you can see from the pictures I've posted on social media. I hate shaving every day, but will persist with it until December is here. If you want to have a conversation with someone who looks like this, please reach out.


How I would re-imagine the PASS organisation

Maybe it's not a re-imagination. Maybe it's a return to what it once was. I don't know.

I'm writing this on my blog because I doubt I'm going to get an audience with the PASS Board of Directors any time soon. I've been relatively vocal about these thoughts for a while, but have never written them up.

And before I start, I want to mention a few things for context. I used to be on the PASS Board of Directors. I served for about six months in the second half of 2011 as an invitee, and then ran for election and served as an elected director for two years, 2012-2013. At that point I didn't re-run. I had discovered that the toll of being on the board from such an incompatible time zone was harder than I wanted it to be, and I didn't feel I was being effective when I had a hard time being physically present at meetings and events. The cost was great, and I didn't feel like my presence on the board was having the impact that the community might have wanted.

One thing I did learn from when I was invited to be on the board was that Microsoft saw PASS as a vehicle to reach the data community. Along with JRJ from the UK and Raoul from Denmark, we had been brought onto the board to help it be more globally aware. To find ways to increase the organisation's global reach, rather than having it just centred on North America. I don't feel like I was effective in helping with that, for reasons I'm not going to go into in this post. Instead I want to focus on that thing that I learned – that Microsoft saw PASS as a vehicle to reach the data community.

Let's be clear – I'm not in the room for the discussions about the future of PASS. I'm not a director, and I'm not running for re-election (because I don't want to have a two-year commitment to those people who might elect me). I offered to be part of discussions, but that hasn't amounted to anything. I do hope those discussions are actually happening, but I'm not in the room.

This post is about making my opinion known in the wider community and opening it up for comment. Maybe some people on the PASS Board will notice and see your comments too.

I think right now, most people associate PASS with their annual event the PASS Summit. But I don't think the PASS Summit is PASS's raison d'être. It's become the main focus of the organisation over the last ten years or so, maybe because it's the primary source of revenue, but I think the reason PASS exists should still be as a vehicle for Microsoft to reach the data community, and for the data community to reach Microsoft.

I've heard from people involved in other data community events – ones that are not PASS-branded – that PASS wouldn't give support because they weren't PASS-branded events. If the goals are to get people along to the PASS Summit, I understand that. If the goals are to reach the wider data community, then it's wrong. From the perspective of building PASS revenue, then protecting the PASS brand is good. From the perspective of being a vehicle between Microsoft and the data community, it's bad. Sadly, this felt consistent with the experience that I had while on the board, and continue to feel as a PASS group leader – that PASS' goals are about promoting Summit, not about the community.

Also, about 13 years ago, Microsoft Australia gathered lots of user group leaders together. Leaders from all the various technologies. One of the things that was communicated that day was that we shouldn't see Microsoft as a monolithic whale, but rather as a pod of dolphins, where each group is doing its own thing, communicating in its own way, but understanding the general direction of the pod. Once upon a time, the SQL Server Product Group might've been a single one of these dolphins – but now there are lots of different groups that might want to interact with the data community. And there are lots of data community groups that want to interact with Microsoft.

So here's how I imagine how PASS could be.

PASS kinda wants to be like in this diagram. Microsoft's way of reaching the data community.

And if this is accurate, if you remove PASS from the world, it looks like this:

And nobody really minds.

If the PASS organisation restricts its definition of "Data Community" to PASS-run events, then that's limiting the reach, and Microsoft will simply go directly to all the other events that run. Events like SQLBits, DPS, DataGrillen, 8kb, GroupBy, all of them. And that's what's been happening over the last dozen years or more.

Let's consider that pod-of-dolphins view of Microsoft. Let's also acknowledge that the Data Community actually means all the different events where people gather and connect and share and learn about data in the Microsoft world.

So now imagine that PASS was a vehicle between all the different data groups within Microsoft and all the different data groups within the community. Now it looks something more like this.

PASS becomes the "Enterprise Service Bus" (to draw on an analogy that's about as old as PASS) to serve as a vehicle between Microsoft and the data community. The various groups within Microsoft that want to reach the community can talk to PASS. The various groups within the data community that want to reach Microsoft can talk to PASS. PASS can be a facilitator, an enabler, a vehicle. Those events want something like the SQL Clinic? Talk to PASS. Those events want a bunch of Microsoft speakers? Talk to PASS. Microsoft wants to get some messaging out about some new thing? Talk to PASS.

In this model, if you remove PASS, it looks like this.

…which is actually what it kind of feels like now. When I run my local user group, I have to figure out who to approach to get to speak. I know quite a lot of people, but if I didn't, I would really struggle. SQLBits, DPS, and all the others have worked hard to establish relationships when a different model of PASS might've enabled it better. And what about new groups that are created within Microsoft? The community doesn't know about those groups, and those groups don't have the relationships with the people that run all these different events.

A model of PASS like this means that PASS is no longer a "Professional Association" of anything. It's about PASS-through communication. It's the Service Broker, enabling conversation. PASS hasn't been a professional association for a very long time, but it can still be a vehicle like this. Money would come from sponsors, particularly Microsoft, rather than events because PASS would be making the logistics between Microsoft and the community smoother. It would provide an actual service to both Microsoft and the community, one that would be paid for by Microsoft and by others who want to be part of the Microsoft + data community conversations. And this service doesn't disappear because of an interruption to the event calendar such as a pandemic, volcanic eruption, or terrorist attack – all things which have been problems before.

This approach also provides a way of letting the community know about events that are coming up that they might want to attend or speak at, because PASS could provide that centralised communication. It could be a central vehicle for other sponsors to reach event organisers (and vice-versa). And it could provide assistance for group leaders to run their groups – not by trying to control everything, but by offering advice. They could offer advice and maybe negotiate discounts for using tools like Sessionize and EventBrite rather than trying to provide all of those services themselves.

PASS would be how you the community reach Microsoft, and how Microsoft reaches you. No matter where in the world you are, and which events you're attending.

Please let me know what you think. Hopefully PASS and Microsoft are watching.


Supermarket Seeks and Scans

At the PASS Summit 2015, I was giving a presentation about Query Plan Operators, and Kalen Delaney (@sqlqueen) was in the audience. She's kind of a big deal in the SQL world – I still remember the first time I met her. It was 2007 and she came up to me and said "I read your blog". I was a little star-struck, but we've been good friends ever since.

In that presentation, I was explaining Seeks and Scans, as I often seem to, and was reminded about the times I wander round the supermarket holding a list of the things I need to get. Because what I'm doing is essentially a Join between my list and the stock in the supermarket. And the way that I implement that join highlights some important ideas in the database world.

Kalen seemed to like my analogy. So much so that over a year later she casually mentioned it on Twitter.

I figured that it was about time that I explained more about this.

Plus, as the topic for this month's T-SQL Tuesday is analogies, hosted by Rob Volk (@sql_r), it's definitely a good time to write about it.

When I'm sent to the supermarket to pick up a lettuce, I know where I'm going. It's in the fruit and vegetables section. I'm good with that. I'll go straight there, pick up the lettuce, and I'm out. I'm not going to wander around – I'm not going wander down the confectionary aisle – I'm just grabbing the lettuce and leaving. This is somewhat like a Seek.

In fact, it's more like a Seek with TOP 1, because there are probably lots of lettuces, and I'm only going to get a single one. That's taking the analogy a little further, but it still works. It's one of the nice things about good analogies, and I totally think this is one of those. If I want to get a lettuce that is a particular quality of lettuce, then I might have to check a few of them before grabbing one (because the supermarket doesn't sort the lettuces into the good ones v the ones that look like they've been there a while), and that's like having to deal with a Residual Predicate. The more fussy I am, the more I might have to look through, and I risk getting no lettuce even if they have some fairly ordinary ones. If I want to specifically get the best lettuce they have (even if it's awful), then I need to do a Top N Sort on all the lettuces. That might be an expensive operation if there are a lot of lettuces.

I mentioned a minute ago that I wasn't going to go down the confectionary aisle. Good thing too, if there's a problem there. I'm sure we can all imagine the times when there's a problem down a particular aisle… analogous to a page corruption in a database, but if I didn't have to go there, then I can still do what I need to without being affected.

What if there's some sort of a crisis going on and I need to buy all I can get of something (I'm not meaning like all the toilet paper – in a crisis, other people might need some too). Like all the Ham & Pineapple Pizzas, because we've been asked to cater for a classroom of kids, and those kids don't understand the world yet. But the supermarket understands the world and only ever stocks like, three of them. I'm totally fine with grabbing all three pizzas and putting them in my shopping basket.

But what if that day they have over-ordered and they have fifty? Suddenly I'm needing more memory – I mean, a bigger basket – and I might need to do something differently. I kinda hope that never happens.

Back to when I have a shopping list, rather than a single item. At this point, I'm wanting to join between everything on my list with the things that match in the supermarket. If it's a short list, it might be best to find one thing, then the next, then the next, and so on. Even if I grab a lettuce and then grab a cabbage, which is right next to the lettuces! If my list is short enough, then that's fine.

When my list is quite long, I'm going to use a different strategy. There comes a time when it's going to be quicker to just walk through the aisles looking for things that are on my list. At first glance that sounds like the "tipping point" with a Seek+Lookup turning into a Scan, but I want to point out that this means we're anticipating having a bunch of rows being pulled into a Nested Loop operator and then doing a Lookup for each one, and that's a Join. Sure, we might decide not to do the join, but I'm looking at the join part for my supermarket analogy.

So if I have a long list I might not want to grab each item individually. Let's think about other options.

One option is to sort the list in my hand into aisle order, which is essentially "section". I know the sort order of the supermarket, so this is fine. I can start with aisle 1, and walk through, keeping my eye out for the things in my list in order. Brilliant. This is a Merge Join. It really is.

And this works pretty well, except that I need to order my shopping list first. That's one of the drawbacks of a Merge Join.

Plus, there are times when I might have picked something up, gone to move to the next section of the supermarket, but then I need to grab something else from that section. So if my sort wasn't down to the point where it's unique in the list, I might need to backtrack, which is really annoying and takes time. Now I'm basically doing a many-to-many join, and a whole ton of efficiency is lost.

Another option is to make sure I can see my whole shopping list, and walk up and down going "Do I need this? Is this on my list?" for every item I come across. At this point I'm doing a Hash Match. It can work, but I need to have that shopping list spread out, and I'm asking myself that question (creating the hash value and doing the probe) about everything.

One nice thing though, is that scenario where I don't know how long the list is because I'm getting text messages as I'm walking in. So I can start spreading out the list, thinking that a Hash Match might work out well, bracing myself for a long walk up and down all the aisles, and then when it turns out the list is short, I can decide to go to each item individually. That's Adaptive, and it's really handy when you don't know how much data you're going to be dealing with.

Shopping in a supermarket is obviously very different to querying a database. But the underlying concepts behind how we pull the right goods from the shelves definitely have some strong similarities, as I hope I've shown here. Analogies can help you learn principles by hanging them on concepts you already know. Maybe next time you go to the supermarket, you'll get a little better at understanding how your queries run.