The Junior SQL Developer’s Kick-Start

Target Audience:

Entry-level and junior SQL Server developers seeking “Now what?” guidance beyond introductory T-SQL.


Congratulations on your recent move into the world of SQL Server development!

You’ve got a handle on the basics — SELECT, INSERT, UPDATE, and DELETE — but now what? How do you know what’s important to learn next, and what’s most likely a waste of time?

In this session, we’re going to answer these questions and more. You’ll discover why set-based thinking is the foundation of SQL Server. You’ll learn to use valuable T-SQL building blocks like window functions. I’ll also share a handful of time-saving shortcuts that could save you an insane amount of typing, meaning you don’t have to take another working lunch to clean up a poorly-formatted script.


In addition to learning new skills for today, you need to set yourself up for success next week, next month, and next year. That’s why we’ll also cover two things you can start doing right now to raise the incline of your career, and get you the recognition you’ll deserve.

Journey with me through stories, concepts, and live demos, and you will emerge ready to kick-start your young SQL Server career.

Why I Want to Present This Session:

There’s a saying that goes, “Do you have ten years’ experience, or was it the first year repeated ten times?” My first eight years working with SQL Server were mostly wasted on learning the same things over and over, and not knowing what to learn next. I want to spare as many people as possible from this hamster wheel of ignorance. As a senior developer, I now know what steps the 2002-2010 Doug could have taken to learn more and advance his career much faster.

I didn’t have to suck that long, and neither do you.

Audio podcast:

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS
Leave us a review in iTunes

Additional Resources:

Session Transcript:

Brent Ozar: Alright ladies and gentlemen, in this session at GroupBy, Doug Lane will be talking about the junior developer’s handbook. Take it away, Doug.

Doug Lane: [crosstalk] the abstract is called the junior developer’s kickstart, and I was too lazy to update the title, but take your pick.

Brent Ozar: I like it.

Doug Lane: Alright, so welcome everybody. My name is Doug Lane, and for eight years I was terrible at my job, and that is the pretext of this whole session. So before we get moving, I do want to ask you all one question. If you’re on the Slack channel, I want you to answer this question for me. As a junior SQL Server developer, I’m struggling with, and then fill in the blank. So if you would take a minute and just throw me out your answer. What is it that’s holding you back today? And Brent immediately says the senior developers; okay, fair enough.

Andrea Allred: You’re making me think really hard, because I’m like, what is holding me back? It’s probably me.

Doug Lane: This is aimed at junior developers, but if you’ve been around a while or if you’re a DBA and you don’t do much development, by all means, let me know what’s holding you back. Okay, I see struggling with fighting fires, root problems.

Andrea Allred: I like the not knowing what to monitor. That’s a good one.

Doug Lane: Okay, cool, so we’re getting some good answers. And hopefully, some of them will be answered by this session. It looks like – yes, alright, cool. So this is a graph of basically the value that I provided in the places where I’ve worked. So I started my career in IT in 2000. I look MCSE night school and got my first job as a network and database administrator. Now, the fact that those two roles are combined tells you how serious it was, it was at a dot com startup, and I was not very good at what I did. And at the end of 2001, I got a call from a recruiter saying, “Hey, we noticed that you work with this special vendor app, do you want to come do a contract?” I was like, sure, and when I told my boss, he said, “Thank god because I was going to have to lay you off.” So I … but that was kind of hard to take. So I did this contract work for a while, and then I ended up moving out to the Denver area and got a job out there. This was 2003, and I spent a good five years working at this company as sort of a middling web developer, but I kept my eye on SQL Server the whole time. You’ll notice this line is pretty flat. At the end of 2008, I could sense that things weren’t going well and maybe I wasn’t doing the best job I could. And I started to work a little harder, and so there’s a little uptick in that graph where I started to try to learn SQL Server a little bit more aggressively. Unfortunately, that was too late because January 2009, my boss called me in and said, “We’re going to let you go.” This was really hard news for me to take because unlike the first time where I almost got laid off, I was now a homeowner, and I had a two-year-old son and I’d found out weeks before that my wife was pregnant. So I felt the weight of the world on my shoulders at that point, and I did what any rational person would do, I took a vacation. This is actually an already planned trip, I took my son down to see my parents and we went to South Padre island. I tried to figure out what I was doing wrong. It was at that point that I had one of two revelations. I realized that I really had to start doing great work. This flat line was not going to get me any more in life and I was always going to be a target for being cut loose. So I decided to work harder, I decided to more aggressively learn, I decided to start attending group meetings, SQL Server user group meetings and the like, and what happened was what I was able to deliver for the people that I work for took off.

I was better able to communicate the ideas that I had and the ideas that I had weren’t terrible. I actually knew what I was talking about. And so it was a big deal for me to kind of come off that low trajectory here. The other revelation that I had a couple of years later was I had to start telling people about the work that I do. Now, this took many forms. I did it through like, presenting at user groups and SQL Saturdays, I started blogging a little bit more, I’ve never really had great blog traction, but I did do some blogging, so I had a public profile that way as well, and I even made some videos along the way too. But the difference is dramatic when you look at that slope. It’s huge. Now, this second line is basically my … Now, I’m going to be transparent with you in this session, but I’m not going to be too transparent. I’m not going to say what that line represents in terms of actual money, but I will show you that basically my income was very steadily but slowly rising over that time as well because you know, if I’m not doing great work, no one’s going to really want to reward me for it, right? So here are the two lines again, this is where I started doing better work, and I started to tell people about it. And that’s what happened to my compensation. It got better as well. Now, the line isn’t all that dramatic. So here’s the skill or the value that I delivered to my companies, the blue line is before, the red line is after. You can see there’s a pretty dramatic gap there. I call that the value gap, because that’s the difference between what you are on a path to be doing for the people that you work for, and what you could be doing for the people that you work for, and I want to emphasize here that there is nothing incredibly special about me. We all have our own unique gifts and talents and ways of looking at the world. So this is not a me story, this is an everyone story. This is not something that only a handful of people are capable of. It’s a pretty simple formula that anyone can follow.

And here is the trajectory of my compensation, what I was getting back for the value that I was providing. Now, there’s not a big gap here compared – I’m sorry, there it is. There’s not a big gap there, but there are things in that gap that can really matter to you. This could be moving out from an apartment into your first home, it could be you know, a new car, a better engagement ring, it could mean donating more to charity, it could be any number of things that this frees you up for. Now, the value gap is bigger than the compensation gap, and this is the way we want it. We want you to be delivering more value to your company than they are compensating you for. Now, that sounds kind of dumb intuitively because you’re like, “Well, doesn’t that mean I’m underpaid?” Well, maybe, but if you’re happy with the compensation you’re getting, does that matter? Probably not. And more importantly, when your company takes a turn down … they’re going to take a look at the people who are overpaid, not the ones that are underpaid, and if you’re delivering massive value to the people you work for, and you’re not overpaid, you’re going to be there as long as you want to be in most cases. Now, there’s no defense against lay offs, it’s 100%, but this comes pretty close.

