My personal databases

This month’s T-SQL Tuesday sees Todd Kleinhans (@toddkleinhans) ask about what we use databases for – outside work. Because obviously as database professionals, we ought to be putting what we preach into practice in our personal lives. We should be leveraging our expertise to make sure that the data with which we live, not just the data with which our clients live, is taking advantage of everything we have to offer.


Except that I’m a consultant. Sure I’m a consultant specialising in the Microsoft Data Platform & Analytics space, but my main priority is looking after the best interest of our customers. Ultimately, that’s what they pay us for – it’s just convenient when it overlaps with our expertise in the Microsoft ecosystem.

Certainly I don’t like to tell my clients to pay for things they shouldn’t be paying for, and that includes my time in building a solution when there’s an off-the-shelf solution.

At home, there are all kinds of data that I’m interested in. I’m interested in my financial transactions, in the car usage, and more. And that’s before I even consider the things that affect the company, which obviously I need to track.

But I have applications for those things. My bank keep track of the financial transactions, and I use Xero for the business. We use Harvest for timesheets, and I track my car usage with an app on my phone. And most of these things provide the analytics that I’m after too. I can look for changes in my spending, or in how project time is tracking against budget, and generally manage my data WITHOUT having to spin up my own databases and creating my own applications. I do this type of work for our customers, but I also know that the best use of my time is not creating applications, but in tuning and troubleshooting, analytics and insight.

I treat my family and my company (and myself) like my customers, and don’t reinvent the wheel for no reason. If there’s a product out there that already does what I need it to do, I’m not going to roll my own. It’s just not in their best interest.


T-SQL Tuesday – the confidence ‘cookie jar’

This month, Shane O’Neill (@SOZDBA) challenges us to write about the “cookie jar” that we reach into to re-energise, to give us back confidence when we’re feeling empty of it. He says we all have times of success that we look back upon to help convince ourselves that we can get through our current difficulties. Past achievements that signal that we can still achieve today.

Hi, my name is Rob Farley, and I’m not that person.

For me, this isn’t how life works. I don’t look back at times that I’ve been successful and therefore assume that things will be okay going forward. I’ve had enough experience to be confident that my past successes are no guarantee of anything in the future. I remember a time in my life when job interviews seemed to go well – I felt quite hireable – and yet not so long afterwards I couldn’t seem to get through an interview at all. If I went in on strength from past experiences, that wasn’t enough for a while, and my experiences then changed and became a negative influence on my thought-patterns.

From a technical perspective, I’ve had to learn that the ways that things were done in the past are no indication of how they will happen in the future, and again I find myself looking at past experiences and realising they have no bearing. Just because I knew something about a particularly technology some years ago doesn’t mean that I’ll have the faintest clue next time that area comes up, because I could just as likely find that my past patterns demonstrate that I’m now simply archaic.

Luckily, this isn’t how I work either.

I don’t look back at achievements and base my opinion of the future on that. Nor do I look at other people’s achievements and base my opinion of their future on that. I’d rather look at myself, or at the qualities of other people, to make those judgement calls.

If I put my confident hat on, I know that I’m good. I’m not overly comfortable saying that. I’m naturally self-deprecating, and concerned about the self-worth of other people, and find that I have to figuratively put on a different hat to make those kinds of statements. It feels like it’s bordering on arrogance, except that arrogance implies that I’m devaluing those around me. Really it’s just self-assurance. Knowing that I will be able to beat whatever the thing is that I’m going through. And I don’t feel always feel it, I just have to decide that I’m going to get up in the morning anyway.

I know I’m good in that I can solve whatever technical thing I’m given. I’ve always had that ability. Sometimes solving something might need me to ask others for help, but I’m okay with that. Sometimes solving something may be the decision to not do various things, maybe finding a workaround instead. But I’m not afraid of technical things.

