Press "Enter" to skip to content

Curated SQL Posts

Database Code is Code

Published 2024-08-15 by Kevin Feasel

Tom Zika speaks the truth:

This month’s invitation is from Mala Mahadevan (b) asking us how we manage the database code. Since this is my passion project, I have a few basic tips to share.

I have yet to see a perfect implementation but even a partial one benefits you. My experience is mostly with enterprise-scaled environments – large servers, minimal downtime, brownfield development and also mostly single-tenant.

But these concepts and building blocks should be generic enough.

Read on for Tom’s thoughts, as well as some pros and cons of the state-based versus migration-based approaches for database changes.

Comments closed

Backing SQL Server up to S3 Buckets

Published 2024-08-15 by Kevin Feasel

David Fowler backs up a database:

Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.

Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.

And, going one step further, you can also use PolyBase to read data from S3 buckets in SQL Server 2022.

Comments closed

String Concatenation in R

Published 2024-08-14 by Kevin Feasel

Steven Sanderson smooshes strings together:

String concatenation is a fundamental operation in data manipulation and cleaning. If you are working in R, mastering string concatenation will significantly enhance your data processing capabilities. This blog post will cover different ways to concatenate strings using base R, the stringr, stringi, and glue packages. Let’s go!

Read on for examples using paste(), paste0(), str_c(), stri_c(), and glue().

Comments closed

SQL ConstantCare Population Report for Summer 2024

Published 2024-08-14 by Kevin Feasel

Brent Ozar shares an update:

The short story: SQL Server 2022 finally saw some growth this quarter! Two years after the release, 1 in 10 SQL Servers is finally running the latest version.

The long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2024 version of our SQL ConstantCare® population report.

As always, this is my reminder that we’re looking at a particular sample of the SQL Server population—users of Brent’s service—and will likely have some skew. That said, even within the context of this population, it is interesting to see these trends over time, and Brent covers that in the post.

Comments closed

Multi-Tenant Database Design Choices for SQL Server

Published 2024-08-14 by Kevin Feasel

Erik Darling finally has a blog post whose text I can quote versus a string of good videos to check out:

When you’re designing the back end of a database, people will have all sorts of ideas.

Normalization, partitioning, referential integrity, and, usually, trying to figure out what to do when you have more than one client.

If your application is user-focused (like Stack Overflow), you don’t have to struggle too much with the idea of isolation. But when your application is geared more towards supporting multiple entities that have one or more users, things change. Sort of like how Stack Overflow manages all the other Stack Network sites.

Were you to ask me which model I prefer, it would be every tenant getting their own database. Your other options are:

  • Everyone all mixed in together like gen-pop
  • Using separate schemas inside a single database

Definitely read what Erik has to say. My prior job was a hybrid multi-tenant environment: for the main transactional database, there were several dozen SQL Server instances. Each instance had anywhere from one to a few dozen copies of the transactional database, and each database hosted one or more customers’ data. There’s not a lot of tooling out there to support that kind of strategy, so we had to build a lot of it in-house. But that said, it did work out reasonably well without having hundreds or thousands of databases on a single instance.

Comments closed

Direct Query of Flat Files in Postgres via file_fdw

Published 2024-08-14 by Kevin Feasel

Semab Tariq uses a Postgres extension:

The file_fdw (Foreign Data Wrapper) is a PostgreSQL extension that lets you access data stored in flat files, like CSV files, as if they were regular tables in your PostgreSQL database. This is useful for integrating external data sources without needing to import the data directly into your database.

file_fdw is a contrib module, meaning it’s an additional feature included with PostgreSQL but not part of its core functionality. Contrib modules provide extra capabilities and enhancements beyond the core database system.

At first, I was going to write that the mechanism looks a lot like PolyBase in SQL Server. But in actuality, it’s more like a hybrid of PolyBase and OPENROWSET, as there’s no definition of external data sources or file formats, but there is the creation of an external (“foreign”) table.

Comments closed

“General Failure” and Query Store Compilation Times

Published 2024-08-14 by Kevin Feasel

Kendra Little warns us not to let a person named General Failure run your military:

This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.

These bugs are most likely to impact you if:

  • You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
  • Anyone manually forces query plans with Query Store.
  • You have slow storage, which can increase your likelihood of having longer compilation times.

The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.

In the words of the great John Madden, that’s a heckuva bug.

Comments closed

Building a Microsoft Fabric AI Skill to Generate Data

Published 2024-08-13 by Kevin Feasel

Sandeep Pawar tries out AI Skills in Microsoft Fabric:

In the last blog I wrote, I showed how to call the AI Skills endpoint in a Fabric notebook. Being able to call the endpoint programmatically creates many opportunities to integrate AI Skills in different applications. One that I thought of was using AI Skills as a function. Function Calling or Tools is a specific use case in Gen AI to create structured output based on a function or behavior instructed by the user. I am not referring to that as AI Skills can only return a table. Instead, what if you created a number of these AI Skills that are grounded in your data with specific functions built to get the intended output? You could serve/share these with end users who can call these functions to generate data/results. Think of these as macros in Excel.

Click through for an example of how it works.

Comments closed

Concurrent Index Creation in Postgres

Published 2024-08-13 by Kevin Feasel

Shayon Mukherjee recommends against a particular technique:

As a developer, you might have encountered situations where creating an index in PostgreSQL fails due to lock timeouts. In such scenarios, it’s tempting to use the IF NOT EXISTS as a quick fix and move on. However, this approach can lead to subtle and hard-to-debug issues in production environments.

Click through to learn more about how concurrent index creation works in Postgres and why the use of IF NOT EXISTS might not work the way you want.

Comments closed