Press "Enter" to skip to content

Curated SQL Posts

Exploring the Area under the ROC Curve

Published 2024-02-06 by Kevin Feasel

Aayush Srivastava takes us through one of the classics of classification:

In the realm of machine learning classification, model evaluation is an essential step to assess the performance and effectiveness of various algorithms. One widely-used tool for this purpose is the Area Under the Receiver Operating Characteristic Curve (AUC-ROC curve). In this blog, we will delve into the significance of the AUC-ROC curve, how it is calculated, and why it is an invaluable metric for evaluating classification models.

In this article, we will discuss the performance metrics used in the classification and also explore the implications of using two, namely AUC and ROC. Here is an overview of the important points that we will discuss in the article. 

The fun anecdote around ROC curves is that their name actually makes sense if you know the origin: it came out of the British army in World War II, where they tracked how their radar operators classified blips as German aircraft or noise (e.g., flocks of birds). The radar receiver operators had certain characteristics, where some were more effective at separating actual threats from noise, hence the Receiver Operating Characteristic curve.

Comments closed

Monitoring if an Azure Server Goes Offline

Published 2024-02-06 by Kevin Feasel

Paul Bergson builds an alert:

My miniature schnauzer, Raven, is a smart and lively dog who loves to hunt for rodents in the yard. She has a keen sense of smell and can detect the slightest movement of her prey. She barks loudly to alert me whenever she finds a potential target and chases after it with all her speed. However, the rodents are too cunning and often escape to a tree or a hole in the ground before she can catch them. She then returns to me with a disappointed look on her face, hoping for a treat or a pat on the head.

Azure Monitor is like Raven, but much more efficient and reliable. It can monitor your Azure servers and detect when they go offline in ~1 minute. It can also alert you via email, SMS, or webhook when something goes wrong, so you can take action to fix it. With Azure Monitor, you can stay on top of your server’s health and performance.

Read on to see how you can use Azure Monitor and build policies, with much less cleanup requirement than a dog.

Comments closed

Contrasting RDS and Parquet in R

Published 2024-02-05 by Kevin Feasel

Colin Gillespie contrasts two file formats:

The RDS format is a binary file format, native to R. It has been part of R for many years, and provides a convenient method for saving R objects, including data sets.

The obvious question is which file format should you use for storing tabular data? RDS or parquet? For this comparison, I’m interested in the following characteristics:

  • the time required to save the file;
  • the file size;
  • the time required to load the file.

Read on for the throw-down.

Comments closed

Counting Path Elements in T-SQL

Published 2024-02-05 by Kevin Feasel

Steven Sanderson switches over to T-SQL for a bit:

Yesterday I was working on a project that required me to create a SQL query to generate a table of accounts receivables pathways. I thought it would be interesting to share the SQL code I wrote for this task. The code is as follows:

Click through for the code. I was playing a bit of code golf in my mind, seeing how I might modify things. One big example would be to replace the STUFF() + FOR XML PATH combo with STRING_AGG(), assuming the SQL Server instance is 2017 or later, or if the database is in Azure SQL DB or SQL MI. The count of a specific character is an interesting approach, and this is the first time I’ve had to wonder why there isn’t a helper function in T-SQL to do that. But considering that this is the first time I’ve had to ask that question, maybe that’s an answer in itself.

Comments closed

Purging Lots of Backup History

Published 2024-02-05 by Kevin Feasel

David Wiseman needs to clear out a significant amount of backup history:

Recently, I encountered an issue running sp_delete_backuphistory on servers that hosted a large number of databases with frequent log backup & restore operations. The clean up task hadn’t been scheduled and the history tables had grown very large over several months. The msdb databases was also hosted on a volume with limited IOPs.

Attempting to run sp_delete_backuphistory under these conditions you will likely encounter these issues:

Click through for that list of issues, as well as a way of mitigating the problem. I’ve noticed this kind of pattern appears fairly often in Microsoft-provided cleanup procedures: the code works well until you reach a certain scale, at which point it falls over. It’d be great if the original sp_delete_backuphistory performed batch deletion from the get-go, but David shows us a way to get around the issue.

Comments closed

Function App Caching of Key Vault Secrets

Published 2024-02-05 by Kevin Feasel

Koen Verbeeck runs into an odd issue:

In the PowerShell function, this application setting is retrieved as an environment variable so it can authenticate with the Graph API using the app registation. So far so good, except that the secret of this app registration expires after 1 year (some time ago you could configure an expiration date for in the future, but it seems this isn’t possible anymore). The Azure Function started crashing with a 401 (Unauthorized) error.

Read on to see what Koen tried, what eventually fixed it, and a pair of updates to the post.

Comments closed

Contrasting Three Unique Identifiers in Postgres

Published 2024-02-05 by Kevin Feasel

Laetitia Avrot shares some advice:

This month’s PGSQLPhriday event is about UUId thanks to my post calling for a fight debate on the topic.

I will answer a question a friend developer asked me: “What is the best when we need a primary key? UUID, CUID, or TSID?”

I hadn’t heard of two of these, but Laetitia provides some links to learn more about them and then offers up some advice on whether to use any of them. And the advice sounds a lot like the advice for SQL Server.

Comments closed

Connect to Azure SQL Database via Azure Entra ID Service Principal

Published 2024-02-05 by Kevin Feasel

Jaime Garcia de Alba becomes the machine:

In this guide, I am going to outline the steps on how to connect to an Azure SQL database using Entra SPN with tools such as SSMS and PowerShell. This demo covers detailed steps for using an existing user when the token is received correctly. Additionally, the steps cover creating a new user from scratch in case there are issues with the existing user.

I’ve used service principals and managed identities in the past in application code, but it wasn’t until this post that I learned you could also use them directly to connect to an instance.

Comments closed

Performance Costs of using Calculated Columns in Power BI Composite Models

Published 2024-02-05 by Kevin Feasel

Chris Webb share a warning:

I don’t have anything against the use of calculated columns in Power BI semantic models in general but you do need to be careful using them with DirectQuery mode. In particular when you have a DirectQuery connection to another Power BI semantic model – also known as a composite model on a Power BI semantic model – it’s very easy to cause serious performance problems with calculated columns. Let’s see a simple example of why this is.

Read on for Chris’s example.

Comments closed