I’m not afraid of new experiences. I did stand-up comedy a few years ago, and it was good. I don’t mean that I was funny – I mean that the experience was generally positive. Public speaking is nerve-racking, but if the alternative is to not do it, then I’d be worse off.

I am afraid of hurting people, and will choose to avoid those things that I think will tread on people, or subject people to unnecessary risk. In that regard, maybe I’m not much of a risk-taker. I can step out over an edge, but will hold back if the person with me doesn’t want to go for that journey.

I have no problem looking at the past and seeing things that I’ve achieved, but I don’t value those things as much as the achievements of other people. I’m immensely proud of the people I’ve been able to help along their journey, but I don’t look back at particular presentations or blog posts or projects with a sense of pride – that’s reserved for the things that make other people smile.

For my own “cookie jar”, I stand on who I am, on who God says I am, and figure that I can get through somehow. Not because I have done in the past, but because I will in the future.


Why we do what we do

This month, Andy Leonard (@andyleonard) asks why do we do what do. He talks about the fact that money is rarely the only reason people become entrepreneurs, and challenges us to to explain what makes us get up in the morning.

Money is definitely not the driving force in my life. I do feel a burden of providing for my family, and don’t feel like money has ever really been a thing going spare, but I also see money as a tool to bless others rather than as something to hold onto.

For me, I created LobsterPot Solutions to help people. It’s a vehicle so I can serve people in whatever ways that I can – typically through consulting about their data, be it strategy, analytics, intelligence, data integrity, whatever. I feel like people could always do what they do better if only they had better data, if they didn’t have to worry about it, or if they could ask questions of it… all stuff that I can help with. And I have employees because it increases the reach I can have. It means we can help more people.

Of course, the company does have to be financially viable. And the employees need to be looked after in whatever ways they need. This takes money. But all of it is to provide a service to people, to help enable those people through data. Our customers do good things, whether they’re in government agencies, or health care, or charities, or mining, or whatever it is – and they’re trying to do the best they can do. So if we can help them make that best a little better, then we’re doing something right.

That’s my why.


Automation and monitoring – can’t have one without the other

Automation is totally necessary. Otherwise, you’ll be going slightly mad over time, and you’re stuck in your job. I’m not even sure you can afford to take holidays if the stuff you do isn’t automated. Maybe you can train someone else to do it while you’re away, and over those two weeks they’ll wonder why you haven’t automated and then they’ll automate it for you. At that point you’re really in trouble. If someone’s going to automate what you do, that someone should be you.

One reason why people seem reluctant to automate things is about wanting to be sure that the task was done correctly each time. It’s like they don’t trust that something ran successfully unless it’s been eyeballed by someone.

So you automate that too! Otherwise you’ve just changed your job from doing the thing to checking the thing.

And to check that your checking is still working right… well you get the picture. At some point you need to have something eyeballed, but you can roll more and more checks into the one spot, and you can eyeball a report that says “Yup, all 1523 things worked today, and 0 of them failed”. Then go and have a larger impact in your world. You’ve already done those 1523 checks today.

Garry Bargsley asks us what we’re proud that we’ve automated and what our go-to tech for automation is.

Well, these days that tech is PowerShell.

PowerShell because you can do so much with it.

PowerShell because you can schedule it in a variety of ways, including SQL Agent and WIndows Task Scheduler.

PowerShell because it’s so easy to handle errors, using trap or try/catch, which helps with that monitoring.

In fact, it’s been PowerShell for a lot of years now.

It doesn’t matter whether I’m kicking off process for ETL, or running some executable, or some T-SQL, or what. PowerShell just handles it. I can use it to loop through datasets, or work in batches. I can manipulate files and use Azure just as easily as anything else, and it’s all straightforward, and easy enough to teach people about.

Sure, I remember my Unix days, and the power that scripting gave you, but I feel like that was so hard to read, so very obfuscated, compared to the PowerShell. If you’re new to PowerShell and it seems complicated – trust me it’s way simpler than handling everything as text. Go ahead and automate things you do!