So what we’re here to do today is set you on a path that will grow your value gap that will make you more of a standout both with what you know and how you communicate that, and make you too good to ignore. That’s the goal here, and we’re going to do that in two parts. Part one, we’re going to talk about how to do great work. What we want to do here is rack up the quick wins because there’s a lot of larger strategic stuff that I could talk about but I want to make sure that you get plenty of things that you can walk away from this session today and start implementing and get that trajectory moving up in terms of your value. Alright, so we got five parts to this part one, we’re going to talk about set-based thinking, we’re going to talk about how to manage dates easier, we’re going to talk about writing new queries, I’m going to introduce you if you haven’t seen them already, to window functions, and then there are a few tricks that I have been doing in the last few years that I would have paid big money to know about in Management Studio in the ten plus years that came before that. They’re very simple, they’re very effective, and there are times where your brain just wants to melt because you’re doing something repetitive or something awful, and this will speed you right through.

Okay, so let’s talk first about set-based thinking. If you’ve come from a web development background or app development background, a lot of times when you interface with the database, you might do something that involves like an ADODB record set, or you know, doing while loops and stuff like that to retrieve one row at a time from SQL Server. SQL Server is not going to be happy with you about this because unlike an application, which is fine with doing things in loops, SQL Server is much happier to just go grab the entire chunk of whatever it is you need and bring it back all at once. An analogy to this would be if you took a bunch of … to the bank, and you wanted to deposit them. If you did that and came up to the teller and said, “Alright, I’m going to fill up this slip, hang on.” You fill it up, pass it over, and it’s for one cent, the teller is going to look at you like, are you kidding me? Just give me the whole wad, right? Don’t do that. So if you’re trying to do things in a loop with SQL Server, you’ll be making that bank teller angry because they’re having to do a transaction for every single penny that you’re handing across, as opposed to saying, “Alright, just give me the 100 bucks. Let’s just be done with this.” And one of the things that when you aren’t used a set-based mentality with SQL Server that happens to you if you’re a developer is you will look at cursors and get excited because that’s a much closer parallel to the way that you’ve been doing things if you were say, a web or app developer. And so one of the things I want to show you now is a way that rather than doing things with a cursor, you can do things in a set-based chunk. So let me switch over here. There we go. Can everybody see that alright?

Andrea Allred: We can see it now.

Doug Lane: Okay, cool. So I’ve got a script here and I’m going to use this sample database and what I’ve got is something that I would have written back in 2005/2007, where if I just want to go through and do one of two things, either update or insert, depending on whether or not a record exists, I’ll do it in this while loop. So I’ve got my declarations here, I’m going to declare a cursor, and then do a select from sales invoices, and I want to get the customer ID and total dry items, because WideWorldImporters is different than AdventureWorks. I don’t know that it’s much better but it’s different, at least. So what I’m going to do is open that cursor and then I’m going to fetch next into those variables. So I’m going and getting these one at a time. While the fetch status is zero, meaning while there’s still something to fetch, I’m going to check and see if in this new table that I’ve got, there’s an existing customer ID, because I want to bring these in to somewhere that I want to kind of match the two tables against. If that exists, I’m going to update it. So I’m just going to take the table that I want to target and update it with the variable that I populated this part of the cursor. If it doesn’t exist, I want to insert it because if it’s not already there, I want to make sure it gets there. And then I’m going to go through this one at a time. This can be a very time consuming process. The larger it gets, the worse it gets, and just as a caveat, there are times and places where cursors are appropriate, but if you’re thinking cursors and you’re a junior developer, odds are you’re not going to want to use a cursor. There’s probably a better way of doing it. So what we can do to clean this up is basically reduce it down to what it is we’re trying to accomplish here. So if we want to get rid of some of this language, we’ll get rid of all the cursor stuff, and look at the logical way that we’re trying to construct this. Basically, what we want to say is if it exists, we want to update it. If it doesn’t exist, we want to insert it. It really is as simple as that.

So once we cut out all the cursor language, and I’m going to spare you the typing, we can end up with something more like this, where we basically just join the two tables and we say, “Alright, I want to look for matches based on the customer IDs.” So I’m going to join sales invoices to the new invoices, and since the new invoices is the table we want to update, we’ll put that one … referenced in the from. Once that runs, we will have updated everything that’s in there, and then we can move on to the insert part. Now, the insert is the same thing – and I lost my join down there, there we go. Okay, so on this bottom one, we’ll just insert sales new invoices and then we’ll use the select and we’ll join it to itself, so it’s kind of trickery because I’m referencing that table twice, but I’m saying, “Alright, look at what is mismatched or I’m sorry, unmatched, between invoices and new invoices and anything that’s not there, I want to pull in to new invoices.” This will go a lot faster, again, the number of rows that you have, the larger that gets, the faster the difference will be between the two. So this is one way that you can go through, and rather than having to loop through and check if something exists and then if it does, update, if it doesn’t, insert it, you can just do two simple statements, the update and the insert. Okay, any questions on that one? 90-minute session, so we got plenty of time for questions.

Andrea Allred: Tara jumped in and said, “Cursors are always the answer.”

Doug Lane: No.

Andrea Allred: That’s fantastic though, I can’t tell you, like when I first started, same thing. I think everyone makes that mistake. Cursors are everything, and then you learn no, SQL likes sets, so that’s awesome.

Doug Lane: Yes, and it’s an easy trap to fall into here. You know, if you’ve come from a background of … doing things you know, in a single iteration at a time, so yes, it’s something I spent years doing wrong and I wish I had known about it. Figure out eventually. Alright, so moving on.

Andrea Allred: Cursor that calls another proc that has a cursor. That’s what Rich found. Yes, sorry, I’ll let you keep going.

Doug Lane: Alright, so you didn’t see this coming, but boom, here it comes. This session comes with a little bit of homework, because if you’re a junior developer and you do want to get better, you’re going to have to take some steps for yourself. It’s not enough to just sit here, listening to what I’m telling you and try to absorb it, and then just get on with your day. So here is your homework. If you have never dealt with moving cursors before, what I want you to do is find a loop in your code. A cursor may even just be a while loop. Find a loop somewhere in the code, replace it with a set-based operation, if that’s possible, and then test it and see how much better it is. This is not something that you should do in production, it’s something you should you know, save off somewhere else where it’s safe to mess around with things obviously, but find a loop, replace it, see how it goes.

