Press "Enter" to skip to content

Curated SQL Posts

What Happens upon Row Modification in Postgres

Published 2024-11-20 by Kevin Feasel

Semab Tariq takes us on a journey:

But behind the scenes, it’s more complex to ensure that data remains consistent and accessible. In today’s blog, I’ll answer some frequently asked questions from our customers and dive into why PostgreSQL relies on a process called VACUUM to efficiently manage data updates.

Updating a row in PostgreSQL isn’t as straightforward as directly changing the existing data. Instead, PostgreSQL avoids in-place updates, meaning it doesn’t overwrite rows directly. 

But what does this actually mean? 

Read on to learn what that actually means.

Comments closed

Metadata-Only NOT NULL Column Insertion

Published 2024-11-20 by Kevin Feasel

Andy Brownsword has the need for speed:

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.

Comments closed

What’s New in SQL Server 2025

Published 2024-11-20 by Kevin Feasel

Brent Ozar has a list:

Today at Microsoft Ignite, Microsoft announced SQL Server 2025. The biggest new features focus on AI and bringing the latest Azure SQL DB features down to your own servers. Here are the top features:

Of this list, I’m excited for regular expression support and optimized locking. On the latter, I would like RCSI to be the default, but I can understand that this would have a significant “Who moved my cheese?” factor for DBAs, and there are just enough cases when RCSI introduces actual change into an environment.

Fabric mirroring is also fine, and sp_invoke_external_rest_endpoint is something that can be useful, not just for making external calls. As for storing vector data, I will want to see its performance—both in terms of how quickly it loads & retrieves data, as well as how accurate the results are—before I get too excited. DiskANN is a good algorithm and it’s in Cosmos DB right now, but I haven’t been that impressed with the quality of results so far from it in Cosmos.

2 Comments

Debugging in Databricks

Published 2024-11-19 by Kevin Feasel

Chen Hirsh enables a debugger:

Do you know that feeling, when you write beautiful code and everything just works perfectly on the first try?

I don’t.

Every time I write code It doesn’t work in the beginning, and I have to debug it, make changes, test it…

Databricks introduced a debugger you can use on a code cell, and I’ve wanted to try it for quite some time now. Well, I guess the time is now 

I’m having trouble in finding the utility for a debugger here. Notebooks are already set up for debugging: you can easily add or remove cells and the underlying session maintains state between cells.

Comments closed

Querying a Fabric KQL Database via REST API

Published 2024-11-19 by Kevin Feasel

Sandeep Pawar grabs some data:

I have previously explained how to query a KQL database in a notebook using the Kusto Spark connector, Kusto Python SDK, and KQLMagic. Now, let’s explore another method using the REST API. Although this is covered in the ADX documentation, it isn’t in Fabric (with example), so I wanted to write a quick blog to show how you can query a table from an Eventhouse using a REST API.

Click through to see how you can do it. Sandeep’s code is in Python but because this is just hitting a REST API rather than using a library, you could also use some tool like Postman.

Comments closed

Aggregate Functions in Power BI

Published 2024-11-19 by Kevin Feasel

Hristo Hristov writes some DAX:

At times when using Power BI, you want to combine your data to produce an aggregated value. The aggregation is performed over some criteria – frequently this may be time (year, month, date) or a categorical value. Some popular aggregation functions to apply can be Sum, Average, Maximum, Minimum, or Count. Typically, Power BI applies certain aggregations by default when adding data fields to visualizations. What if you wanted to create your own data aggregations? To achieve better understanding of the underlying data, how can you attain fine-grained control over the aggregations?

Read on for several DAX measures, including totals, running totals, moving averages, and day over day changes.

Comments closed

Open Order Computation with Visual Calculations in DAX

Published 2024-11-19 by Kevin Feasel

Marco Russo and Alberto Ferrari want to track open orders:

Open orders (or events in progress) is an extremely common pattern in business intelligence. It answers a simple question: given two dates – order received and order delivered – how many orders have yet to be delivered at any given point? We do have a pattern here: Events in progress – DAX Patterns that solves the scenario with DAX measures.

While demonstrating the pattern during a classroom course, one student (thanks Justin Duff!) asked whether the pattern could be solved by using visual calculations. It turns out that visual calculations can be of great help in optimizing the performance of this specific scenario because they greatly reduce the number of calculations required to solve it. Well… sort of. Visual calculations might perform well, but we will do better with DAX alone!

Read on for the examples and why visual calculations might not be the best fit for this scenario.

Comments closed

Creating Totals and Subtotals in Postgres with ROLLUP and CUBE

Published 2024-11-19 by Kevin Feasel

Elizabeth Christensen uses a pair of analytical operators:

Postgres is being used more and more for analytical workloads. There’s a few hidden gems I recently ran across that are really handy for doing SQL for data analysis, ROLLUP and CUBE. Rollup and cube don’t get a lot of attention, but follow along with me in this post to see how they can save you a few steps and enhance your date binning and summary reporting.

I’ve used ROLLUP on occasion, but never found a great case when CUBE made sense in a report. I am, however, quite partial to GROUPING SETS, the third of these analytical operators and the one that gives you the most control.

Comments closed

Deadlock Resolution and Prevention in SQL Server

Published 2024-11-19 by Kevin Feasel

Eitan Blumin works through some deadlock issues:

Deadlocks in SQL Server can be frustrating and can cause significant performance and reliability issues. A deadlock occurs when two or more transactions are waiting for each other to release a lock on a resource, resulting in a situation where no transaction can proceed, and eventually, one of them is automatically killed and rolled back. This can happen when two transactions try to access the same data in a different order or when one transaction holds a lock on a resource while waiting for a lock held by another transaction. In this blog post, we’ll discuss how to troubleshoot and prevent deadlocks in SQL Server.

Click through for a way to get information on deadlocks, as well as three techniques for reducing the risk of deadlocks occurring.

Comments closed

Using Kubernetes with Distributed Availability Groups

Published 2024-11-19 by Kevin Feasel

Andrew Pruski has a guide for us:

A while back I wrote about how to use a Cross Platform (or Clusterless) Availability Group to seed a database from a Windows SQL instance into a pod in Kubernetes.

I was talking with a colleague last week and they asked, “What if the existing Windows instance is already in an Availability Group?”

This is a fair question, as it’s fairly rare (in my experience) to run a standalone SQL instance in production…most instances are in some form of HA setup, be it a Failover Cluster Instance or an Availability Group.

Read on for the tutorial. There are quite a few steps involved.

Comments closed