The Bit Bucket

I work with a lot of clients that are creating analytic systems. They all collect large numbers of data points and analyze them. Most do a fairly good job with it.

One area that many also handle well is detecting anomalies i.e. data that is out of the ordinary.

But another area that I see very few handling well, is the data that is missing, rather than just the data that is present. There’s a huge difference between data that arrived, and is odd, and data that just didn’t arrive at all.

2024-11-18

One of the most popular courses that we used to run in person was a Query Performance Tuning and Advanced T-SQL course. I recently finished converting the Advanced T-SQL course to an online format, but a key (no pun intended) part of the Query Performance Tuning course was the content on reading SQL Server execution plans.

For so long, I’ve wanted to get the execution plan content available online, as reading them is such an important skill. Well, we finished it today ! The new course is:

2024-10-30

One of our popular courses is Advanced T-SQL for Developers and DBAs.

If you’re still writing T-SQL like it’s SQL Server 2000, or even SQL Server 2016, it’s time to look at what’s changed over the years.

We’ve just pushed out substantial updates to that course. The new content areas pushed out in this update are:

New Module 1 Content: (Using Common Data Types Effectively)

• UNISTR - embedding Unicode within strings
• ANSI string concatenation with ||
• Extensions to TRIM, LTRIM, and RTRIM, particularly for removing characters apart from spaces
• New options for CURRENT_DATE
• Truncating dates with DATETRUNC
• Grouping periods of dates with DATE_BUCKET

New Module 2 Content: (Using Special Data Types)

• Creating tables of values with GENERATE_SERIES
• Optimizing optional parameters by using IS [NOT] DISTINCT FROM
• Working with bits (shifting left, right, counting, and setting and getting bits)

New Module 7 Content: (Ranking, Pivoting, and Grouping Data)

• Using APPROXIMATE_PERCENTAGE (discrete and continuous) to enhance performance and reduce memory usage
• Using GREATEST and LEAST to reduce query complexity

New Module 8 Content: (Using TOP, APPLY, and Window Functions)

• Using named windows to simplify queries
• New NULL treatment options for window clauses

New Module 10 Content: (Working with JSON Data)

• New options for storing data by using the json data type
• Enhanced options for OPENJSON
• Using ISJSON to check JSON formats, including checking for VALUE, ARRAY, OBJECT, and SCALAR types
• JSON_PATH_EXISTS helps when working with nullable data
• Using JSON constructors to create objects and arrays
• Using the new JSON aggregates

We’re really pleased with this update.

2024-10-09

I’ve had a busy time reading books lately. Another one from my friends at PackT that looked interesting to me was Database Design and Modeling with PostgreSQL and MySQL: Build efficient and scalable databases for modern applications using open source databases by Alkin Tezuysal and Ibrar Ahmed.

PostgreSQL and MySQL

Database design and modeling is a passion of mine and, while most of my time in recent years has been spent on the SQL Server side of the fence, I’m always involved in PostgreSQL projects, and occasionally with MySQL. Each database has “a way of doing things” that’s considered best practices. MySQL has never been a database engine that I’m keen on, but with tools like WordPress, it’s always been part of some projects.

2024-10-02

Another book that my friends at PackT sent me for review recently was Microsoft Power BI Performance Best Practices by Thomas LeBlanc and Bhavik Merchant.

Over the years, I’ve come across Thomas many times but haven’t had the chance to spend time with him, unlike Bhavik who worked here in Australia quite a bit. They are a good combination to write this book.

Content - Downloads

I like the fact that samples of everything discussed in the book are available. I also like the idea that if you bought the printed book, that you can download a PDF as well.

2024-10-01

This is yet another post in the category of “I’ll record it here so that the next time I run into it, I can remember what it was”. And if it helps anyone else in the meantime, that’s a bonus.

I was trying to push some changes to an Azure Repos repository (as part of an Azure DevOps project).

No matter what I tried, the Commit button in the Git window was grayed out.

2024-09-25

This post is in the category of “I’ll record it here so that the next time I run into it, I can remember what it was”. And if it helps anyone else in the meantime, that’s a bonus.

I had configured a new repository in Azure Repos (as part of Azure DevOps), cloned it to a local copy, modified some files and tried to push them back to the repo.

2024-09-25

One of the events that I try to take part in each year is the Hack Summit. I’ve always enjoyed it.

The next edition of the The Hack Summit is on October 17th online and October 18th in Warsaw at Stadion PGE Narodowy. It’s a conference fully dedicated to cybersecurity.

I so wish I could have been there, but unfortunately, that’s not possible for me this year. The organizers describe it as having “a diverse and rich agenda, featuring more than 15 thematic tracks, over 150 presentations, networking opportunities, an afterparty, and much more….”.

2024-09-20

Another book that I’ve been sent for review lately, was a pre-release edition of Data Visualization with Microsoft Power BI by Alex Kolokolov and Maxim Zelensky. It looks like it’s released now.

I’ve known Alex for many years, and have acted as a judge in his UI competition each year. He has a flair for data visualization. In the book, he mentioned that he was initially puzzled by many people finding it hard. But noted that “I later discovered that for experienced analysts and data engineers, visualization was entirely unnecessary. They comprehend the data structure, and the relationships are evident to them simply through tables. They genuinely don’t understand the need for all these pie charts and graphs.”

2024-09-18

Just a heads-up that v24 of SDU Tools went out to SDU Insiders last week. If you haven’t used SDU Tools, they are just a large library of functions implemented in T-SQL. You can use them as a full library or use them as examples of code in work you are trying to do.

Backwards Compatibility

We’ve also tried to keep all the code working for all versions of SQL Server that we see clients using. That means from SQL Server 2008 to SQL Server 2022. We also have an Azure SQL DB version.

2024-09-10