Press "Enter" to skip to content

Curated SQL Posts

Working with Managed Entities in Azure SQL DB

Published 2024-07-26 by Kevin Feasel

Josephine Bush creates and uses a managed identity:

Benefits of Using Managed Identities and Entra Groups

  • Enhanced Security: Using managed identities eliminates the need to manage credentials, reducing the risk of credential theft.
  • Simplified Management: Entra Groups streamline the management of permissions for multiple users or managed identities, making it easier to apply consistent access policies.
  • Scalability: As your organization grows, you can easily manage access by adding new users or managed identities to Entra Groups without needing to update database permissions individually.

Read on to see how you can create one and what you can do with it.

Comments closed

Changing Distributions and Simpson’s Paradox

Published 2024-07-25 by Kevin Feasel

Jerry Tuttle describes a paradox:

So you spent hours, or maybe days, cranking out thousands of numbers, you submit it to your boss just at the deadline, your boss quickly peruses your exhibit of numbers, points to a single number and says, “This number doesn’t look right.” Bosses have an uncanny ability to do this.

      Your boss is pointing to something like this: Your company sells property insurance on both personal and commercial properties. The average personal property premium increased 10% in 2024. The average commercial property premium increased 10% in 2024. But you say the combined average property premium decreased 3% in 2024. You realize that negative 3% does not look right.

Although the blog post doesn’t explicitly mention Simpson’s paradox, I’d argue that this is a good example of the idea. H/T R-Bloggers.

Comments closed

Plotting Individual Values and Means of Multiple Groups in R

Published 2024-07-25 by Kevin Feasel

Ali Oghabian builds a graph:

In this post I show how groupScatterPlot(), function of the rnatoolbox R package can be used for plotting the individual values in several groups together with their mean (or other statistics). I think this is a useful function for plotting grouped data when some groups (or all groups) have few data points ! You may be wondering why to include such function in the rnatoolbox package ?! Well ! I happen to use it quit a bit for plotting expression values of different groups of genes/transcripts in a sample or expression levels of a specific gene/transcript in several sample groups.

Click through for the sample code and output. H/T R-Bloggers.

Comments closed

Comparing Configuration of Two SQL Server Instances

Published 2024-07-25 by Kevin Feasel

Jana Sattainathan checks the labels on these bottles:

A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.

This is, strictly speaking, a comparison of configurations rather than data differences, indexing, and the like. Nonetheless, it’s useful to make this sort of comparison just to ensure that your instances have your desired state configuration.

Comments closed

Shared Library Preloading in PostgreSQL

Published 2024-07-25 by Kevin Feasel

David Wheeler talks pre-loading:

Recently I’ve been trying to figure out when a Postgres extension shared libraries should be preloaded. By “shared libraries” I mean libraries provided or used by Postgres extensions, whether LOADable libraries or CREATE EXTENSION libraries written in C or pgrx. By “preloaded” I mean under what conditions should they be added to one of the Shared Library Preloading variables, especially shared_preload_libraries.

The answer, it turns out, comes very much down to the extension type. Read on for details.

Read on for an interesting discussion of what pre-loading means and the circumstances you should consider along the way.

Comments closed

Microsoft Purview Classifications and Sensitivity Labels

Published 2024-07-25 by Kevin Feasel

James Serra labels the data:

I see a lot of confusion on how classifications and sensitivity labels work in Microsoft Purview. This blog will help to clear that up, but I first must address the confusion with Purview now that multiple products have been renamed to Microsoft Purview. I decided to use a question-and-answer format that will hopefully clear up the confusion (I was very confused too!):

Purview is a fantastic product. I just wish it cost about 10% as much as it does; then I could heartily recommend it to people.

Comments closed

Finding Multiple Substrings in R

Published 2024-07-24 by Kevin Feasel

Steven Sanderson is looking for two things:

Hello, fellow R programmers! Today, we’re looking at a practical topic that often comes up when dealing with text data: how to check if a string contains multiple substrings. We’ll cover how to do this in base R, as well as using the stringr and stringi packages. Each approach has its own advantages, so let’s explore them together.

Read on for three separate examples.

Comments closed

The Importance of Exploratory Testing

Published 2024-07-24 by Kevin Feasel

Thuy covers why exploratory testing is important:

Exploratory Testing is a software testing method that testers use to explore, find, and test features, bugs, or issues in an application freely and without the need for a prior testing plan. In exploratory testing, the tester will focus on freely working with the app as a real user and trying to find bugs and issues without following a specific test scenario.

Exploratory testing is a type of testing in which test cases are not created beforehand, but testers can test the system quickly. They can jot down ideas about what needs to be checked before performing the test. The focus of exploratory testing focuses more on testing as a “thinking” activity that explores new cases that do not follow the mainstream activity.

It’s amazing (and dismaying) how many bugs you can find simply by clicking around. The tricky part about exploratory testing is not actually finding bugs, but keeping track of your actions so that a developer knows how to fix the bugs you’ll inevitably find.

Comments closed

Forms and Filters in Streamlit

Published 2024-07-24 by Kevin Feasel

I have a new video:

In this video, I extend the Streamlit app that we’ve been working on even more. We’ll convert a set of drop-down lists into a form, change the behavior of these drop-down lists, and add date picker logic.

Click through for the video, the code to date, and links to additional resources. I’m pretty happy so far with this series, and we’re about to kick it up to another level with the next video.

Comments closed

A Comparison of Backup Types between SQL Server and Oracle

Published 2024-07-24 by Kevin Feasel

Sergey Gigoyan compares two sets of backups:

It is a well-known fact that backing up databases is one of the most common tasks for any database administrator (DBA), regardless of the relational database management system (RDBMS) used. However, the definition, naming, and structure of backup types can differ for each RDBMS. Therefore, if a DBA switches from one RDBMS to another, dealing with backup types can sometimes be very confusing. For instance, switching to Oracle databases after working with SQL Server databases for a long time can cause some difficulties. DBAs can work through these challenges by understanding the differences in database backup types.

Click through for the comparison. This a good reminder that, just because two things have the same name, doesn’t mean they do the same thing.

Comments closed