Okay, next thing we’re going to talk about is how to work with dates easier. Now, I have written date cart and all kinds of date stuff more times than I can count, but there’s actually a better way of doing …, and that’s with a date table. So a date table is a wonderful thing to have in your databases. It provides you with a single source of information for your organization so if there’s some disagreement about maybe how you abbreviate things or if you need to keep track of when your fiscal year begins and ends, these are things that can all be stored in one place. So when you reference them, you don’t have to say in your code, “Well okay, let’s see, a month is July, August or September, that’s fiscal year, quarter one, if it’s October, November, December, it’s fiscal year quarter two.” That logic everywhere you go. You can simply have what’s the date, and then another column in that date table can say, “Alright, this is second quarter of fiscal year whatever.”

It saves you from having to do the date name date part and a lot of the other date math and operations that you would normally have to code if you didn’t have this table. And it allows you to establish alternate realities. When I say an alternate reality, I am talking about things like fiscal year, you know, things that exist only for your company. So if you wanted to say that 2017 to 2023 is the age of goats, go ahead. You can put whatever you want in there, you can reference that table however you like, and everyone will be on the same page when it comes to what things are called. Now, this is probably the most common thing that I used to do when I didn’t have a date table. I would do cast date name, month, so I try to turn that into December, I would cast the date as a two-character varchar and you know, get the date number and then get the year, just to spit out that number at the bottom. Now, this is an … the more you’ll hate it, and what you can do instead is just pull this out of a date table. So let’s take a look at how we can construct one.

Andrea Allred: I’m going to jump in while you’re switching here. I learned date tables from you and it absolutely saved me. We wanted to compare like the current date to last year’s same date in a sales report and because I did the date table and it had the two dates in there, super easy.

Doug Lane: Sweet, alright. So I wrote a script to create my own date table, but there’s one that exists and you won’t find it in the AdventureWorks regular sample. You’ll find it in the data warehouse one, AdventureWorks DW database. It has something called dim date and I like to use dim date as well because I did work with analysis services for a while and dim is a multidimensional construct where there are fact tables and dimension tables. And the dim is just short for dimension, meaning kind of a lookup table. So I like to call dim date so I don’t have a date table called date, which is a reserve keyword, which you really shouldn’t be using. So mine is called dim date just like AdventureWorks, and what I’m going to do is I’ve got the OLTP version of AdventureWorks on here, I don’t have the data warehouse, so I’m going to build this table from scratch. I want it to start in the year 1899 and I’ll have it end in the year 2100. I’m going to put some fun stuff in there, there’s day name of week, day number of month, day number of year, week number of year, ISO week number of year. This is kind of a fun one. If you’ve ever dealt with this column, the week versus ISO week, there’s a difference because one of them starts on the first day of the year, and one of them spills over from the last week of the previous year, and you can actually end up with week 53. So if you’re going to do anything that involves week, I suggest that you kind of look into the differences between week and ISO week. They’re slightly different, and it may kind of muck up what you’re trying to report on because you may have like January 2nd be the week that looks like it came from the previous year. So just one of those oddities to know about. Also got calendar month, quarter, year, semester, and then a bunch of fiscal stuff that I want to fill in too. And this is one where I actually have to do a loop in order to populate this stuff because the values are always changing. I’m going to fill in my fiscal calculations and do a bunch of other stuff. Okay, so we’ll do the fun part, we’ll run it. Oh shame on me, I changed it. What did I call it? See, I think this broke the last time I gave this presentation, and I fixed it on the fly and I didn’t save it. Shame on me. Alright, let’s do a little debugging here. Alright, in case you’re wondering, what I just did on my keyboard was control K followed by control U to uncomment and then control K and control C to comment. So Management Studio has these things called strings and you can do two keyboard commands in tandem to make something happen, and control K, control C will comment, control K, control U will uncomment. It’s really handy. So it saves you from having to do a whole bunch of dash, dash, dash everywhere.

Alright, so let’s try this again. And this should take in the neighborhood of 30 seconds. But what it’s going to do is it’s going to create all this stuff for me and I’ll have a whole bunch of columns that I wouldn’t normally get. And there it already knows about it. Here’s another keyboard shortcut if you want to keep track of these. Control shift R will refresh Intellisense. So if you ever get the red squigglies and it doesn’t know about changes that you’ve made, you can hit control shift R and it will catch up and kind of refresh it’s cache, so it knows about all the objects that are in your database.

Alright, so let’s see what we got in here. We’ve got a date ID, a … and let me scroll down, you can see the first day of the following month resets, all of these, full calendar date, day of the week, day name of the week, day number of month, day number of year, week number of year, ISO week number of year, and this is where there’s weirdness between the two, because ISO thinks that this Sunday is part of the previous week, and the week number is different from that. So again, SQL Server gets a little goofy with these two. Month full name, month abbreviation, calendar month, there’s all this stuff, and this is just different text representations of the same day. Where this really comes in handy is if you have to do reporting and there’s some argument about whether July is July or JLU or JUL and whether calendar year should come before the number, things like that. If you have to put in labels on a bunch of charts, the people that write reports will love you for having this table because they won’t have to do any of the logic in the labeling, they’ll just say, I want this column name and it’s going to display exactly what I want it to. So there’s all kinds of goodies in here, and you know, as you look across, think of all the date name and date part stuff that you won’t have to write with this. It really is fantastic, and it’s saved me hour upon hour of development work.

Andrea Allred: I love this stuff, it is so cool. Are you going to post this script somewhere? People are asking that.

Doug Lane: Yes, yes, I can post that script.

Andrea Allred: Perfect.

Doug Lane: So the one thing I would caution you on is that it’s kind of sample data in terms of like the fiscal year columns, so if your fiscal year starts in September, you don’t want to deploy that table as is, you’re going to want to go through and you know, fix it up to set things right.

Alright, so it’s your turn. What I want you to do is find out if your team has a date table. They may already have one, which will be pretty sweet. Shame on them for not telling you about it. If they do have it, start using it. If they don’t, I’ll post the script for this one, you can also get the AdventureWorks data warehouse copy, I’ll post a link to that as well. … is just in the Microsoft blogs. They’ve moved over to GitHub, which is – it’s good and bad because you used to be able to just go grab the …, that whole database, but they’ve split things up a little bit into like, SSDT projects and stuff like that. But there’s a dim date CSV file that you can go get, in particular, and I’ll post a link for that later on. And then try it out. Try looking through your code to where you might have a bunch of date name date part stuff and join it to the dim date table. One thing that’s really cool about this too is that you’re working with days, and over the span of even a couple of centuries, you’ve only got – I think it’s around 75,000 rows. You know, one for each day. So you’re not going to join to a massive table … to something that’s going to be pretty quick, and you can index it, however, way you like. But mostly you’ll be joining on the date ID and the full date.

