Press "Enter" to skip to content

Curated SQL Posts

Dealing with Arrays in SQL and jOOQ

Published 2025-04-01 by Kevin Feasel

Lukas Eder covers mapping functions:

ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to:

  • Construct arrays
  • Nest data into arrays (e.g. by means of aggregation or subqueries)
  • Unnest data from arrays into tables

But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value function> section lists a not extremely useful TRIM_ARRAY function, exclusively (using which you can remove the last N elements of an array, something I have yet to encounter a use-case for)

There are a few database platforms that support the ARRAY type, as Lukas lays out.

Leave a Comment

The Overhead Cost of Kubernetes

Published 2025-04-01 by Kevin Feasel

Steve Jones shares some thoughts:

A report of cloud Kubernetes usage shows that these resources are being under-utiliized, over-provisioned, and costing more than necessary for many organizations. From the previous year, average CPU declined from 13% to 10%, and memory is used at only around 23%. Companies are over-provisioning their clusters, which is understandable. No one wants to have systems overloaded and users complaining about performance.

Steve goes on to list some of the challenges of running an orchestrator like Kubernetes (or OpenShift or whatever). There’s a lot of code and process behind them, and that can be challenging if you don’t have administrators who know what they’re doing. Even hosting in Azure Kubernetes Service or Amazon Elastic Kubernetes Service only removes some of the systems management pain. That said, there is a certain level of comfort in knowing that my applications will automatically restart if a problem occurs, so the pain is usually worth it.

Leave a Comment

Avoid using sysadmin Accounts for Linked Servers

Published 2025-04-01 by Kevin Feasel

Denny Cherry shares sound advice:

When setting up linked servers, the selection of the accounts that are used for the linked server logins should have the lowest permissions needed to get what the users on the source side of the linked server need to do. Over time, this will mean changing the permissions of the linked server or even setting up multiple linked servers that all point to the same target server so that different applications don’t have permission to access each other’s databases over the linked server. The one thing that you never want to do is to use a login for the linked server that has sysadmin rights on the target instance, especially if that linked server is available for everyone on the server to use.

Click through to understand why.

Leave a Comment

Choosing the Right Extended Events

Published 2025-04-01 by Kevin Feasel

Grant Fritchey makes a choice:

For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don’t just think that’s a good thing. I think it’s a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I’m trying to do? Except for blogs like this one, there’s not always a lot of guidance on these things. Microsoft has (good) documentation on Extended Events in general, but not on every event, plus, not a ton of guidance. (some though).

Click through for Grant’s advice.

Leave a Comment

Updates to OneLake Security

Published 2025-04-01 by Kevin Feasel

Aaron Merrill announces some upcoming changes:

This evolution of OneLake security is still in development. Over the next few months, we will be expanding OneLake security’s integration across the platform, adding even more robust capabilities, and boosting performance. Customers wishing to get an early look at these capabilities and provide feedback before the broad public preview, can sign up for the early access preview. Once we’ve enabled your workspaces, these new features will show up as new capabilities within OneLake data access roles.

Click through to see what’s on the list. The original promise of a single security model covering all data in Microsoft Fabric is still quite a ways away, though this is a step in the right direction.

Leave a Comment

Microsoft Fabric March 2025 Updates

Published 2025-04-01 by Kevin Feasel

Patrick LeBlanc puts together a big list:

Welcome to the March Feature Summary!

From the innovative Variable library (Preview) to the powerful Service Principal support in the CI/CD features, there’s a lot to explore. Dive in and discover how the new Partner Workloads in Fabric bring cutting-edge capabilities to your workspace. Plus, enhanced OneLake security ensures your data is protected. And don’t miss out on the expanded regional availability for Eventstream’s managed private endpoints, making it easier for organizations worldwide to build secure, scalable streaming solutions.

With FabCon kicking off today, the announcements are rolling in! Get ready to explore these features and more in the March 2025 updates for Fabric!

FabCon triggered a large number of big announcements, and considering that the outline takes up about a page and a half, there’s a lot to dig into here.

Leave a Comment

Generating Realistic Data with Data Diluvium

Published 2025-04-01 by Kevin Feasel

Adron Hall wants to generate some realistic time-series data. First up is humidity data:

When working with TimeScale DB for time-series data, having realistic environmental data is crucial. I’ve found that humidity is a particularly important parameter that affects everything from agriculture to HVAC systems. 

Then comes temperature data:

Following up on my previous post about adding humidity data generation to Data Diluvium, I’m now adding temperature data generation. This completes the pair of environmental data generators I needed for my TimeScale DB setup. Temperature data is crucial for time-series analysis and works perfectly alongside the humidity data we just implemented.

Check out both of these for detailed coverage of how to generate realistic-looking simulated data.

Leave a Comment

Finding Mismatched Rows in Power Query

Published 2025-03-31 by Kevin Feasel

Reza Rad looks for that other sock:

Finding rows that are in one table, but not the other is one of the most common scenarios happening in any data related applications. You may have customer records coming from two sources, and want to find data rows that exist in one, but not the other. In Power Query, you can use Merge to combine data tables together. Merge can be also used for finding mismatch records. You will learn through this blog post, how in Power Query you can find out which records are missing with Merge, and then report it in Power BI. To learn more about Power BI, read Power BI book from Rookie to Rock Star.

Click through for the video and article.

Leave a Comment

Semantic Search in PostgreSQL

Published 2025-03-31 by Kevin Feasel

Hans-Jürgen Schönig performs a search:

PostgreSQL offers advanced capabilities through extensions like pgvector, which enable semantic search at a level and quality never achieved before. Unlike traditional text search, which mostly relies on trivial string comparison, semantic search in PostgreSQL goes beyond keywords by understanding context and meaning, enhancing relevance.

The quick idea here is that converting words (or parts of words) into vectors can maintain most of the semantic meaning behind the words. Then, when we perform certain types of vector comparisons, we can take advantage of this semantic meaning and find results whose language is different from our query but the concept is a match for what we want. Click through for the full article.

Leave a Comment

Building a Simple Microservice with Azure Functions

Published 2025-03-31 by Kevin Feasel

Temidayo Omoniyi takes us through an example of creating a microservice:

Today’s architecture is serverless intensive, with multiple microservices performing a particular task. Industries are beginning to move away from traditional monolithic applications, which have a single large codebase infrastructure handling everything, to an easier microservice approach.

Click through for a primer on serverless architecture, microservices, and how to create a simple Python app that acts as a microservice.

Leave a Comment