The Bit Bucket

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:

You’ll find the podcast with Louis (and all our other podcasts) here: SQL Down Under Podcast

2021-01-19

I wish SQL Server had more metadata associated with columns in tables, and with objects in general. The closest thing we have to that is the use of extended properties.

Other Databases

In other databases that I’ve worked with, there are richer properties held for each column. For example in Progress, as well as the normal data type and nullability, there were options like:

• Formatting mask (how this column is normally formatted)
• Prompt (what question to automatically ask the user - gives you a chance to explain what you’re asking)

and much more. This type of thing drastically cut down the amount of time it took to build applications using the database, and provided a great level of consistency.

2021-01-19

In a previous post about CAST and CONVERT, I mentioned that when you use CAST, you don’t get the option to specify the format you want to use. SQL Server offers an extension to the SQL standard with CONVERT and it lets you do just that.

CONVERT has an option parameter that lets you apply a style to the data type conversion that you’re doing.

In the example shown above, I’m converting the current date and time (based upon the SYSDATETIME() function) to a varchar(8), using style 112.

2021-01-18

I do a lot of data conversion work each month. Many systems store date and time values in Unix format, so I often need to convert to/from these in T-SQL. Fortunately it’s easy.

A common way that Unix systems store the date and time value, is to store the number of seconds since the beginning of 1970.

The DateTime2ToUnixTime and UnixTimeToDateTime2 functions in our free SDU Tools for developers and DBAs, does this.

2021-01-15