Alright, moving right along, new queries. One of the things that I always wondered about when I was kind of struggling is – and actually, I still kind of wonder it today. So the question never fully goes away, but I wondered what do other people do in terms of new query templates? What is it that I should have when I sit down and I want to write a new script? Whether it’s a new just basically SQL script or if it’s a stored proc, what are other people doing that might save them time, make it easier to read things like that. So what I want to show you is the template that I use now and probably three years, in change, ago, I adopted but it contains basic information that helps me understand what the script does and like, where it’s safe to run and things like that. So I always start mine with a big comment block, and little bit of behind the scenes disclosure, most of this block was inspired by the scripts that we had when I worked for Brent, so you know, where to run it, if it has limitations, things like that, and you’ll see a similar comment block in the open source stuff from the first responder kit. The script name and whether or not I can run it in sections, or if I can just F5 the whole thing, because I want to know if I pop this open, if there are different parts to this script, I may not want everything to run at the same time. I may not want to run one section at all unless it’s an emergency, things like that, but I also don’t want to have to comment things out. Now, there are ways that you can kind of set this up to track people so that they don’t run the whole thing at once; I’m not going to get into that but it’s you just basically throw an error right away if they try to run the whole thing. But what I say here is I’m going to run it in sections or I’m going to run the whole thing. And then I’ll explain what it does. Now, this one is obviously just showing a template for new scripts and procedures, but if it has outputs and things like that you can mention that too, like this is part of a sequence of scripts that you would want to run and so forth.

Limitations, if there’s anything that I need to know about this script, like for example, if it won’t run in versions prior to 2012, if I’m doing a Window function say, those for the most part didn’t exist before SQL Server 2012. Or if I’m referencing a temporal table, that didn’t exist until 2016. So there’s a bunch of stuff that I’m going to want to know about if my query is going to break and I try to run it in you know, different environments. Is it safe for production? I make sure that this is always a question that gets answered in the comment block that starts my script because if there’s one thing that’s going to cause a lot of damage, it’s not knowing whether or not this should have been run in production. Now, hopefully if you’re not supposed to be running any scripts in production, you know, your company has you kind of walled off from that, but if you are able to run things against production, it’s good to have this comment blocking here to let you know that you know, this is really dangerous and for fun and testing purposes only, don’t ever run this against a prod server.

So a little while back I wrote a blog post, it’s the three letters that will make your DBA love you. I’ll just spoil that, it’s use. The one thing your DBA will always want to see at the beginning of a script that you may pass off to them is the use statement. And that’s because a lot of times in a company, you’ll have similar business objects that will end up having similar names across different databases, across different tables, across different views, whatever. And it can get really easy to mix up where that script is supposed to run. You could take a query that would logically run in one database, pull it over to a completely different database, and it will actually run there just because there’s so much overlap between the different logical constructs that you have. So I always start out my scripts with use statements. The other thing that I like to build in is fixed time and I/O on. I don’t have to run this with every iteration; in fact, I don’t really want to. But I like to have it there, and … tell me is as soon as I start trying to script out, I’m going to see how much time it takes in terms of both elapsed time and … time. I’m also going to see how many reads this query is doing, which is important. If you saw Erik Darling’s session immediately before, he was poking around in execution plans and he was also looking at the statistics.

So what we’re going to do is check out this query and let’s say that I’m writing this query from scratch, I’m just doing brand new development here. So what I’m going to do is I’ve already got this prewritten, but I’m going to take some stuff out here and I’m going to make this pretty small. Alright, so I’m going to set stats time I/O on. Alright, that’s done. And now what I’m going to do is I’m going to run this query, and you’ll notice that I’ve constructed in a way that I have a table that I’ve joined to here, the sales order detail, I like to put all of the columns that I’m referencing from any given table on line, or a line, or lines by themselves. Reason is, if I want to comment out this table, I can do it much quicker if all this stuff that came from that table are grouped together. So I’m going to run this. Shame on me, that’s why you put use first people. Alright, get my results back, so down here I see that I got 609 rows, I got 689 logical reads. Now the CPU time there is elapsed time. It’s easy to look at these and not know A, which one matters, and B, what the heck is going on if the CPU time exceeds the elapsed time. This can happen when your query goes parallel, meaning it uses more than one core in order to get the job done. It will actually take your workload, break it off into multiple parts and then reassemble it. When that happens, each thread that it goes down will count its own CPU time, and then that CPU time will get added together as part of the entire plan. So I could have, in this case, it’s less, but say that I have 150 milliseconds. It could be that it decided to use six cores at 25 milliseconds a piece. That would get added up, but the elapsed time may only be 50 milliseconds. What you want to focus on here is the CPU time and the reads. Elapsed time isn’t really up to you. You can influence it but you can’t really control it. Reason is if you have a gigantic dataset, the elapsed time includes the time that it takes the render all this stuff in Management Studio, to basically spit out a spreadsheet of your results. And that can take a while. So if you’re trying to tune up a query or if you’re trying to understand why it’s slow, don’t really focus as much on the elapsed time, worry more about the CPU time.

Okay, so I’ve written this query, and I’ve started with the one table, because I want to establish a baseline of how slow this thing is going to be right out of the gate. Alright, so I’m returning about 100 rows here, and it took me 16 milliseconds and 639 reads. Let’s see what happens when we tack on this second table. Alright, that was pretty quick too, and if you didn’t have stats I/O and time on, you wouldn’t really notice a difference, right? Because if you look down here in the corner, it still says zero seconds, so it went from you know, under a second to under a second. Big whoop. Look over here, we do see that there’s more disconnectivity going on. Now we’ve done another 1100 reads, which you know, we’re fine with that, because it came back quick. And also the CPU time doubled. So whether you saw it or not, the CPU time actually doubled, and this can be a significant thing to keep track of because if you have a query that runs over and over and over and over and just like punches the server repeatedly, you know, hundreds of times a second, this does add up. And notice too that our elapsed time went way up. It went up to 180 milliseconds. I’m willing to bet that has more to do with rendering all of this than anything else, but just the same, we’ll keep going. As I’m adding stuff to my query, I want to see if I’m going to … because right now I’ve doubled my CPU time, but it’s still from my point of view, acceptable, I can deal with a 30 millisecond query. So let’s tack something on here. Let’s do a cross join, and I’m doing this to be basically Toonces the Driving Cat and drive it off the cliff, and I’m going to do a cross join here. So now we’ve got 115000 rows and this is starting to get a little heavier. Now I’m at 110 milliseconds, and it took almost 13 seconds just to return all that stuff. So you can see how that elapsed time delta grows as I’m trying to accumulate this all and bring it back. I could keep going, I could make this worse and worse, but you get the idea. As you’re going through and building a new query, you can have in mind what you want the end result to be, but as you’re doing that, you do want to keep tabs on performance, because if you just write run it and say it’s slow, you’re going to have to deconstruct it going the other way, and it’s just easier to start with the one table, make sure you’re getting the results you expect from that one table, and then build upon that, and watch for when you go off a cliff. When you do finally go off a cliff, it’s either something that you wrote, or something that’s missing on the database side, like say, an index that would make this go a lot faster. And that’s when you get into things like execution plans, which is kind of beyond what we’re going to talk about today. But just the same, that’s kind of the next step for when you are writing a query and trying to make sure that it’s going to perform well.

