No I can!

Allow me to interrupt my normal technical posts to share a personal story. I can’t sing Starting in my early youth and continuing all through my adulthood, people have been telling me that I cannot sing. Classmates, colleagues, random people I met. But also people I trust and love. For example, my mother loves to talk about when my, my brother, and my sister were still young children and we used to have times when the us three and mum and dad would sit together and sing songs. She loves the memory, and one part of his we sang “polyphonic”…
Read More

Removing multiple patterns from a string

Recently one of my clients had a request that was a challenge to do effectively. I don’t think it’s a very common requirement but I still decided to blog about it. Who knows, maybe one of you will actually benefit. Pattern removal Within a existing query, one of the columns in the result set holds string data that includes sequences and patterns that need to be removed in the output. Most of those patterns can occur more than once, and then all of them need to be removed. Some of these patterns overlap with each other and then the longest…
Read More

T-SQL Tuesday #111: Why, tell me why

No Comments
Time flies. It feels like the new year has just started, and yet we’re already at the second T-SQL Tuesday of the year. Our host this February is Andy Leonard (b|t), and his assigned topic is, simply: Why? Or rather: What is your why? What motivates you, what makes you tick? And most of all: What makes you do the things you do? The Execution Plan Reference I do a lot of things. I have a one-person consultancy business, I have a wife and kids, I have a personal life with several hobbies, and I could talk about my motivation…
Read More

Plansplaining, part 10. Just passing through

No Comments
Welcome to part ten of the plansplaining series. Each of these posts takes an execution plan with an interesting pattern, and details exactly how that plan (or pattern) works. In this post we will look at a query and execution plan that may appear perfectly normal and unexpected at first sight, but that has some perhaps confusing execution counts. Sample query The sample query below will (as almost always) run in all versions of the AdventureWorks sample database. It returns a list of all staff, and for people that have a sales-related job title it adds the total of sales…
Read More

SQL injection

2 Comments
One of the many sites where I occasionally answer database-related (and sometimes other) questions is Quora – also known as “yet another question and answer site because we all know that what the world really needs is yet another question and answer site”. On that site (and probably many others), some topics just keep resurfacing. One of those “evergreen” topics is SQL injection. The questions on this topic are not all the same, but they all do fall into a few broad categories. So instead of repeating the same reply over and over again, I have decided to write a…
Read More

Collation change script

31 Comments
WARNING: The script presented in this post was for a specific situation at a specific client. It was not intended to be complete, and it is not actively maintained. Several people have suggested improvements in the comments, and Erland Sommarskog has written a long article where he presents a more refined version. Please check which version works best for you, and test with care! Normally when I blog, I spend a lot of time making sure that get as close to perfection as I can. This post is an exception. The problem I am talking about is too far out…
Read More

Off Topic: Charity and snooker

No Comments
So this will be a very unusual post for this blog. It will be completely off topic. One of the things I do in my spare time is to play snooker. Mind you, I am not good at the game. But I enjoy it, and it’s a nice break from sitting at my laptop. Marathon So why do I share this tidbit about my personal life? Why do I think anyone cares? The reason is that I need some help. Next month, on February 15 and 16, 2019, I am participating in a 24-hour snooker marathon. And with my participation…
Read More

T-SQL Tuesday #110 – “Automate All the Things”

It is once more the second Tuesday of the month, better known as T-SQL Tuesday. This month, we got our writing assignment from Garry Bargsley (b|t). He asks us to write about automation: what automation are we proud of or do we want to complete, and using what technology? However, I found that I was more triggered by the title of his request post than by the content, so I’ll allow myself the liberty of applying a very loose interpretation of the topic. All the things? Garry titled his invitation “Automate All the Things”, and I plagiarized that title because…
Read More

Looking back to 2018

No Comments
We are approaching the end of 2018. It is December 30 as I start writing this (I’ll probably finish and publish it December 31), traditionally a time to look back at the past year. I often refrain from doing what everybody does because a certain date is on the calendar (my dad would sigh and suggest that I’m still not over puberty, and he’d probably be right). But in this case I’ll make an exception. For me, 2018 was mainly the year I started building my own website. The site where I publish my blog posts, but more important: the…
Read More

Plansplaining, part 9. Recursive CTEs

4 Comments
I had to skip three months, but finally it is here: part eight of the plansplaining series. Each of these posts takes an execution plan with an interesting pattern, and details exactly how that plan works. I am pretty sure that (almost) everyone reading this blog knows that a CTE (Common Table Expression) is an independent subquery that can be named and then referenced (multiple times if needed) in the main query. This makes CTEs an invaluable tool to increase the readability of complex queries. Almost everything we can do with a CTE can equally well be done by using…
Read More

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close