But again, you need to monitor it all,. You need to be pushing Success and Failure statuses back to some central repository and report on them. It doesn’t matter how you’re automating your stuff, you need to report on it. Otherwise, someone’s going to ask why it’s not working, and you don’t want to be in the dark.

Oh, and my favourite thing that I’ve automated was probably a bunch of Azure ML stuff, because it was so simple and delivered really meaningful stuff to my customer.


People I influence

This month’s writing task is to describe a time when we influenced someone. Thanks to Jason Brimhall (@sqlrnnr) for hosting. I should’ve got this post completed before now – it’s already nearly an hour past the end of T-SQL Tuesday in the GMT time zone – first time I’ve missed the deadline, but I think grace will hold out until Tuesday is over everywhere in the world.

I’d like to think I influence a lot of people – I’m a community leader (hosted the local SQL UG since 2005) and long-time MVP – but I’m also not comfortable thinking about whether I have influence or not. I can think of people that I hope I influence, but I also wouldn’t want to suggest anyone, just in case I’m wrong!

But there is definitely one group of people that I think I influence, and that is my employees. Over the ten years I’ve had the company there is quite a list of people that I’ve employed. There have been as many as seven in the company at one time, and while we do have turnover, there aren’t too many more than seven that have been with me at some point.

Now, sure I pay these people a salary, so they have to adhere to the company culture to a large degree, but I hire people to increase the reach that I can have, and serve more customers. To that end, it’s important that I influence my employees to be the best they can be in that service, making sure that the company is both viable and effective, leaving a positive impact on the data stories of our customers.

Every one of my current employees, and the vast majority of my former employees, are conference presenters and involved actively in the community. For a while now, I’ve thought that I would rather employ MVPs than be one myself (although I hope this doesn’t actually cause me to lose my status), and I have often looked for opportunity to get my team in front of people. At SQLSaturday Perth a few months ago, all four of us presented (by far the most). At Difinity in 2017, no company – not event Microsoft – had more presenters than us. Recently, my former employee Heidi Hasting did amazingly well at Speaker Idol at the PASS Summit, although presenting is something she’s only got into since leaving the company.

Needless to say, I’m tremendously proud of my employees. They’re all brilliant.

Hopefully I’ve been an influence in their brilliance, although I suspect they were there already.


Non-SQL technologies

This month, Mala Mahadevan (@sqlmal) challenges us to write about technology to learn that isn’t SQL. Interesting topic – I feel like there are quite a number of technologies that I feel I know outside the SQL space, but I had to give some thought to what I should encourage people to learn.

PowerShell was an obvious one – arguably, any command-line scripting. The number of times that people need to do things with files, or have some automated process, or need to do some old-fashioned grepping, but don’t feel confident enough to spin up a PowerShell window… it beggars belief. It’s incredibly useful and anyone technical should be across it. General data folk probably don’t need to jump right into creating their own cmdlets, but they should definitely know about ripping into files, looping, all that.

To help get you started in that, I wrote something a bit over a year ago at 

The other technology I can’t go past is Excel.

Excel because it’s the data environment that regular business people know, and you should know it at least as well as them.

They’ll want you to give them copies of data in Excel (tip: ask them what they will do with that data once you’ve given it to them!), they will create charts with Excel, they will give you data in Excel (another tip: learn about MDS and let them use Excel to maintain data stored within MDS), and they will implement their business logic there. Essentially, it’s where they do Data Analytics. Make sure you can read their Excel expressions / formulae, and the VBA code they write, and understand pivot tables, and so on. It will become a useful tool for you as well, because you can generate Excel spreadsheets through SSRS now (so long as you understand what SSRS will do around merged cells if you haven’t taken care).

And Excel can lead you onto teaching them about Power Pivot, Power BI, and Analysis Services. Your Excel experts can become expert data modellers, and you can be raising the next generation of data scientists.