Okay, there’s always a lag when I tell it to switch. There we go. So now it’s your turn. Just like the date table, I want you to find out if your team has a template for writing new queries because you don’t want to make everyone mad by saying, “Hey, I’ve got this great new template”, and everyone says, “We’ve already been using this other one. Get with the program.” So find out if there’s one already out there. If there’s not, one thing you could do is try the new stored procedure template in template explorer. If you just go to view, you’ll see object explorer, solutions explorer, template explorer, and you can go to the stored procedures folder and you’ll see one there. Use the one that says menu in parenthesis after the new stored procedure, because that one has junk in it. But try that one out. And then if you have important parameters like I strongly recommend that you add safe for production to your comment block at the beginning to specify whether or not you’re going to blow something up if you run it in prod. And anything else that might be pertinent to your organization.

Andrea Allred: I also like to include in my comment block like if it’s a stored procedure or something like that, I like to include like a safe run for that stored procedure, like here’s an example of how you could run it, and it’s a safe customer to run it against, or something like that. So if there’s a problem it can…

Doug Lane: Nice. I hadn’t heard that one before, that’s clever. Okay, so I’m going to talk about window functions. This is like my favorite topic under the sun, I can’t get enough of window functions. Once I discovered them, my life was forever changed permanently. So if you’ve ever tried to do row number in SQL Server, you’ve tried to use a window function. It’s just that way back in 2005 when they introduced row number and rank, people didn’t really think of those as window functions, they just thought of them as row number and rank, because there really weren’t any other counterparts to those. Now we’ve got a whole raft of things you can do with window functions. In terms of what window functions do, look at it this way. It used to be that if you wanted any sort of awareness about the data set that you were returning, you had to do some sort of sub query, you had to come back with a result set and then do another query on top of that to examine the initial results. Now, what you can do is do both in the same result set. Another way of look at this is imagine if you work in a giant office and there’s nothing but cubes everywhere, if you are sitting in your cube, and the walls are too high, you can’t see over them, if someone asks you about what’s in the cube in front or what’s going on in the cube behind or the cube to the left, cube to the right, you don’t know. It’s the same awareness level that like a single cell in your result set would have in SQL Server. But with window functions, you can actually kind of prairie dog and say, “Two rows ahead, that’s what’s going on. Three rows back, that’s what’s going on. And then all together, this is what’s going on in the aggregate.” It’s pretty sweet. It’s a level of awareness that we had to really code around before 2012 came. So I’m going to show you some window functions, and the syntax is really not as complicated as it may seem. There’s a lot going on in terms of what you can do with window functions, but they all follow the same basic structure. So you’re going to need a red Lego and an orange one and a yellow one and a blue one, and what you fill in between are things like column names and numbers. So some of this stuff that you can do with window functions includes row number, rank, lag, which looks behind, lead, which looks ahead in the results set, first value and last value, kind of self-explanatory, but it’ll give you the first and last value in the set, sum and average. And normally when you do sum and average, you have to do some sort of group by, but you don’t here. What you do instead is you specify the partition by and we’ll see examples of that here coming up. So every window function has the function itself and then over, which you’ll always have, and then partition by, perhaps, which tells you, alright, this is the scope of what we’re talking about. This is how far – which values we want to separate by, so well, we’ll look at it in the script. And then there’s order by and rows between. And again, some of these are optional, you don’t have to put them in every window function you do.

Here’s row number as a window function, so what we’re going to do is order by due date, we’re going to partition by due date and customer ID. So what that means, when I say partition by, is that the scope will reset every time I hit a new instance of whatever I’m partitioning by. So if I’m doing row numbers by due date and customer ID, so for today, for customer ID three, I want all the orders and I’m going to number them. As soon as it rolls over to tomorrow, or as soon as I roll over to a new customer ID in my result set, that row number starts over again. And then when I say rows between, you can specify – if you don’t specify anything, it’ll grab all the rows. But if you wanted to say like, I want to look at all the rows behind me up until now, which is great for things like year to date, you can say I want unbounded preceding and current row. So look at all the stuff that happened up until now and then stop. It’s amazingly powerful and it’s incredibly easy to use. Let me pull that up and I’ll show you a script of some window functions here.

So here I’ve got AdventureWorks, I’m going to not make the mistake I made last time, I’m going to run them. What I’m going to do is select customer ID, due date, total due. I’ve got row number, I’ve got the total due. As I’m partitioning this differently, you’ll notice there’s no group by here. I’m doing a sum but with this first one, I’m not going to partition it at all. So basically it’s the same thing as a sum with a group by nothing, or a group by all the detail columns. I’m going to do a sum of all the stuff by due date. So for every due date, I want to rack up a sum. The next one is the customer total due for every day and customer ID combination. I want to find the sum. With lag and lead, what I can do is tell it, I want to look one row back. That’s as far as I want to go. So I’m looking for the previous due and the zero is a little bit different because I’m telling it if there’s not a value there, normally it would return null, and sometimes we want to see that, but if I don’t, I’ll just say, you know what, if there isn’t a previous row, just say zero. And then I’ll do a lead, and I’ll look three rows ahead, so what’s coming up. I’ll say that’s ahead three rows due. There’s first value and last value. Now, there’s a little quirk I’m not going to get into, but with last value, you have to use certain parameters in the rows between or you’re going to get a weird number. But there’s first value and last value, and I’m going to order by due date because whenever you do something where you say first and last, it’s going to need to be able to order this somehow. Same with lag and lead. It’s got to have an order in order to tell you what came before what. And then what’s really sweet is running totals. You can do running totals much easier now with window functions than you used to be able to. So with this, I just want to do a sum of total due and I want to do the order by due date, and then between the unbounded preceding, which means all the way back to the beginning and where we are now, running total. And then you can do other fun stuff like if you say look at stock charts, or you look at any sort of business metric, or you want to say, “Well, how are we over the last 13 months?” for example. For this one, I’m doing the last four rows, and rather than saying unbounded preceding, I’m saying, do just three rows back. So I want to get this, and the three before.

