Press "Enter" to skip to content

Curated SQL Posts

PostgreSQL ON CONFLICT Directive

Published 2024-06-19 by Kevin Feasel

Shane Borden walks through an issue you might not expect:

I’m always working with customers migrating from Oracle to PostgreSQL. One of the things in Oracle that didn’t necessarily have any additional impact other than I/O against an index was if the application executed insert statements which violated a PK constraint. Typically an exception handler was added to the code and while you could argue that the application shouldn’t do that, it typically was not something that had to be dealt with too often. However in PostgreSQL it IS something you need to be aware of.

Read on to learn why, as well as what you can do about it (other than making your INSERT operation resilient to this sort of issue).

Comments closed

TempDB Contention and SQL Server 2022

Published 2024-06-19 by Kevin Feasel

Simon Liew shows off a change in SQL Server 2022:

Tempdb often acts as a high-traffic repository in SQL Server, experiencing significant contention. This includes not only temp table usage but also processes such as triggers, worktables for storing intermediate results for spools, cursors, sorts, work files for hash join, and temporary large object (LOB) storage, just to name a few.

A prominent issue is Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) system page latch contention, which can be particularly problematic under specific high concurrency workloads.

Read on for a demonstration of this.

Comments closed

T-SQL Tuesday 175 Round-Up

Published 2024-06-19 by Kevin Feasel

Andy Leonard rounds ’em up:

It’s time to celebrate and confess.

I was honored to host the June 2024 edition of T-SQL Tuesday – #175! That’s the celebration.

Confession: I had GoDaddy add a firewall back in February and it worked well. Too well, in fact! A friend reached out to let me know comments on the blog post – titled T-SQL Tuesday #175: Old Tech, New Tech, Bold Tech, Blue Tech –  was returning a nasty ACCESS DENIED message:

It was a bit of a short month in terms of turnout, but click through for Andy’s summary.

Comments closed

Retaining SQL Agent Job History in a Managed Instance

Published 2024-06-19 by Kevin Feasel

Andy Brownsword gets around the limitations:

In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?

There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:

Click through for those three techniques.

Comments closed

Downloading and Deleting Files in Oracle Object Storage

Published 2024-06-18 by Kevin Feasel

Brendan Tierney continues a series on Oracle Object Storage:

In my previous posts on using Object Storage I illustrated what you needed to do to setup your connect, explore Object Storage, create Buckets and how to add files. In this post, I’ll show you how to download files from a Bucket, and to delete Buckets.

Click through for scripts to perform both. If you just want to delete an item from a bucket without deleting the bucket as a whole, you can do so with a quick modification to Brendan’s script.

Comments closed

Transforming a REST API into a Data Stream

Published 2024-06-18 by Kevin Feasel

Lucia Cerchie and Dave Troiano build a stream:

In the space of APIs for consuming up-to-date data (say, events or state available within an hour of occurring) many API paradigms exist. There are file- or object-based paradigms, e.g., S3 access. There’s database access, e.g., direct Snowflake access. Last, we have decoupled client-server APIs, e.g., REST APIs, gRPC, webhooks, and streaming APIs. In this context, “decoupled” means that the client usually communicates with the server over a language-agnostic standard network protocol like HTTP/S, usually receives data in a standard format like JSON, and, in contrast to direct database access, typically doesn’t know what data store backs the API.

Of the above styles, more often than not, API developers settle on HTTP-based REST APIs for a number of reasons. They are incredibly popular. More developers know how to use REST APIs and are using them in production compared to other API technologies. For example, Rapid API’s 2022 State of APIs reports 69.3% of survey respondents using REST APIs in production, well above the percentage using alternatives like gRPC (8.2%), GraphQL (18.6%), or webhooks (34.6%). 

Click through for a demonstration of how to take an existing REST API and build a data stream out of it using Apache Kafka and Apache Flink.

Comments closed

Exporting Multiple Databases Concurrently via SqlPackage

Published 2024-06-18 by Kevin Feasel

Jose Manuel Jurado Diaz has a script for us:

This week, I’ve been working on a service request case where we need to export multiple databases using SqlPackage. Following, I would like to share my lesson learned to export  simultaneous several databases, saving the export files to the F:\sql folder and the logs of the operations to the F:\sql\log folder.

Few recommendations when performing these exports:

Click through for those recommendations and the script. The cynic in me would add a third tip: make sure your databases are small, or else SqlPackage won’t work so well.

Comments closed

SSIS Extensions in Visual Studio

Published 2024-06-18 by Kevin Feasel

Andy Leonard fills us in on what’s happening in the world of Integration Services:

I’m not sure why the decision was made to “fork” development and maintenance of the Integration Services extension for Visual Studio 2022. Based upon experience with SSIS, Visual Studio, and the Microsoft SSIS team, I am confident there’s a very good reason. As one can glean from my appointment screenshot above, I save the links to both pages. The links are:

SSIS Pre-2022: https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects&ssr=false#overview
SSIS 2022: https://marketplace.visualstudio.com/items?itemName=SSIS.MicrosoftDataToolsIntegrationServices

Read on to see what’s new in both of those projects.

Comments closed

Explaining Filter Context in DAX

Published 2024-06-18 by Kevin Feasel

Marco Russo and Alberto Ferrari bust out the drawing board:

The filter context is a fundamental concept to understand in order to write good DAX code. In this article, we describe the filter context using a visual approach – we rely on a graphical visualization that represents the different interactions that exist in the filter context when you use the visual elements, filters, and slicers of a report. This is the first step to plan the changes required to obtain the desired result: these changes can be obtained by using the CALCULATE function, which removes, adds, and replaces existing filters in the filter context.

This article provides a different perspective on a topic already discussed in other filter context articles: read them to get more insights about this importance concept for DAX.

Read on to learn more about how it works.

Comments closed