Press "Enter" to skip to content

Curated SQL Posts

EXPAND and COLLAPSE in Power BI Visual Calculations

Marco Russo and Alberto Ferrari build on a foundation:

In a previous article, we introduced VISUAL SHAPE, the table modifier that adds a hierarchical structure to a table, which is needed to implement visual calculations. In this article, we introduce the concept of visual context, the virtual table lattice, and the two main operators to navigate in the visual context: EXPAND and COLLAPSE.

Click through to learn more about these operators and how they fit into the rubric of visual calculations.

Comments closed

UNION and UNION ALL

Erik Darling hires the Pinkertons:

UNION and UNION ALL seem to get used with the same lack of discretion and testing as several other things in T-SQL: CTEs vs temp tables, temp tables vs. table variables, etc.

There are many times I’ve seen developers use UNION when result sets have no chance of being non-unique anyway, and many times I’ve seen them use UNION ALL when there would be a great benefit to discarding unnecessary duplicates.

Erik’s explanation goes about three steps beyond “UNION is bad so always use UNION ALL.” It’s a must-read for anybody who regularly writes T-SQL queries.

Comments closed

Correlated Subqueries in SQL

Joseph Yeates classifies subqueries:

I’ve recently been brushing up on my SQL skills, as I’ve used the language for a while but less so recently. Through this process, I’ve found that I’m comfortable with the topics of complex joins, Common Table Expressions (CTEs), and nested subqueries. However, it was my deep dive into subqueries where I found something new: correlated subqueries. In this post, we’ll explore the intricacies of subqueries, with a spotlight on the often overlooked (at least for me) correlated subqueries.

Click through to understand what correlated subqueries are, in contrast to other forms of subquery.

Comments closed

Parameterizing Dynamic SQL the Right Way

Andy Brownsword does things right, after a fashion:

When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names.

Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right way.

Why have the wrong way and then the right way? The answer is simple. It is a truth universally acknowledged, that a post with a Gallant must be in want of a Goofus.

Comments closed

Using the tapply() Function in R

Steven Sanderson applies things a different way:

Hey R enthusiasts! Today we’re diving into the world of data manipulation with a fantastic function called tapply(). This little gem lets you apply a function of your choice to different subgroups within your data.

Imagine you have a dataset on trees, with a column for tree height and another for species. You might want to know the average height for each species. tapply() comes to the rescue!

Read on to see how it works.

Comments closed

Testing with Databricks

Anh Nguyen Viet shares some thoughts on testing in Databricks:

With diverse support and a focus on workspace uniformity, Databricks can bring many benefits to the testing process, such as the following:

  • Centralized: Databricks provides an integrated environment for many teams (including testing team also), allowing them to work focused and productive. Integrating tools and services in a single platform reduces fragmentation and increases efficiency during testing.
  • Consistency: Databricks offers integrated tools and services, allowing testers to work consistently across the entire testing process as a uniform and efficient working environment.
  • Enhanced Productivity and Cost Reduction: With the flexibility and efficiency in data processing supported by DataBricks, testers can save time and effort, thereby increasing work productivity and reducing project costs. Utilizing utilities properly helps automate the testing process and delivers better results.

Read on for a few tips around building tests using Databricks.

Comments closed

Tips for Crafting Recorded Software Demos

Joey D’Antoni has some advice:

In the last few weeks, I’ve been at several conferences, such as the MVP Summit and SQLBits, and have watched many demos. I’ve noticed a trend in the previous couple of years where presenters use more recorded demos than in the past. This trend is mainly something I’ve noticed with members of the Microsoft Fabric team, but I’ve seen others do it as well. When you do it correctly, only the keenest eyes in the room will notice that you aren’t doing live demos. When you do it poorly, everyone’s eyes quickly divert to their phones, losing the audience.

Click through for Joey’s advice. Mine differs from Joey’s in one aspect: I actually record the audio before the video. I used to use Joey’s approach but flipped and found it’s actually easier to do it the opposite way. For my YouTube videos, the basic workflow is:

  • Build out the demo I’ll show. Use this to get a feel for how long things should take
  • Script out each section, including intro, demo, and outro
  • Record each audio segment separately
  • Play the demo audio segment and go through the demo. When I do this, I have the audio input muted so my screen recording has no audio track
  • Splice together the audio and video tracks. Sometimes, I’ll have minor changes, such as needing to pause the video while waiting for a demo step to finish

It took me a dozen or so videos to get this system down but it does work really well.

Comments closed

Azure SQL DB Watcher and Azure Data Explorer Integration

Guy Reginiano gets chocolate in my peanut butter:

Azure SQL family users can now take advantage of an enhanced monitoring solution for their databases and leverage Azure Data Explorer or the Microsoft Fabric integration. With the introduction of the new Database Watcher for Azure SQL (preview), users gain access to advanced monitoring capabilities. 

Database watcher is a new managed monitoring solution for database services in the Azure SQL family. It supports Azure SQL Database and Azure SQL Managed Instance.

Read on to see how you can analyze results with Azure Data Explorer.

Comments closed

Analyzing Aircraft Routes

Mark Litwintschik performs a deep dive into aircraft telemetry:

In November, I wrote a post on analysing aircraft position telemetry with adsb.lol. At the time, I didn’t have a clear way to turn a series of potentially thousands of position points for any one aircraft into a list of flight path trajectories and airport stop-offs.

Since then, I’ve been downloading adsb.lol’s daily feed and in this post, I’ll examine the flight routes taken by AirBaltic’s YL-AAX Airbus A220-300 aircraft throughout February. I flew on this aircraft on February 24th between Dubai and Riga. I used my memory and notes of the five-hour take-off delay to help validate the enriched dataset in this post.

Click through for Mark’s analysis using DuckDB and Python.

Comments closed