So I’m going to run all this and it doesn’t take very long, but I’m doing a whole lot of thinking, which is wonderful, because it used to be that this was more work for SQL Server and a whole lot more work for you to write. So I’ve got my due date, total due, customer order, grand total, total for the day, total for the day and the customer, date, average, customer date average, previous due, ahead due. And here’s my running total. Last due, and so on.

So there’s a lot of stuff that you can do now calculations wise that you couldn’t do before. Where you really get to cooking with gas is when you put the window functions together with your dim date table. Alright, so I’m going to run this, hopefully, I didn’t break anything. Good, no. Alright, so now I’m looking at calendar year and the total due, quarter to date, because I have quarters in my date table, year to date, all time, running total, remaining future total, which is kind of cool, you can do like current row to unbounded following, which is the opposite of preceding. The percent of that total that remains from this point on, and this is where nulls come into play with that lag function. If you tell it lag and you don’t specify the default value of zero, this is what you’re going to get, a bunch of nulls. Sometimes you want to see that because you want to know exactly where things actually begin in terms of real data, but you can do all sorts of fun stuff by putting together dim date and window functions. Alright, so now it is your turn. So…

Andrea Allred: Before we go to the homework, can I ask you a question?

Doug Lane: Yes.

Andrea Allred: Okay, so this came into the question section. When using a dim date table and joining a table with a date time field, does this cause more problems since you have to convert the date time field to a date in order to join to the dim date table?

Doug Lane: No. No, that’s one that SQL Server will not do an implicit convert on and blow the whole thing up. It’s fine.

Andrea Allred: Okay.

Doug Lane: It’s good with that. So your turn. A few years back, I actually made a couple of videos on windowing functions that were part of the T-SQL level-up course with Brent Ozar Unlimited, and I’m still really happy with them. And now they’re out on YouTube for free, so if you just search Brent Ozar windowing they’ll be the first two results that you get. If you want to know more about window functions, the window functions layout one and syntax is going to be a lot of what we just covered, but there’s also a whole another video on how they perform. So if you want to see how well they do in terms of performance and how many you can pile on before things get bad, that’s a fun video to watch too.

I have a poster at that’s the four parts of the window functions. Back here I had a kind of a slide that alluded to that, but there’s more explanation of how these get pieced together, and in full disclosure, you get it when you sign up for my mailing list, so if you don’t want to deal with that, I understand. But anyway, it’s there if you want it.

Okay, now we’re going to talk about Management Studio speed. These are some of the most … that I’ve had to deal with over the years that if only I had known there was a shortcut for it, and it turns out there actually was. So I’m going to hop over and show you a script that we can mess around with. Alright, so gotten to the script. There’s two things that I want to show you here that are a lot of fun. Number one is when you need to replace multiple lines, replace the same spot on every line. The other thing is when you get garbage script output, like a lot of times you’ll bring it in from some other text editor, something like that, or paste it off the internet, and you’ll get multiple lines in between each line, and it’s infuriating because now you’ve got tons of white space that you want to get rid of but there’s no convenient quick way to do it. I’m going to show you a couple of tricks, how we can clean up a script like that. So I don’t even really need to run this query, it’s more just a matter of cleaning up the … is this is going to drive me nuts, all these go’s because I have some cleanup to do and I can’t do that with all these go’s in the way. So what I want to do is I’m going to find and replace and I was struggling last night to make this little box bigger. I don’t know why it’s so tiny, but anyway, just try to squint with me. What I’m going to do is I’m going to click on this little star, and that means use regular expressions. Now, regular expressions, I have the same reaction that most people do. When I hear that, I just want to run for the hills because I want no part of regular expressions. They’re so weird, they’re so complicated. It’s cringe-worthy. But what I’m going to do is turn this on, and I’m going to look for something very specific. I’m going to look for go, and the letters G O might appear somewhere else in here, I don’t want to mess with that, but what I do want to find are go’s that are sitting on lines by themselves, because I don’t want those anymore. So what I’m going to do is I’m going to type, /R, and already I’m getting stuff highlighter, /N and then I’m going to type go. And now all my go’s are highlighted, along with the line breaks. It’s similar to if you’ve ever done this in Visual Basic, or, it’s going to be – really showing my age by saying Visual Basic there., it would be like VBCRLF. It would be carriage return line feed kind of a thing. So what I’m going to do is replace these all with nothing and now I’m in a much better position to work with all this stuff without all those go’s in the way.

That also frees me up for the second thing that I want to show you, which is alt shift, which just – it was mindboggling when I found out about this. Say we’ve got a problem with this query where we can’t really do a find and replace. Maybe there are four values over here and three columns over here. I could go through and say – I’ll just add currency code twice because I’m lazy. I could go through and add this a second time. I would have to do that for every line, and if you’ve ever had to type anything repeatedly in Management Studio, there’s this part of your brain that starts to malfunction after you’ve done about 20 or 30 lines of that. You just start you know, kind of crazy laughing to yourself. Or maybe it’s just me. But what I want to do here is take what I’ve just done with currency code in the comma and do it all the way down the line. What I’m going to do is hold down – and this is a bad example because I’ve already done that line. I’m going to hold down alt shift and I’m just going to start going down the line, and you’ll notice that this cursor is on every line. Now, when I start typing, I’ve just edited all these lines at the same time. I’m sure you’re wondering, does the reverse work? Yes, it does. Same thing. It’s magnificent.

Another thing that you can do is say we’ve got all of these lines here, and they’re varying lengths. Because it’s not always going to conveniently line up. We can do the same thing, come down to the bottom, we’re going to do alt shift, and then I’m going to hold the control key, and what happens is the control key when I do text operations back and forth, it’s going to rather than do things one character at a time, it’s going to do things in the way that it kind of breaks up words. Now, this doesn’t always work. I probably should have seen that one coming, but a lot of times if you need to break things up quickly, like say you have file names that have no spaces in or anything like that, that’s a good time to make that work. I can put that all back.

Andrea Allred: My favorite is control Z because it undoes everything I’ve just done.

Doug Lane: Yes. So yes, in this case it’s not going to work because you’ve got spaces in there, but if there were no spaces, it would all go kind of uniform back together. Alright, so those tricks alone have saved me hours and hours since I found out about them. I had to use them a lot, in fact, I used them recently once where I had to restore a whole ton of log files in order, and I had to go down the list and they were in a different location, so I had to change the drive that they were on; rather than do one at a time or do a search and replace I was able to just go through and cut through them all.

