A Fine Slice Of SQL Server
Jared Westover explains the difference:
Recently, a developer asked for my input on solving a complex SQL query problem. As we went through each step, I suggested using a ranking function at one point, specifically RANK. However, they were having trouble deciding when to use RANK versus DENSE_RANK in a query. It raises a good question: When should you use DENSE_RANK instead of RANK?
Jared’s analogies for when to use which are very similar to the ones I use: a race versus levels.
Leave a CommentOlivier Van Steenlandt runs into an issue:
Over the past few weeks, I have been doing some experimenting with Azure SQL mirroring to Microsoft Fabric. In the process, I ran into a couple of issues and challenges. In this data recipe, I will be going through one of my challenges when I got Azure SQL mirroring to Microsoft Fabric setup and running.
At first, everything seemed to be working as expected, and the integration felt very smooth. At that point, I continued to develop my test database in Azure SQL to learn a bit more about mirroring. I made a couple of minor changes to my test database and tried to publish them from my SQL Database Project.
Read on for the issue, as well as the solution—that is, assuming you don’t actually want to change any of the things you’re actively moving over into Fabric.
Leave a CommentLeo Li announces a new preview feature:
The On-premises Data Gateway manual update feature is now available in preview! This new capability simplifies gateway maintenance and helps you keep your environment secure and up to date with greater flexibility and control.
With this new feature, administrators can now manually trigger updates—either directly through the gateway UI or programmatically via API or script. This ensures that you can manage update timing based on your organization’s internal policies and maintenance windows while still benefiting from the latest features, performance improvements, and security patches.
Read on to see how this works, as well as where the Fabric team is going with this.
Leave a CommentKellyn Gorman brings in the usual suspects:
“How does the database identify this query and its execution plan?”
Both Oracle and PostgreSQL answer this question, but I find they do it in very different ways, reflecting fundamentally different design philosophies around optimization, observability, and stability. As I dive into this rabbit hole once again, I’m going to reflect on how Oracle’s SQL_ID differs from the query_id in PostgreSQL and how two terms that sound so similar (PLAN_HASH_VALUE and query_hash) could be generated so differently, as well as misinterpreted. I’m guilty of it myself, so it’s a good place to spend some time.
Read on for the answer.
Leave a CommentHugo Kornelis wrote some code:
The application had clearly been developed by someone who knew just one tool: SQL Server. So he made the database do all kinds of things that really should have been in the application layer. Call external APIs to monitor the primary process of the company. Send fully formatted emails with invoice information. Send formatted HTML and interpret the callback response as a picked menu item. Yes, all that was handled in SQL Server, the frontend app did nothing more than sending query results to the web server and sending HTTP replies back to the database as a parameter into a stored procedure that handled everything.
Read on to see what Hugo was able to do in a one-year timeframe and what it was like coming back to that same code years later.
Leave a CommentRebecca Lewis continues a series on Microsoft Fabric:
You already know the options. Run heavy reporting queries against production. eewgh. Or stand up a reporting replica, build ETL to keep it current, maintain a refresh schedule, and hope nothing breaks on a holiday weekend. It works, but it’s expensive and has an awful lot of moving pieces.
Fabric gives you a third path: continuously replicate your SQL Server data into OneLake using Fabric Mirroring, and let Power BI read it using Direct Lake mode. Your SQL Server stays focused on OLTP and your reporting runs against a near real-time copy in Fabric. No pipelines. No refresh schedules. Nice.
Read on for the options available with Microsoft Fabric, as well as an endearing note that “real-time” isn’t.
Leave a CommentShane Borden lays out an argument:
The short answer is always “maybe”. However, in the following post, I hope to demonstrate what creates a sub-transactions and what happens to the overall transaction id utilization when they are invoked. I will also show how performance is affected when there are lots of connections creating and consuming sub-transactions.
Click through for some testing and results.
Leave a CommentJason Yousef shows off a capability in Pytest:
Pytest is one of those tools that feels obvious after you’ve used it for a bit. Tests are just functions. Assertions read like normal Python. And when you need context—database sessions, config, mock data—you reach for fixtures instead of duct tape.
Read on to see how they work. Admittedly, I don’t think I’ve used fixtures before in Pytest, but now seems like a good time to try it.
Leave a CommentAndy Brownsword thinks about successful code:
A common time to revisit old code is when something breaks. I was contemplating Pat’s question this week when a field length change had caused a truncation issue in an old SSIS data flow. Some code doesn’t age badly because it’s wrong, but because it didn’t expect to fail.
It’s all too easy to write a piece of code which ‘does the thing’, run a few variant tests, and send it on its way. But will it stand the test of time? That’s where my good and bad code diverge, and I usually revisit the code and find the old milk.
Admittedly, I’m not as good at this as I should be either. It can be a challenge to think through the possible issues that could arise and develop code to mitigate or eliminate those issues. But as Andy points out, it can be critical to success.
Leave a CommentRob Douglas ran out the clock:
My preview trial of 2025 was evaluation version, and if you let that run past 180 days your SQL instance just will not start. I had an old Azure VM that I fired up to grab some code from a SQL Agent job I had been playing with and hit exactly this problem. Here’s the workaround:
Click through for the process. Fortunately, Rob doesn’t stop at how to get the instance up again, but continues into installing a non-evaluation version.
Leave a Comment