So those are my two suggestions for non-SQL technologies – PowerShell and Excel. They’re not new, but they are set to stay significant for some time. If you don’t have them, you’re missing some serious foundations.


Where projects often go wrong

I don’t write about our customers in a negative way. I just don’t. I don’t ever want a customer (or potential customer) to see what I’ve written about someone else and to worry about what I might be saying about them. So I don’t tweet about the times when something’s driving me crazy, or about code that I’m seeing – I don’t even tweet about the times that I’ve tuned a process from 15 minutes down to a second or two in case someone considers that I’ve just broadcast that they had a process that was taking 15 minutes.

But I do write for T-SQL Tuesday every month. Without fail. And this month, the topic is failed projects. Thanks to Jeff Mlakar (@jmlakar) for hosting.

And so I want to write about something I see which commonly makes projects fail, and that is:

A misaligned expectations and feedback loop

There’s a blog post I often show to customers that dates back to 2006. It’s Kathy Sierra’s post called “Don’t Make The Demo Look Too Done“, and it brings out a lot of good points.

The gist of it is that customer expectations largely depend on how finished the demo looks, because they don’t see what’s happening behind the scenes. They’re measuring the completeness of the project based on the tip of the iceberg, and not really listening to any commentary about how much work there is to do beneath the surface. I was thinking of putting a picture here of an iceberg, but I think you can imagine. You know what’s going on out-of-sight of the customer, and how the user interface is just a small part. It’s just that the customer might not, and will imagine, and either get excited about how much progress they think has been made or concerned about how little progress seems to have happened.

Kathy also brings out the fact that the depth of feedback will tend to be based around how complete the thing appears to be. If I asked for feedback on this post, you might say “You know, you really should’ve put a good picture of an iceberg in that last paragraph”. Or you’ll point out the fact that I have a mispelled word in this sentence. You probably won’t tell me whether you think Kathy’s post is a good way of making my argument, or whether I should continue to write blog posts every month. You’ll probably assume that the window for that is gone. (Of course, having said this, there are four types of comments that will almost certainly appear below – no prizes for the bleeding obvious.)

As a consultant who tends to get involved in customers’ businesses, not just their data (because honestly – there’s a reason why it’s called “Business Intelligence”), I do sometimes find myself saying “Now, please tell me if the opportunity for this level of feedback has passed…”, and then asking questions that probably belonged much earlier in the project. I’ll then see people taking deep breaths and wondering whether they should’ve either not invited me to that meeting or perhaps got me into meetings back when the opportunity for that level of feedback hadn’t passed.

Project-wise, I see that developers, whether database developers or user interface developers or whatever, tend to work based on their understanding of how things work, and customers won’t ask certain questions until they see how things actually look. Because before then, they don’t really get a sense that it’s being worked on. And then suddenly things look like they’re done, and they feel that the window for deeper feedback has passed them by. As much as I love the rich visuals available with Power BI, the speed with which data can be presented (and the same occurs even in SSRS) can often cause people to give the wrong level of feedback early on, and only later look at fundamental questions like whether the metrics that are being shown are being measured in the right way for the business.

All of this can lead to projects which are poorly estimated.

Customers will feel like things are going well if they’re seeing things that look pretty, and developers will become unstuck when they realise that they didn’t have key pieces of information early on because they hadn’t asked for feedback at the right times.

Sadly, there’s no easy solution, except to manage both sides better. Customer expectations need to be managed to make sure that they have a good picture of where things are at, and developers need to be managed to make sure that stories are being presented in ways that invite the right level of feedback. Methodologies can help, including rapid prototyping (although this should also be taken with salt because prototypes can often form the top of an iceberg and mislead – everyone should understand that prototypes are designed to be learned from and thrown away), but ultimately it’s about perspective and empathy. By understanding the difficulties faced by each side, a technical manager ought to be able to manage the expectations of both customers and developers, and advocate for the other in getting the right level of feedback needed at each point along the way.