One more thing that I can show you, and this is of limited value. I’ve yet to find a use case for it, but if you hold down the alt key and you click and drag, you can do sort of a freeform box. And normally, if you’d clicked and dragged from here you’d get line after line, so it’s just sort of a fun thing. Someday, I’ll find a use for it and I’ll be thrilled that I knew about it, but until that day comes I’m just going to file that one away.

Andrea Allred: Okay, can you repeat the keys that you use to do it word by word?

Doug Lane: Word by word? So what I did was I did an alt shift, and then I did control and delete, which basically means I want to suck – let’s see, I want to suck things in word by word until they’re gone.

Andrea Allred: That’s awesome. Everyone’s talking about how their mind’s been blown.

Doug Lane: Cool. Alright, so write down that stuff and just keep it when – someday you’re going to use it and you’re going to love the fact that you had it, and rather than look it up, write on a sticky note, just put it somewhere on your desk where you’ll be able to reference that. okay, so we’ve been going for about an hour now, I had a bonus in here, I don’t think I’m going to have time to get to it. But what I’ll say is real quick, there’s something called SARGability that will blow up your queries, and I’ll explain in 60 seconds how this works. If you’ve ever gone to church, funeral, or if you just go to church regularly, or wedding, whatever, there are hymnals, there are books that have numbered songs in them, right? And whenever people open their hymnals, they say, “We’re going to turn to page, you know, whatever, to read or to sing hymnal number 455.” When you look through that hymnal, if it’s referenced by the number, you’re going to be able to find it pretty quick because you’ll know, alright, I’m getting closer, I’m getting closer, there it is, and then you can start singing. Now, I know half the church will be late in doing this because it’s always a slow process, but what happens when you tell your people to sing a hymnal that has the word glory in it? How am I supposed to find that? It’s the same thing when SQL Server has to look for something where you say, for example, I want you to find all the things that have percent sign glory percent sign. Is SQL Server going to be able to do that quickly? No. It’s going to have to go through and look at the title of every song in that book because there’s no way for it to quickly reference, alright, do I need to move further ahead to find them all or do I need to move back to find them all? Could be anywhere. So there are things that you can do with your query. If there’s any way that you can work around doing just an open string like that, by all means, do it. If there’s a way that you can avoid putting things like date name in your where clause, or date part, date add, anything like that in your where clause, where it can’t immediately look at that and say, “I know that it’s talking about and I know how to find that”. There’s so much more I could say about it but I’m running out of time for that part of the whole presentation, so I’ll just leave it at that. SARGability, it’s something that you definitely want to look into because it’s the way that you can tune your queries better without having to deal with doing any sort of like, new indexes or anything like that. If you try and create new indexes to work around a non-SARGable query, it’s going to be slow. So I wish I could say more about that but we got to move on.

Okay, part two, this is the point where there are switch tactics, and we’re going to talk less about what you can do today and talk more about setting yourself up to be indispensible, to be too good to ignore, to have a huge value gap. One of the things that most people have trouble with, and I was definitely in this camp as well, is that it’s hard to talk about what you do in a way that comes across as not bragging. So I’ve got a couple of quotes here, hopefully, they will help you out. I don’t just want you to take it from me. So the first one, “The fact is, good work rarely speaks for itself. Managers are surrounded by hundreds of shiny objects seeking to grab their attention. Good work needs a little marketing.” And this is from a Harvard Business Review. This is another quote that I think you should hear. This is from a blog post at Medium. “People rarely go out of their way to recognize the work of others. Everyone needs to promote themselves more. Women are especially bad at that because we think it’s ‘bragging’. Screw that. Brag away. No one knows what you’ve done at your desk and how important it was unless you tell them. Not only is it good for your career, it’s often useful information. Your co-workers will learn of your strengths and will know they can come to you for help.” This is Donna Malayeri, and she was writing about her experiences of being a female program manager at Microsoft. So I’m not just telling you this, there are a lot of other people in the world that have figured out that you can’t stay silent about the work you do. Good work done in anonymity is never going to get you anywhere.

So we’re going to do two things today. We’re going to start a success log and you can call it a progress log, you can call it whatever you want. I like to call mine a success log because I just like to look at it and feel good about it. And then we’re going to talk about how to spread the word. Talking about your own work isn’t easy, so I’m going to give you a couple of tips that will hopefully make that a little bit easier. I like to keep things like charts and numbers because you want to be able to quantify the work that you do, and occasionally you just want to editorialize a little bit. So dear diary, today at 4:43, I kicked some ass. You want to be able to file away what you’ve done and how you’ve made a tangible, quantifiable impact to the business that you work for. If you have any sort of monitoring tool, this is a great place to go.

A success log does a number of things for you. It helps you remember how you solved problems. Basically, if you don’t have a work diary, you have to start digging back through email and assuming that you emailed anyone about the problem you solved, figure out, “Okay, what day did I do this? What did it look like? How is it better now?” If you keep sort of a running log of this stuff, you don’t have to go digging for it when it comes time to use it. It builds a case that you are making a difference. It’s one thing – your annual or however often you get a review, and you sit down and you say, “Well, how was the last year?” “It’s good, I feel like I did some stuff. I think we’re in a good place.” What you want to do is actually build that case. No lawyer would show up to the courtroom saying, “Well, I feel like my client’s innocent.” It’s nothing like that. You actually have to provide evidence, and the same is true when you go through this process at work. And hey, a success log is better to look at than Twitter, right?

What do you put in a success log? If you have email from happy people that you’ve helped, this just really has a nice feel-good effect, because there are days you’re going to show up to work and you’re not going to feel like you make a difference, you’re not going to feel like you matter, you’re not going to feel like you know what you’re doing. If you want to be able to kind of cheer yourself up, you can read through these and it will feel good. If you have stories, people love stories. Stories are wonderful. It’s how we connect with each other, it’s how we connect with each other for centuries, millennia, it’s how we pass on from generation to generation great things that have happened. And you want to do great things. Can’t guarantee that your stories will last 3000, 4000 years, but you’ll at least have them around for a little while.

And then screenshots and spreadsheets. Don’t steal that name, it’s a role-playing game I’m working on. I’m kidding. This is what mine looks like. I used to use Evernote, I use OneNote now because I find that copying and pasting stuff is a whole lot easier. Evernote, I used it for a good six, seven years, and I just kind of moved on. The other cool thing is if you have Office at work, you have OneNote, and if you don’t have Office at work but you have an Office 365 subscription, you have OneNote, and it’ll sync all over the place. So what I do is I like to keep a log of what I did in a week, and some bosses I have found like to know what you’re doing on a weekly basis. It’s really easy to scrape that if you work for someone that wants that information, and then just pass it on, say, “This is what I did this week”, and you can paste in screenshots, you can even reference Excel spreadsheets, there’s all kind of funs stuff you can do with OneNote.

