Press "Enter" to skip to content

Curated SQL Posts

Indexes in PostgreSQL versus Oracle

Published 2024-11-13 by Kevin Feasel

Umair Shahid continues a series on migrating from Oracle to PostgreSQL:

For database experts well-versed in Oracle, moving to PostgreSQL opens up new indexing methods that differ significantly in terms of structure, management, and optimization. While both databases leverage indexing to enhance query speed, their approaches vary, particularly in terms of available types, performance tuning, and maintenance. This guide clarifies key differences and provides practical strategies for effectively handling indexes in PostgreSQL.

Read on for the article.

Leave a Comment

Describing R Models with Tilde (~)

Published 2024-11-12 by Kevin Feasel

Steven Sanderson describes a relationship:

The tilde operator (~) in R is more than just a symbol – it’s a powerful tool that forms the backbone of statistical modeling and formula creation. Whether you’re performing regression analysis, creating statistical models, or working with data visualization, understanding the tilde operator is crucial for effective R programming.

Read on to see how it works and several examples along the way.

Leave a Comment

The Concepts of Data Control Language in SQL

Published 2024-11-12 by Kevin Feasel

Joe Celko talks about the lesser-known language in SQL (compared to DML and DDL):

But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.

The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.

Click through to learn more.

Leave a Comment

The Value of Sufficiency

Published 2024-11-12 by Kevin Feasel

Rob Farley is good enough:

The French philosopher Montesquieu wrote, “Le mieux est le mortel ennemi du bien”, which means, “The best is the mortal enemy of the good.” Montesquieu was an interesting man who lived in the 18th century, and influenced many of the political structures around the world. His book “The Spirit of Law” from 1748 described ways to avoid absolute power, established concepts like “innocent until proven guilty”, and campaigned against slavery. I guess some countries adopted those ideas more than others.

I mentioned it because Josephine Bush has invited us to write about the topic of “Good enough is perfect“. And while I realise that the two statements are not identical, I consider that they are essentially the same. If we always strive for perfect then we can all too easily miss the mark of being able to deliver anything worthwhile. As a consultant I have to understand that the ideal solution for my customers is not the one where everything is completely perfect if achieving that means that the cost is too high. Instead, I need to find the point where good enough really is good enough.

Understanding value at the margin is a vital skill.

Leave a Comment

Execute a Collection of Child Pipelines from Metadata in Data Factory

Published 2024-11-12 by Kevin Feasel

Andy Leonard continues a series on design patterns:

In this post, I clone and modify the dynamic parent pipeline from the previous post to retrieve metadata from an Azure SQL database table for several child pipelines, and then call each child pipeline from a parent pipeline.

When we’re done, this pipeline will:

  1. Read pipeline metadata from a table in an Azure SQL database
  2. Store some of the metadata (a collection of pipelineID values) in the (existing) pipelineIdArray variable
  3. Iterate the pipelineIdArray variable’s collection of pipelineID values
  4. Execute each child pipeline represented by each pipelineID value stored in the pipelineIdArray variable

Read on to learn how.

Leave a Comment

Analyzing Delta Table Measures in Microsoft Fabric

Published 2024-11-11 by Kevin Feasel

Sandeep Pawar has a script for us:

I have been sitting on this code for a long time. I shared the first version in one of my blogs on Direct Lake last year. I have been making updates to it since then as needed. I waited for the lakehouse schema to become available and then forgot to blog about it. Yesterday, someone reached out asking if the above could be used for warehouse delta tables in Fabric, so here you go. It’s 250+ lines so let me just explain what’s going on here:

Read on for the explanation, the script itself, a demonstration, and several additional notes.

Leave a Comment

Function Volatility in PL/pgSQL

Published 2024-11-11 by Kevin Feasel

Deepak Mahto shares some advice:

The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation. Choosing the appropriate volatility for a function is crucial; as they say, “With great power comes great responsibility.“

Read on to learn more. It seems like it would be quite easy to mess this up.

Leave a Comment

Cmdlets vs Functions in Powershell

Published 2024-11-11 by Kevin Feasel

Adam Bertram differentiates the two:

Whenever you execute or run anything in PowerShell, you’re running a “command.” A command is an informal term for executable code, which can be a built-in PowerShell cmdlet, a custom function, or even an object method.

Cmdlets are compiled executable code provided by Microsoft, created in a language like C#, compiled, and added to a PowerShell module by software developers. Functions, on the other hand, are created directly in PowerShell by users. Although functions and cmdlets are technically different, they can be thought of as similar when you’re starting out.

Read on for more information, including a demo. I normally call them all cmdlets, but apparently I’ve been doing it wrong all this time.

Leave a Comment

Continuous Deployment in Practice

Published 2024-11-11 by Kevin Feasel

Bravin Wasike completes a series on continuous integration and continuous deployment:

Continuous Deployment (CD) is an advanced CI/CD practice that takes automation to the next level. It automatically deploys every code change that passes the automated testing phase to production. Unlike Continuous Delivery, which requires manual approval before deploying to production, Continuous Deployment eliminates manual intervention. This allows for seamless and frequent software updates.

Click through for the full article.

Leave a Comment