It doesn’t surprise me that our T-SQL Tuesday host Jeff sees continuing behaviour around poor project estimation. I see it too. I’m guilty of misjudging the quality of feedback that I’ll need.

And I frequently show Kathy’s blog post to the customers and developers that I interact with, in the hope that we can all improve and get better estimations going forward.


Triggers – good or bad?

Long time readers might remember my posts from years ago about implementing Table-Valued Parameters in SQL 2005, or about TVPs in SSIS, and think that I have some interesting views about triggers. I’m not sure whether Steve Jones (@way0utwest) knew this when he decided on this month’s T-SQL Tuesday topic, but either way, it’s been a long time and triggers are worth talking about.

They’re worth talking about because people misunderstand them and misuse them. And therefore they are hated.

Triggers are code that is executed when something in the database happens, such as an insert, delete, or update for DML triggers, or created, dropped, and so on for DDL triggers. The event ‘triggers’ the action, hence the name (but don’t get me started about the fact that the ‘trigger’ is not the thing that does the triggering; it’s the action).

And therein lies the problem. Someone does something which seems to be innocent, but then finds that some code has started executing, and the innocence of their tiny operation is lost. And who knows what awful things are done!? And all within the same transaction as your innocent little update…

But the problem isn’t what they do, it’s that you didn’t know it was there. Just like asbestos.

People complain about nested triggers, about triggers that are overly complex, about triggers which do too many things… but stored procedures have all these faults too. It’s just that you knew that the stored procedure was there, and you didn’t know that the trigger was there.

I wish that triggers were more visible inside the tools (Management Studio / Operations Studio / Visual Studio), right alongside stored procedures and procedural functions (those ones that use BEGIN & END and are bad, rather than inline functions). They’re code and should be treated as such.

Once you start to see triggers as code, and particularly if you use INSTEAD OF triggers rather than AFTER triggers, you can start to see them as being not dissimilar to stored procedures that have access to handy internal tables. In those two posts I linked to at the start, I show ways you can use triggers to effectively have table-valued parameters without user-defined table types. I think this is really handy and somewhat elegant, except that the code isn’t obvious. Of course you can document them, and find ways to let your DBAs know they’re there… but still they will be considered semi-visible, and be nefarious by default.

Like many features of technology, they will be used by well-meaning developers, and often the impact will be negative rather than positive. You may not be able to change the fact that they’re used without refactoring major parts of the architecture (again, like asbestos), but there are some things you can do:

1. Be pragmatic. Accept that you will come across systems that use triggers. Don’t overreact or ask that developers be fired – remember that you’ve seen worse.

2. Document them. Make sure that you don’t forget they’re there, and that those that come after you can discover them easily.

3. Learn to appreciate the power of them. I’m not saying you should start implementing them, unless you’re pushing data around using SSIS and are wishing you had TVPs, but you could look at them as being like stored procedures and appreciate that they could be an elegant solution.

4. Tune them. You tune the stored procedures in your system, and you can tune the triggers too. Every bad thing you come across is an opportunity to demonstrate your strength as a DBA.

Thanks again to Steve for hosting this month’s T-SQL Tuesday. For more on triggers, go and check out the other posts this month. I’ll leave it up to you to decide whether triggers are good or bad – I think it’ll depend on how they’ve been done.


Developing a new niche for presenting

This year I wanted to set a goal for coming up with a new range of topics to present on.

As much as I think a large portion of the SQL community need to learn more about Joins (and to start looking at them differently in terms of the functionality they’re providing) and Sargability (in general!), two talks that I did at last year’s Data Platform Summit in Bengaluru, I was feeling like they were topics I’d done quite a lot over the years. And they’d been recorded a few times, which made me feel like I maybe wasn’t providing as much value to attendees as I would’ve liked.

Coming up with ideas was looking like it was going to be hard. I had definitely hit a brick wall (thanks for hosting, Wayne!).

