Press "Enter" to skip to content

Curated SQL Posts

Natively Compiled Stored Procedures in SQL Server

Published 2025-01-29 by Kevin Feasel

Yvonne Vanslageren covers a point of frustration for me:

Modern applications often demand lightning-fast performance from their databases, whether they’re handling large transactional workloads or complex analytical queries. SQL Server’s in-memory OLTP feature addresses these needs by using memory-optimized tables and natively compiled stored procedures to boost throughput and reduce latency. This post provides an overview of natively compiled stored procedures, how to create them, and best practices for performance monitoring and maintenance.

My point of frustration is pretty simple: these things work really, really well. But they’re also so limited that I have never been able to use one in production. Memory-optimized tables are already so limited in good use cases, and natively compiled stored procedures have even more limitations, like using an awful collation (from the standpoint of humans working with the data) for string data.

Comments closed

Using the Azure SQL DB Query Editor

Published 2025-01-29 by Kevin Feasel

Josephine Bush writes a query:

I keep losing track of this wondering where it went. You have to access it at the database level. Adding this post to remind me for later. This came in very handy when my home internet went down and I couldn’t auth on my phone hotspot without timeouts in Azure Data Studio.

You can login in with SQL Server auth or Entra.

Read on for some notes about limitations. It is definitely a helpful tool for occasional queries or having a simpler way to access data without having to set up a VPN and a whole bunch of tools.

Comments closed

Security Baselines for Azure SQL Workloads

Published 2025-01-29 by Kevin Feasel

Mika Sutinen builds a baseline:

I’ve recently had to work a bit more with the Microsoft Defender and the vulnerability assessment in Azure. Following those efforts, it dawned to me that the topic of security baselines is sometimes slightly misunderstood. So, in this post, we’ll look into what a security baseline should cover (and what they probably shouldn’t).

But first things first. Security baselines are provided by the Microsoft Defender for Cloud service, which I always recommend enabling for Azure workloads (unless there’s a 3rd party solution for it already). If you don’t have anything of the sorts enabled for your databases and servers, I highly recommend you go and turn Defender on. Seriously. Do it now.

Read on to learn more about why having a security baseline is so important and where to draw the cut-off between security and functionality.

Comments closed

ALL vs ALLCROSSFILTERED in DAX

Published 2025-01-29 by Kevin Feasel

Marco Russo and Alberto Ferrari disambiguate a pair of operators:

Have you ever wondered what the subtle difference between ALL and ALLCROSSFILTERED might be? The family of ALL functions and modifiers includes some common functions, like ALL and ALLSELECTED, and some fancier and less frequently-used functions, like ALLNOBLANKROW and ALLCROSSFILTERED. This article discusses what ALLCROSSFILTERED is, why it is there in DAX, and when and how developers should use it.

Read on for that answer, along with several helpful demos.

Comments closed

Emitting Data to a Single CSV in Spark

Published 2025-01-28 by Kevin Feasel

Chen Hirsh wants to consolidate:

To write and read data faster, Spark splits the work between nodes in a cluster, each reading\writing part of the data. That’s why, in the screenshot above, there are 3 CSV files (That’s the files starting with “Part”, with a CSV extension), instead of 1. Note that this can also occur when working with a single node cluster since Spark splits the work into tasks.

This behavior is great if you intend to keep working with the CSV files in Databricks since reading will be faster. But if you want to share this file with someone outside of Databricks, this may be inconvenient.

Read on for two ways of doing this, as well as the price you pay to get it done.

Comments closed

Dropping a Role in PostgreSQL

Published 2025-01-28 by Kevin Feasel

Josephine Bush drops a role:

You can’t just exec DROP ROLE your_role_name; if it’s granted perms or other roles are granted to it. I had to go fishing to find all the grants to revoke them. Note: if you are worried about re-granting later, you can always fiddle with this to output the grants for these perms as a rollback.

Read on for a few scripts to help out with finding what that role owns, revoking rights, and reassigning ownership.

Comments closed

Truncating All Tables while Preserving Foreign Keys in T-SQL

Published 2025-01-28 by Kevin Feasel

Ronald Kraijesteijn builds a script:

When testing a data warehouse, a common challenge is managing large datasets effectively. Often, you need to reset tables to a clean state, ensuring consistent testing environments. The most efficient way to clear a table is using the SQL command TRUNCATE TABLE. However, this command is not straightforward when foreign key constraints are present. In this article, we’ll explore a solution that temporarily disables constraints, allows truncation, and then restores the constraints—keeping your data model intact.

Click through for the script, which saves a record of all of the foreign key constraints, truncates each table, and then re-creates the foreign keys.

Comments closed

Automatically Refreshing a Power BI Semantic Model after Dataflow Loads

Published 2025-01-28 by Kevin Feasel

Reza Rad refreshes a model:

Although this seems to be a simple thing to do, it is not a function that you can turn on or off. If you have a Dataflow that does the ETL and transforms and prepares the data, then to get the most up-to-date data into the report, you will need to refresh the Power BI semantic model after that, only upon successful refresh of both dataflow and semantic model is when you will have the up-to-date data into the report. Fortunately, in Fabric, this is a straightforward setup. In this article and video, I’ll explain how this is possible.

Click through for the video and the blog post. Granted, this feature is in preview, but using it is pretty straightforward.

Comments closed

Calculating the Distance between Points via T-SQL

Published 2025-01-28 by Kevin Feasel

Sebastiao Pereira gets out the measuring tape:

How do you calculate the distance between two different points on a sphere using TSQL in SQL Server? In this article, we look at how this can be done to calculate different points on the globe.

Sebastiao first shows the raw calculation, then uses the GEOGRAPHY data type to simplify the job. Note that these are “as the crow flies” distances and not travel distances, as there’s no information on roads.

Also, these are calculations specific to Earth. Which sounds like the setup for a joke, but it’s really not. If you have a smaller sphere (or oblong spheroid, if you will) and you want to calculate the distance, use the GEOMETRY data type instead.

Comments closed

Power BI Embedded and Direct Lake Mode

Published 2025-01-28 by Kevin Feasel

Hasan Abo Shally announces a new preview feature:

As we step into 2025, we’re excited to announce the preview of Power BI Embedded with Direct Lake Mode, a new feature designed to enhance how developers and Independent Software Vendors (ISVs) provide embedded analytics in their applications. This capability, available in preview starting Q1 2025, leverages the power of Direct Lake Mode to allow an even enhanced performance and experience for embedded analytics.

This probably affects a small audience, though the functionality is good to see.

Comments closed