Press "Enter" to skip to content

Day: September 8, 2022

The Joy of Treemaps

Published 2022-09-08 by Kevin Feasel

Simon Rowe answers describes one of my favorite often-inappropriate visuals:

Dr Shneiderman developed the “treemap” in order to visualise this large amount of data—with multiple levels of folders and subfolders—in an efficient way, without taking up too much screen real estate. The treemap uses a series of nested rectangles, sized proportionally to the corresponding data value, to deliver an organised and multi-level view into any hierarchical data set.

Treemaps get misused a lot but are really valuable in specific scenarios. Click through to learn when (and when not) to use a treemap.

Comments closed

Visualizing Delay Times on Subway Stations

Published 2022-09-08 by Kevin Feasel

Benjamin Smith looks for delays:

Any Torontonian who has commuted regularly on the TTC has probably experienced their fair share of delays on the subway. Having experienced a few recently I was inspired to visualize the average delay times across all stops on the subway. What are the stations with the longest delays on average this past year? Could we make a nice visual with it?

Click through for the end result as well as the process to get there.

Comments closed

PolyBase and Named Instances

Published 2022-09-08 by Kevin Feasel

I show how to connect to a named instance using PolyBase in SQL Server 2019 or 2022:

We have two SQL Server instances running on the same machine. Before we get started, I do want to point out one thing: PolyBase can only work on one instance for a given server (physical machine or virtual machine) because the PolyBase engine and data movement services are system-level services. This means you cannot have PolyBase installed on your main instance as well as your named instance.

Click through for two methods.

Comments closed

Building UNPIVOT Syntax

Published 2022-09-08 by Kevin Feasel

Michael J. Swart has a function:

Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.

Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.

Comments closed

FizzBuzz in SQL

Published 2022-09-08 by Kevin Feasel

Cathrine Wilhelmsen does some modulo division:

This week, my coworkers and I were given a fun challenge. Using any tool or language, solve FizzBuzz! Then present and explain the solution to the rest of the team. This was a fun challenge because our team is a mix of junior-to-senior developers and data professionals, working with everything from SQL to Python to C# to DAX to PowerShell. Those who had never solved FizzBuzz before got the chance to do so, while those who had already solved it got the chance to try again using a different tool or language.

Read on for Cathrine’s solution using a tally table. This classic variant of FizzBuzz is pretty easy to do in T-SQL; building the numbers table is the trickiest part.

Comments closed

Lock Escalation Thresholds

Published 2022-09-08 by Kevin Feasel

Paul White gets into the weeds:

This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much.

Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings.

There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. Neither of those assertions is true, but the details are interesting, as I’ll explain.

As always, Paul brings clarity to a difficult topic.

Comments closed