You see, I wanted to come up with something different.

These days I have introduced Cognitive Services and AI into the topics I teach about, but teaching courses feels different (to me, at least) than presenting at conferences. Nothing is jumping out at me yet in that area. I spent a bit of time looking, but nothing felt right.

And then I found myself remembering SQLBits.

At SQLBits, I was in a weird mood. My (UK-based) grandmother had died about a day before, while I was en route to see her. I was emotionally exhausted and also had grief to deal with, but figured I would go to the event anyway. I saw friends from the SQL community, played around with a few magic tricks, and decided to do a very different type of presentation – one without a computer. Not just slide-free, but technology-free (apart from the microphone I was wearing). It wasn’t brave of me – the word is probably ‘stupid’. You can watch it here if you want, but essentially I used large pieces of paper and a thick pen. I got people up from the audience, put them in groups, moved them around, and distracted the people in the room from the bounds of a typical presentation. I think I mostly pulled it off.

At SQL Saturday in Melbourne I gave a talk about index internals, with the goal of explaining how indexes work and why your index choices can affect the ways that your queries run, the space that they take, and more. And again, I did it without a computer. I wrote on flip charts, and got people up from the audience to hold them. My original plan was to stick them on the wall, but the audience participation worked nicely, and I’m sure I pulled it off.

I presented in these styles because I found myself wanting to do something different with the topics that I was already doing. I wasn’t developing a new niche of topic, just a new style. I had given up trying to think of a new topic, but still wanted to do something different. I’d done slideless talks, and whiteboard sessions using OneNote, but this was new.

And this is going to be my niche. It’s not a new topic niche, just a new presentation niche. I’ll spend a bit of time exploring it, I think – coming up with different ways to explain the stuff you potentially already know.

So over the next year (or more), I might not come up with topics that are entirely new, but I want to present them in ways you might not have seen before. I want to come up with entirely new topic areas every couple of years still, but in the meantime, I can take audiences in different directions. It might not work for everyone, but if it works out for most, then that’s good enough for me.

I didn’t break through my that brick wall that I’d hit. It’s still there, and it’s still made of brick, but I’m looking at it from a different angle now. Sometimes the best way through a wall is simply to take a step back, and walk around it a different way.


Code I couldn’t live without

This month’s writing assignment is to describe code I wouldn’t want to live without, and Bert (thanks for hosting, by the way!) actually says “that you’ve written”. Well, that last point made me think, because the examples I’m most likely to wish I had with me are all ones that I can find easily enough online, and are very unlikely to have been written by me.

The most obvious one that I think of that I wrote and have used multiple times and was really frustrated when I couldn’t find it when I needed it was one that I’ve used remarkably rarely in comparison – being a warehouse date table with a bunch of computed columns (in fact, every column except one called ActualDate was calculated, and persisted of course). I’d taken the time to work out all the nuances of nondeterminism to make sure that it worked correctly, handling financial years and English month&day names, and variations around the start of the week, and all kinds of things.

…but it felt very multidimensional-centric, and I haven’t used it in quite a while.

It was hard to find another example, but I’m going to go with the example that I use to demonstrate the pain of NOLOCK. Because I’ve typed this fresh lots of times, and I find it’s still very compelling. So I’ll write it for you again.

A case against NOLOCK

I’m going to create a table and insert exactly 1 million rows. This particular table will be a clustered index, and will contain 1 million GUIDs.

Next I prove that there a million rows.

Now without inserting or deleting any rows, I’m going to shuffle them.

And if while this is happening, I count the rows in a different session, I have to wait for that query to finish.

But what if I use NOLOCK?

I run this over and over, to demonstrate the results that come out.

I don’t have to wait for the answer, but the number it gives me is…


That’s right, I won’t get the correct answer until the first query is finished. At least, I’m very unlikely to.

Of course it’s exaggerating what happens in real life, but I find it makes the point.