Okay, so now it’s your turn. What I want you to do is if you don’t already know these, I want you to find at least three ways where your performance at work is measured. Not feel good stuff, but actually say, “Alright, if you need numbers to know how much better I am than I was …, what are those numbers?” Or, if I’m part of a project and I need to know what quantifies that project, succeeding versus failing, and how to measure my part in that, I need to know that too. In order to establish a base line for these metrics, you need to know what they are. You establish a base line and then you start a success log with daily entries, and I can’t emphasize this enough, it has to be daily because if you skip a day you will forget what you did the day before and you will spend time digging for where the evidence of what you did the day before exists. It’s so much easier to just develop this as a daily habit, write down what you’re doing every day. Anything that hits one of those three metrics, you highlight it so that when it comes time to loop back and see, alright, what have I done over the last month, year, whatever, anything that hit those key points, the things that are most important to the people you work for, you’ll be able to highlight those.

Okay, and last, we need to tell people about what we’re doing. It’s great that you’re keeping a diary now, but if you keep that diary to yourself, it’s not going to help you a whole lot. You might feel good about it, but long run, it’s not going to matter too much. So now you have to start telling people what you’re doing. When you tell people about the work you do, you can do it internally, or you can do it externally. You can do something like you know, sharing in a morning stand up meeting, or you know, a monthly meeting at work about how the team has progressed, and you can talk about the things that you’ve learned. When you do this and you share what you know, you are building credibility, and you can build that … that you work for – sorry, internally for the people you work for, or you can build that externally. Externally, for example, would be a user group, or you can do it just on a blog to whatever audience you might have for that. In either case, people are finding out that you know what you know, and this is what we want. We want people to know that you know what you’re talking about. It will help you, and because this is GroupBy and it’s you know, going to be something you watch on YouTube, I’m going to lower my voice a little bit because you might be watching this at work. This will help you at this job or the next, one of the two. You want to build credibility at your job, but when it comes time to move on to a new job, you want to have the credibility externally to move on to the next job. You want people to know who you are and what you know, so they will find you and they will ask you if you want to work for them. And then the rest will take care of itself.

Now it’s your turn. What I want you to do is develop these two habits. I want you to share something you learned publicly, and when I say publicly, it’s up to you what scope you’re comfortable with. It can be a lunch and learn, it can be presenting a five, ten or even like a full half hour at a user group meeting. Heck, if you’re not going to user group meetings, consider going to user group meetings because they’re a great way to meet people. And you see that all kinds of people can get up and do this. You can too. I want you to share what you learned publicly, and I also want you to publicly thank someone who helped you. Again, do this internally, do it externally, because we’re all in this together. I didn’t learn everything I learned on my own, nobody else did it that way. We all have to depend on each other, and the more gratitude that you show to the other people that have helped you, the more gratitude you’ll get back when you are sharing what you know. And it’s a nice positive tornado that lifts us all up, throws us across the corn field. And then I want you to commit to repeating steps one and two. I want this to be a habit for you because this is what will send that trajectory up. Not just what you know, but you need to share what you know. People need to know this about you. So what I want you to do is do great work and tell people about it. It’s as simple as that. Build these habits up, and then that value gap will grow and people will want you on their team.

That’s all I have for today. Thank you very much for attending. One footnote of all this, and Brent shows up just at the right time, this is great. So I mentioned before, I mentioned before that what I told you about today will make you better at what you do. It will make you more employable; it will also make you really valuable to the people you work for. That’s not insolation against ever being laid off. Now, I’ve been laid off a couple of times. The difference is when I was laid off the second time, I was in a place where I had a much bigger value gap, and I had put myself in a position to be known for what I was known for. The first time I was laid off I was terrified, I can still taste the terror in my mouth when I think about how lost I was. The second time it happened to me, it sucked, but I didn’t feel fear. I felt curiosity, I felt a sense of wonder about what would come next. I didn’t worry about the fact that I was in a good place or not because I knew I was. I had put in the work, I had made myself better, and I told people about the work I was doing, and that made all the difference in the world. I can’t promise you’ll never be laid off, but I can promise you that you will be in a good spot if you are.

Brent Ozar: Nice, excellent presentation. And it’s also not just about getting laid off either. What happens when … find a new place? You know, when you – I know I had horrible stories along and the way up where I had a manager actually in a meeting once announce, he said, “Everyone, you know, Brent’s last day is going to be Monday”, and I was like ,”What?” He goes, “Yes”, he goes, “You resigned.” And I say, “No I did not”, and we had this unbelievable political thing happen after that, and I’m like, “Okay, I got to hit the eject button fast.”

Doug Lane: Wow. Oh hey, one other thing. Before I forget, I wanted to say a huge thank you to everyone that showed up. I did something kind of fun on Twitter last night. Twitter doesn’t have to be a flaming hellscape like Southern California, it can actually be fun. So if you – you don’t even have to follow the SQL Theatre account if you don’t want to, but I made a choose your own adventure Twitter thread, so what I’ll do is I’ll throw that up in Slack. Let me see where my notepad went. There it is.

Andrea Allred: Oh my goodness, oh my goodness, oh my goodness.

Doug Lane: It should only take you – well, it depends on how many times you screw it up, but there you go.

Brent Ozar: SQL Server – Valentine’s day? Valentine’s day. Oh wow, this is…

Doug Lane: [crosstalk] then I felt it was – you know, it would have felt like I missed the boat on that one, so I had to move it to Valentine’s day.

Brent Ozar: That’s pretty smart.

Erik Darling: [crosstalk] romance.

Doug Lane: It was funny too because I did this like late at night last night because I wanted to put it out there when none of the people that actually follow their account would see them, and I got a couple of retweets from like midstream. It was click here to recompile, and it was retweeted, and I was like, “Why would you do that?” Anyway…

Brent Ozar: People got to the exciting part of the thread and they just decided they wanted to retweet it. Well, for those of you who are watching the recording, you’ll be able to get it through the recording page too if you go to, answer through Doug’s site or search for Doug’s session. We’ll put the link in there so you can follow it inside of there as well. Well, thank you very much, Doug for presenting today. Nice job. Virtual applause for Doug there on Slack.

The following two tabs change content below.

Doug Lane

Latest posts by Doug Lane (see all)

Previous Post
Uptight Database Security
Next Post
SQL Server 2017 Community Driven Enhancements

1 Comment. Leave new

Where Can I get your DimDate script ?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.