Press "Enter" to skip to content

Author: Kevin Feasel

RETURNING Clause in PostgreSQL Update Operations

Hans-Jürgen Schönig wants the output:

PostgreSQL 18 offers a couple of new features such as asynchronous I/O (aio), improvements around indexing, and a lot more. However, there is one feature that has really caught my attention. While it might go unnoticed by many users, it is really powerful and, to me, somehow represents everything I like about PostgreSQL.

The improvement I want to focus on today is related to the way the “RETURNING-clause” works in PostgreSQL 18.

This behavior is very much like the OUTPUT clause in T-SQL. Though it appears that OLD and NEW are not themselves pseudotables like SQL Server’s INSERTED and DELETED, as there is an example that includes old and new columns together in the same row.

Leave a Comment

An Outside-In Triage for SQL Server Performance

Kevin Hill triages an issue:

It’s 9:05 AM and your helpdesk lights up: “The SQL Server is down. Nothing works.”

By 9:07, everyone is staring at you.

The trap: you open SSMS and start digging for blocking queries. But what if the database isn’t the problem at all?

I think my biggest gripe about the list is the challenge of being allowed to get several of these metrics. But it is good, in the face of a “Everything is slow fix it now!” style of request, to look at extrinsic factors before digging into SQL Server, as that’s just following the old joke about the drunken man searching for his keys under the streetlamp, despite not having lost them there, because it’s easier to see that area.

Leave a Comment

A Primer on GitHub Actions

Temidayo Omoniyi provides an introduction to GitHub Actions workflows:

In today’s fast-paced development cycles, the demand to ship high-quality code quickly is more important than ever before. However, several tedious, labor-intensive, and prone to mistakes procedures that stand between producing code and releasing it to consumers frequently slow down teams.

Every Developer faces these common issues:

  • Repetitive Checks: Before each push, unit tests, linters, and build scripts are manually executed.
  • Inconsistent Environments: Code that passes locally in one environment but fails in another is known as the “it works on my machine” dilemma.
  • High-Stakes Deployments: Deploying code by following a meticulous, manual checklist in which even one mistake could result in downtime.
  • Slow Comments Loops: The review process is prolonged when you wait for a coworker to pull your branch, run tests, and provide comments on a pull request.

I like GitHub Actions workflows a lot. Once you’ve put together a workflow or two, it’s pretty easy to see what’s going on. On top of that, there is a huge amount of functionality and an enormous number of third-party templates to extend it even further.

Leave a Comment

Announcements from FabCon Vienna

James Serra has a round-up:

A bunch of new features for Microsoft Fabric were announced at the Microsoft Fabric Community Conference (FabCon Vienna) recently. Here are all the new features that I found most interesting, with some released now and others coming soon:

Click through for approximately two dozen more announcements.

Leave a Comment

Time Zone Conversions in SQL Server

Louis Davidson knows what time it is:

Recently a topic came up at work, and I remember seeing it on Reddit, so you know, that definitely is a sign that I had to write a blog on it.

For a lot of people, we typically standardize on a single time zone for our life, and in turn our data. Maybe it is the time zone of our headquarters, our house, and sometimes it is the standard time zone UTC. It is often suggested that every organization should use UTC, and that is a great suggestion for many things.

However…if you store all your times as UTC, this becomes a nightmare for some kinds of reporting.

Click through for a primer on the capabilities of the DATETIMEOFFSET data type and some of the functionality you can use with it. And Louis hits on one important note around daylight savings time changes: the date changes over the years. Another is that what’s in the Windows or Linux time zone database isn’t always historically accurate. For example, in 1974 and 1975, the US extended DST and began it in January and Feburary, respectively. But if you use Louis’s query, it claims DST started in April, which would have followed the 1966-1973 and 1976-2006 patterns but is historically inaccurate. You’ll find these sorts of things for a variety of countries because Daylight Savings Time adherence is kind of wild.

Leave a Comment

Comparing Spark Application Performance in Microsoft Fabric

Jenny Jiang announces a new capability:

The Spark Applications Comparison feature is now in preview in Microsoft Fabric. This new capability empowers developers and data engineers to analyze, debug, and optimize Spark performance across multiple application runs—whether you’re tracking changes from code updates or data variations to improve performance.

The image in the blog post is pretty small and hard to read, but I do wonder if (or how well) it will capture cases where you’re twiddling your thumbs to get a machine so that you can execute your code. This seems to be a big problem sometimes.

Leave a Comment

More Types of Window Functions in SQL Server

I continue a series on window functions:

In this video, I continue a dive into each category of window function, quickly reviewing the four categories of window function (plus ordered set functions). Then, I cover offset window functions, statistical window functions, and ordered set functions.

This video includes some of the window functions I use most often (LAG(), LEAD()), some of the window functions I use least often, and even a few ordered set functions to boot. Combined, it’s about 45 minutes of content between this video and the prior.

Leave a Comment

Contrasting Microsoft Fabric, Databricks, and Snowflake

Ron L’Esteve builds a comparison chart:

Databricks and Microsoft Fabric are two of the most innovative Unified Data and Analytics intelligence platforms available on the market today. While similar, each brings their own advantages and limitations. Snowflake joins these two powerhouses when data warehouse decisioning comes into play. Sometimes it is challenging to decide which one to pick for your organization’s needs. This tip will help with uncovering when to choose Databricks vs Fabric vs Snowflake.

When it comes to Spark performance, Databricks is always going to win—they keep most of their optimizations to themselves, so anyone starting from open-source Spark is at a disadvantage. Otherwise, it’s a bit of a slugfest between Fabric and Databricks. At the end, Ron also brings in Snowflake, focusing on the data warehousing side of things for that three-way comparison. I don’t think there’s a clear winner among the three, and on net, that’s probably a good thing, as it forces the groups to continue competing.

Leave a Comment

Grouping Options in T-SQL

Chad Callihan rolls up the data:

When learning T-SQL, I’d wager that learning GROUP BY comes up early in the process. What may not be mentioned are the variations that can be added to a GROUP BY clause. Are you familiar with GROUP BY GROUPING SETS, GROUP BY ROLLUP, and GROUP BY CUBE? If you’ve never seen these used, or if you have and want a refresher, read on as we look at an example of each.

Of the three, CUBE is the one that I’ve used the least. I’ve found good instances where ROLLUP gives me exactly what I want for reporting purposes, and GROUPING SETS is powerful enough that I’ve made use of it a fair number of times. But CUBE just returns back too many combinations for what I’ve needed.

Leave a Comment