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 Comment