Press "Enter" to skip to content

Curated SQL Posts

Compiling Power BI Calculation Groups

Published 2026-09-03 by Kevin Feasel

Phil Seamark looks at an optimization:

Power BI now avoids compiling calculation items that a query has already filtered out. The time saved is before the first storage engine event. It does not make the scans themselves faster.

Will I benefit? If your query filters a calculation group down to a subset of its items, it may compile faster. A single filtered group can benefit, although often only by a little. The largest gains are in models where calculation groups reference one another, because the engine used to expand combinations the query never needed. If your query has no calculation groups, or uses every item in them, there is nothing to prune.

Click through to see what the hubbub is all about and if it might affect you.

Leave a Comment

From SQL to PySpark and Spark SQL

Published 2026-09-03 by Kevin Feasel

Andy Brownsword gives Spark a try:

I’ve spent years shaping data with SQL Server, however after pulling at the threads of Fabric I’m opening notebooks and finding PySpark.

At first glance the difference is stark, but it’s not quite the dramatic shift it appears. If you’re not familiar, let’s look at what’s very similar, and where the true differences are.

There’s plenty of nuance in the syntax differences and behavioral differences between the platforms, but Spark SQL is just as ANSI compliant at this state as pretty much any other platform, and PySpark feels a lot like a chained quasi-functional approach to SQL because of Spark’s Scala heritage.

Leave a Comment

Full-Text Index Management Permissions

Published 2026-09-03 by Kevin Feasel

Emad Al-Mousa tests some permissions:

I was exploring SQL Server Text Indexes, and while exploring it I stumbled upon the function sys.dm_fts_index_keywords.

According to the “current” version of the documentation (up to 1 September 2026): https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-fts-index-keywords-transact-sql?view=sql-server-ver17

sysadmin role is required to run this function, I found out that this is not true !

Click through for the test. Granted, this does require CONTROL on the database, so not something that J. Rando db_datawriter can do.

Leave a Comment

Atomicity and Isolation in Fabric Data Warehouse

Published 2026-09-03 by Kevin Feasel

Louis Davidson receives a surprise:

Ironically, today’s topic is kind of the opposite. I expected things to be far different in Fabric, but it isn’t really that different, at least not in behavior, except when it is. Since this system is Parquet file based, I didn’t think there would be locking, blocking, etc. I sort of expected it would be sort of locked down when writing data, maybe just single threaded per file, but highly concurrent when reading. Reads would most likely work like time travel and read previous data where it existed. And transactions? Would there be transactions? I guessed not before I got started with it.

Was I wrong?

Read on for the answer. The concurrency model isn’t exactly the same as SQL Server’s, but it’s not that far off.

Leave a Comment

Spark SQL Temporary Views on Fabric Schema-Enabled Lakehouses

Published 2026-09-03 by Kevin Feasel

Gerhard Brueckl wants to create a temporary view:

Some time ago my friend Christian Henrik Reich blogged about how to handle schema-enabled lakehouses in Spark temporary views. We already found a good solution leveraging SQL USE keyword to set the schema once and reference tables by name only in our views. However, after some tests, in particular with notebookutils.notebook.runMultiple, I realized that there are some more things to consider as suddenly my SQL and notebooks stopped working when executed in parallel!

But let set the scope for this blogpost first. We recently built a data platform on Microsoft Fabric where we integrated data from different source systems which were combined into a single schema-enabled lakehouse where each source system had it own schema. Naturally, when querying those schemas, we used temporary views to express our business logic in SQL and then continue working with PySpark. We relied heavily on USE to work around the issue describe by Henrik until we realized that this approach does not work in combination with runMultiple. So we had to find another solution which I will describe here.

Read on for that solution.

Leave a Comment

The Downside to SQL Server’s Trigger Execution Model

Published 2026-09-01 by Kevin Feasel

Fabiano Amorim lays out an argument:

By default, SQL Server executes both DML (data manipulation language) and DDL (data definition language) triggers under the security context of the user whose statement causes the trigger to fire. The trigger author supplies the code, but the future caller supplies the privileges under which that code runs.

This is the default behavior of SQL Server triggers, and it creates a dangerous situation. One principal controls the trigger code, another supplies the execution privileges and, combined, the trigger can then potentially hijack the caller’s authority.

Additionally, a user who creates a trigger does not need permission to perform every operation contained in the trigger. They only need permission to create the trigger – and then an opportunity for a more privileged principal to fire it later.

Click through to see how things can go wrong and what alternatives would be possible for a new execution model.

Leave a Comment

REGEXP_LIKE() and Its Return Value

Published 2026-09-01 by Kevin Feasel

Reitse Eskens troubleshoots an error:

I’ve declared a variable and assigned it a value. Now, I want to check if my variable matches the regular pattern of Dutch postal codes. These codes are four numbers followed by two letters. Using a regular expression helps check validity.

My expectation was that this query would return either 1 or TRUE. In any case, a result telling me that the postal code is valid. Instead, it throws an ‘Incorrect syntax error near the keyword ‘REGEXP_LIKE’.

Click through for the screenshot and the explanation.

Leave a Comment

Identity Column Reseeding and Clashes

Published 2026-09-01 by Kevin Feasel

Dualcore DBA violates Betteridge’s Law of Headlines:

I recently had cause to reseed an identity column on a table in SQL Server, which got me wondering “If we reseed an identity column to a value lower than the existing identity values in the column, could it cause a clash?” Because why wouldn’t you wonder if you can cause something to break, whilst performing a fairly simple task!?

Let’s find out what happens…

Click through for the demonstration.

Leave a Comment

Bacpacs vs Dacpacs

Published 2026-09-01 by Kevin Feasel

Drew Skwiers-Koballa covers two file formats that often annoy me:

When you need to move an entire database or move the objects in a database, bacpac and dacpac files often come up because of all of the tooling options to interact with them. Bacpac and dacpac files share some core similarities as well as some major differences in how they’re used in SqlPackage and other SQL tools, but their flexibility can create a bit of confusion. In this post, we are going to discuss exactly what makes bacpac specifically important, as well as explore the options that a dacpac is capable of.

My big problem with bacpacs (and dacpacs that include data) is that, once you get to database sizes that are at all interesting, bacpacs fail to work, either because they time out in creation or in restoration. Dacpacs are quite nice for database deployments and maybe including a few reference tables, but creating a bacpac of a 100+ GB database? Best of luck.

Leave a Comment