Press "Enter" to skip to content

Curated SQL Posts

Legacy Power BI Apps Going Away

Published 2024-04-29 by Kevin Feasel

Nicky van Vroenhoven shares a public service announcement:

In case you missed the official blog post 2 months ago, I suggest you read my blog post 🙂

Or if you want you can refer to the official blog here: Announcing the retirement of legacy Power BI Apps (pre-audiences).

Already on March 6, 2023(!), Power BI apps with multiple audiences went Generally Available.

Read on for more information, with the note that these things will disappear soon—May 1, 2004 is the date of retirement.

Comments closed

Switching All SQL Server Databases to Simple Recovery Model

Published 2024-04-29 by Kevin Feasel

Vlad Drumea doesn’t need no steenkin’ transaction log backups:

This brief post contains a script that can help switch a whole SQL Server instance, model and all user databases, to SIMPLE recovery.

The script is useful in case of dev/test/QA/UAT instances that have been left by accident to use the default FULL recovery model, yet do not have or need transaction log backups.

Read on for the script. It also shrinks the transaction log file after the switch-over.

Comments closed

Digging into Azure Elastic Jobs

Published 2024-04-29 by Kevin Feasel

Rod Edwards has a job to do:

After a lengthy period in Public Preview it seems, the boffins at Microsoft have finally pushed Elastic Jobs for SQL Azure DB to general availability. Hooray!

But what are Elastic Jobs? And why would I want to use them in SQL Azure DB?

That’s one of the things you’ll learn in this post.

Comments closed

Vacuum and Autovacuum in Postgres

Published 2024-04-29 by Kevin Feasel

Pavel Borisov explains the importance of vacuuming your tables:

By default, all table data in Postgres are physically stored using the “heap” method. So every database is a set of 1Gb files (”segments”) and each file is logically split into 8Kb pages. Actual table rows are put into any page with enough free space.

When the row data is updated, a new version of a whole row is constructed and written (to any free space). The old one remains because, at the time of the update, the transaction is not completed and can be rolled back in the future. When the transaction is completed we’ll have two or several versions of the same row in the table. Cleaning old ones is by an asynchronous process called vacuum (and autovacuum).

Read on for more information about how this works and what you can (or should) do to help it along.

Comments closed

Dropping Data Frame Columns in R

Published 2024-04-26 by Kevin Feasel

Steven Sanderson performs feature selection:

As an R programmer, one of the fundamental tasks you’ll encounter is manipulating data frames. Whether you’re cleaning messy data or preparing it for analysis, knowing how to drop unnecessary columns is a valuable skill. In this guide, we’ll walk through the process of dropping columns from data frames in R, using simple examples to demystify the process.

Read on for three ways of doing this.

Comments closed

Reviewing a Pull Request with the GitHub CLI

Published 2024-04-26 by Kevin Feasel

Mike Robbins shows off one feature of gh cli:

Checking out someone else’s pull request (PR) on GitHub is essential in collaborative software development, enabling thorough code reviews, local testing, and detailed feedback. This process allows developers to ensure the quality and functionality of the code before integration, identify and resolve potential conflicts, and maintain consistency across the project. By reviewing changes locally, team members can collaborate more effectively, offering insights and improvements that enhance the overall quality of the project. Additionally, local reviews support security audits and performance evaluations, ensuring that the code is not only functional but also optimized and secure.

I don’t use gh cli as much as I should (in part because 95+ percent of the time, I’m committing into one-man repos and use GitHub Desktop), but every time I do use it, I remember that it’s a nice CLI.

Comments closed

An Overview of Powershell for DBAs

Published 2024-04-26 by Kevin Feasel

David Seis shares tips from an upcoming talk:

PowerShell is an essential tool for SQL Server database administrators looking to streamline their workflow and automate repetitive tasks. When it comes to troubleshooting your SQL Server instances, PowerShell is an essential tool for the DBA toolbox.

As a versatile scripting language and command-line shell, PowerShell enables seamless integration of commands, arguments, variables, and modules with an interface and logic that SQL users would find pleasantly familiar. This blog post will guide you through the basics of PowerShell, demonstrate how to implement simple automation for routine database tasks, and explore advanced automation strategies to optimize your database management.

The post starts as a basic primer on Powershell but also includes plenty of notes on good ways to make use of the language as a DBA.

Comments closed

Writing Conditional JOIN and WHERE Clauses

Published 2024-04-26 by Kevin Feasel

Erik Darling has Blondie stuck in my head:

The OR operator is a perfectly valid one to use in SQL statements. If you use an IN clause, there’s a reasonable chance that the optimizer will convert it to a series of OR statements.

For example, IN(1, 2, 3) could end up being = 1 OR = 2 OR = 3 without you doing a darn thing. Optimizers are funny like that. Funny little bunnies.

The problem generally isn’t when asking for IN or OR for a single column, with a list of literal values, the problem is usually when you:

  • Use OR across multiple where clause columns
  • Use OR in a join clause of any variety
  • Use OR to handle NULL parameters or variables

This is an excellent, detailed article and well worth the read.

Comments closed

Building a Docker Image with Docker Build Cloud

Published 2024-04-26 by Kevin Feasel

Andrew Pruski shows off Docker Build Cloud:

In a previous blog post we went through how to build a Docker container image from a remote (Github) repository.

Here we’re going to expand on that by actually building the image itself remotely, using Docker Build Cloud.

What we can do with Docker Build Cloud is instead of building the image locally and then having to push to a remote container registry (for example the Docker Hub), we can build remotely and then immediately push that image to the registry so that it is available for immediate use by say, our team members or deployment/testing pipelines.

Read on to see how it works.

Comments closed

Selecting the Top N Values by Group in R

Published 2024-04-25 by Kevin Feasel

Steven Sanderson searches for subsets:

In data analysis, there often arises a need to extract the top N values within each group of a dataset. Whether you’re dealing with sales data, survey responses, or any other type of grouped data, identifying the top performers or outliers within each group can provide valuable insights. In this tutorial, we’ll explore how to accomplish this task using three popular R packages: dplyr, data.table, and base R. By the end of this guide, you’ll have a solid understanding of various approaches to selecting top N values by group in R.

Read on for the three examples.

Comments closed