A Fine Slice Of SQL Server
Vlad Drumea explains how powerful a few specific roles are:
Privilege escalation is a method in which an attacker gains unauthorized privileged access into a system.
This is done by exploiting misconfigurations, design flaws or unpatched vulnerabilities.The most straightforward example in SQL Server land being someone “promoting themselves” from members of the public fixed server role, to members of the sysadmin role.
Read on for these three roles and what you can do with them.
Leave a CommentThe Full-Text Engine manages full-text indexes. The engine splits your text columns into individual terms and builds an inverted index, mapping each term back to the rows in which it appears. Unlike a standard B-tree index, the structure lives outside the normal index internals and is maintained asynchronously via a background process called a crawl. Effectively, each word in your text string ends up indexed, rather than the string as a whole. This makes certain types of searches much more efficient (you need to use search terms like CONTAINS() to utilize full text indexes).
If you have to search strings like this then full-text indexes can be very effective. String searching isn’t great in SQL Server, but this is definitely a tool in your belt if you have a requirement that makes it useful.
Because of some changes to the way full-text indexing works in SQL Server 2025, there is a post-upgrade maintenance task you’ll have to perform.
Leave a CommentIn earlier posts in this series we established that every heap tuple lives inside a strict 8KB page. Everything else is built on top of that hard limit: MVCC, HOT updates, and indexes that point at
(page, line_pointer). And yet this still works:CREATE TABLE docs (id int PRIMARY KEY, body jsonb); INSERT INTO docs VALUES (1, (SELECT jsonb_agg(g) FROM generate_series(1, 100000) g));That
bodyvalue is somewhere north of half a megabyte. The heap page is still 8KB. Both statements are true at the same time, and the mechanism that makes them coexist is TOAST: The Oversized-Attribute Storage Technique.
Read on to see how TOAST works, when it kicks in, and some of the consequences of this solution.
Leave a CommentBernat Agullo Rosello compares two capabilities:
Ever since DAX UDFs came out as public preview in September 2025, many DAX developers started wondering how they will compare with calculation groups since both have the centralization of code as one of their main selling points. As pointed out in a recent article by SQLBI they are indeed very different beasts, even though they can be used to achieve very similar outputs.
In short, a calculation group is a model-level object whose items swap one DAX expression for another at evaluation time. Once an item is in the filter context, it applies to every measure being evaluated. A DAX UDF is a smaller object: a named, reusable expression with parameters, callable from any measure but invisible to report users.
Read on to see when calculation groups still make sense and when DAX UDFs are the better choice.
Leave a CommentGarry Bargsley puts it all together:
Over the past four weeks, you’ve built a real foundation. You know that single quotes are literal and double quotes expand variables. You know how to store and use data in variables – strings, integers, booleans, and hashtables. You know how to loop over a collection and do something useful to each item, including handling errors when a server doesn’t cooperate.
Now we put it all together. No new concepts this week, just everything you’ve learned working as a single, practical script that solves a real DBA problem. Think of it as your Jedi trials. The training is done. Time to use the Force.
Click through for the script. It’s fairly simple in terms of what it’s actually doing, but gives you a good idea of the types of things you can do.
Leave a CommentZhenguo Zhang adds some patterns:
Adding patterns to plots is a great way to improve accessibility (making plots colorblind-friendly) and to add an extra dimension of information. The
ggpatternpackage provides a rich set of tools to achieve this inggplot2.
I’m personally not the biggest fan of patterns. I see them as a point of necessity when dealing with grayscale circumstances, such as printing out a chart in an academic journal. But it’s very easy to overdo patterns and end up making a mess of the visual.
But one side note about color vision deficiency and plots: make sure that your plots are monochrome-friendly because somebody probably will try to print out your chart or view it on a grayscale-only device. Or might actually be monochromatic.
Leave a CommentHugo Kornelis is back with another video:
Bad hair day? Try having an almost-no-hair month!
Jokes aside. It has been almost six weeks since my last video blog. Not really the schedule I had planned. But I believe I have good reasons.
Anyway, I do have a new video ready now. As promised in my last video, I now cover Parameter Sensitive Plan Optimization (PSPO), a new feature, introduced in SQL Server 2022, that is supposed to alleviate the pain of bad parameter sniffing.
Read on for the promise and letdown of PSPO.
Leave a CommentReza Rad covers a multitude of formats:
Power BI files come in multiple formats. If you have been working with Power BI Desktop for a while, you probably know the PBIX format well. But there are other formats too: PBIT, PBIP, and PBIR. The last two are more recent additions, and there is also a language called TMDL (Tabular Model Definition Language) that comes with its own file type.
In this blog post, I am going to explain the differences between all of these formats, why each one exists, and which one you should use for your scenarios. I will also show you, through a practical demo, what PBIP looks like and how you can use it to make your development process significantly better.
Click through for the video and article.
Leave a CommentRichard Yen provides a write-back system for analysts:
I recently wrote a post about WAL log shipping and how a standby built on log shipping is a great way to give data analysts production data without putting the primary at risk. Having access to the production data in this way is great, but it’s read-only. How can we create views of this data for better analytics work? I want to make the case today that Foreign Data Wrappers and Materialized Views can make a great solution – not only in accessing production Postgres data, but also working with other data sources.
Click through for an architectural discussion of how they can work together.
Leave a CommentKoen Verbeeck answers some questions:
Is Microsoft Fabric replacing Azure Data Factory?
Officially, no. Or maybe not yet. At the time of writing, ADF still remains a separate product but it’s noticeable that more new features are added to Fabric than to ADF. There are still many customers using ADF, so Microsoft might keep the service around for a while. There’s also still a bit of a feature gap between the two services, but this becomes more narrower each month. Microsoft is offering migration scenarios from ADF to Fabric.
I picked this question because of how much the concept annoys me. There are three separate Data Factory code bases in Azure with overlapping but not matching functionality (which is how you can tell it’s multiple code bases and not just one code base reskinned). This can lead to a scenario where Person A says, “Oh, do this thing in Data Factory.” Person B then says, “But I can’t do that in Data Factory.” Person A’s response: “Oh, that’s weird, because I can do it in Data Factory.” This leads to necessary but somewhat absurd clarifications around how you need to use Microsoft Fabric Data Factory, not Azure Data Factory because, even though Microsoft Fabric Data Factory is hosted in Azure, it’s a different product.
And don’t get me started on the wide variety of KQL platforms, all of which are subtly different.
Leave a Comment