The Bit Bucket

I’ve been working at a site that uses proxy servers for Internet access. And we were unable to clone a Git repository in Azure DevOps (AzDO) from within Visual Studio (VS). It was quite frustrating.

Visual Studio has got proxy settings and I had configured those:

They live in the system.net node within the settings.

VS was able to get out to the Internet, and I could find and try to connect to projects in AzDO but I couldn’t clone them from within there.

2021-02-02

Sometimes we need to determine whether a string is a date or whether it is a number.

In the first example above, I’m asking if the string ‘20190229’ is a valid date. You can see from the response (0) that it isn’t. That’s because even though it’s a valid date format, February in 2019 doesn’t have a 29th day. It’s not a leap year.

The value returned from the ISDATE function is a zero or a 1. Curiously, the return value is of data type int. You’d think that a function that starts with Is and tests something would return a bit data type instead. But that’s just one of the curiosities of T-SQL.

2021-02-01

I’ve had a lot of good feedback about the options that we’ve provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn’t have that, and we’ve added it. Generally, in the industry, this is referred to as Cobol Case.

So, we added a function CobolCase.

It takes one parameter:

@InputString - the string to be formatted

2021-01-29

Another book that I listened to recently, also fits into the “I nearly didn’t get past the first chapter” category. It was Atomic Habits by James Clear. By the end of this book though, I realised just how much I’d enjoyed it.

James makes a really interesting study into habits. Far more than anything I’d ever read before.

He makes it so clear how the compound effect of hundreds of small decisions leads to profound life changes. That was already pretty obvious to me but I loved the way he made this so practical. He calls the habits that are formed Atomic Habits.

2021-01-28

I recently recorded a podcast with Kamil Nowinsky. Kamil is well-known for creating some tools that make it easy to publish individual Azure Data Factory pipelines. It’s a far better method that the standard ARM (Azure Resource Manager) template deployment that’s provided by Microsoft.

However, if you use this method (and I recommend you do), you’ll be publishing pipelines separate to the linked services that your datasets connect to.

One mistake I’ve seen a few clients making lately, is that when they created the linked services in different environments, they did so with slightly different names. Because of that, they needed to modify the deployed objects after deployment. You don’t want to be doing that.

2021-01-27

We all use IN when writing queries:

SELECT ProductID, ProductName, Color, Size 
FROM dbo.Products 
WHERE Size IN ('370ml', '220ml');

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size Color 370ml Blue 370ml Red 220ml Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

SELECT ProductID, ProductName, Color, Size
FROM dbo.Products 
WHERE (Size, Color) 
    IN (('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue'));

I wish T-SQL had that option but it doesn’t. It would be particularly useful when those values in the IN clause are coming from a sub-query.

2021-01-26

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that’s a US date format, but if it was only 5/4/2016, there’s no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I’m converting strings to dates (and/or times), I certainly don’t want to change my settings so that I run with US configuration.

2021-01-25

I spend a lot of time doing data conversion work. It’s hard to imagine how much of that relates to working with dates and times. A requirement that I had recently was to find the nearest weekday to a given date i.e. find me the closest Thursday to a supplied date.

The NearestWeekday function in our free SDU Tools for developers and DBAs, does this.

It takes two parameters:

@TargetDate date - the date that we’re aiming for @DayOfWeek int - Sunday = 1, Monday = 2, Tuesday = 3, etc. (values from 1 to 7)

2021-01-22

I haven’t posted any book reviews lately but I have been getting through quite a few books, so it’s time to rectify that. First up is Gary Vee’s book #AskGaryVee: One Entrepreneur’s Take on Leadership, Social Media, and Self-Awareness. I often read or listen to books that cover entrepreneurship. If you’ve read any of these, or listened to any related podcasts, you will have heard of Gary Vee. Gary is a Belarusian-American whose name is Gary Vaynerchuk.

2021-01-21

Louis Davidson is an old friend of mine. He’s an awesome Data Platform MVP, a database architect and a prolific author. Louis was on an earlier podcast and I was so pleased to invite him back on the show.

SQL Down Under show 81 features Louis discussing his new book, technical writing, relational DB design, and graph DB applications. Louis’ new book is this huge 1154 page volume: